Deleting related records in another table with "Where"-like considerations

Question

I have a data table with a primary key called OptDefID. When a record in this table is deleted I need to go and delete all records from the Permissions table that have that OptDefID in the defID field (in Permissions). The tricky part for me is that the Permissions table does not have a primary key and holds lots of different kinds of permissions, and has a permissiontype field. I need to delete rows that have the OptDefID AND a permissiontype of OptDef.

Because I need to consider the permissiontype, I don't believe a Foreign Key Constraint is appropriate here (or is it?).

I've also considered creating a trigger, but am unsure how to get the OptDefID passed into the trigger.

I can do this via the application itself, but I feel like this should be a database level solution.

What's the best solution?

see http://stackoverflow.com/questions/1767013/sql-server-delete-trigger-row-handle-referred-to-a-deleted-row-or-a-row-marked for how to do that via a trigger... — p.marino, Nov 08 '12 at 23:07
_"I need to delete rows that have the OptDefID AND a permissiontype of OptDef."_ And what about rows that have the `OptDefID` but do **not** have a `permissiontype` of `OptDef`? Should they be just set to NULL? — Branko Dimitrijevic, Nov 09 '12 at 00:37
@Ojen So you need rows with "hanging" `OptDef`? What would be the meaning of that? This feels suspicious to me, and might indicate deeper problems in your model... — Branko Dimitrijevic, Nov 09 '12 at 22:24
@BrankoDimitrijevic Say I want to delete from Permissions where defID is 20 and permissiontype is 'OptDef'. There may be another row in Permissions that has a defID of 20, but has a permissiontype of 'Member'. That show should not be deleted because it pertains to Members and not Opt data. I've edited the original question for to add clarity. — Ojen, Nov 12 '12 at 14:37

score 0 · Answer 1 · answered Nov 12 '12 at 14:44

In case the OptDefId column is only filled when the record in question references the Permissions table, a foreign key should be appropriate. I.e. you have another column MemberId, which in turn could be a foreign key on a Members table.

It is only when you have a single column - let's call it ObjectId - which takes on other meanings as the contents of the type column change, that you cannot use foreign keys. In that case, a trigger would probably be the best approach, as you already guessed. I only know about triggers in Oracle PL/SQL, where they are passed in as separate, complete rows representing the old and new state. I guess it will be analogous in MS-SQL-Server.

score 0 · Answer 2 · edited May 23 '17 at 10:26

0

In addition to using join with SELECT statements, you can also join multiple tables in DELETE & UPDATE statements as well.

As I understand the issue, you should be able to join the Permissions table to the table with the OptDefID column & add a WHERE clause similar to the this:

DELETE MyTable
...
WHERE [Permissions].permissiontype = 'OptDef'

Also, these links may be of interest too:

SQL DELETE with JOIN another table for WHERE condition (for MySQL, but still relevant)
Using A SQL JOIN In A SQL DELETE Statement
Using A SQL JOIN In A SQL UPDATE Statement

edited May 23 '17 at 10:26

Community

1
1

answered Nov 12 '12 at 14:50

Alexander

2,320
2
25
33

I realize this type of delete can be done, but are you suggesting doing this via a trigger or a via the applciation or something else? – Ojen Nov 12 '12 at 14:59
Here's an example at [SqlFiddle.com](http://sqlfiddle.com/#!3/175ea) that illustrates what I'm thinking of - this query could be executed from your application. – Alexander Nov 12 '12 at 15:34
That won't work because defID is not a primary key. See my comment to Branko above. Thanks for the help – Ojen Nov 12 '12 at 15:44
Ah, I see that comment now. Why can't you just do this: `DELETE Permissions WHERE defID = 20 AND permissiontype = 'OptDef'` ? Also, you could wrap this within a single [transaction](http://msdn.microsoft.com/en-us/library/ms188929.aspx) scope if you need to modify any other data (e.g. delete a record from another table) at the same time. – Alexander Nov 12 '12 at 15:50
That might be what I end up doing, just thought a database level constraint maybe have been more appropriate. – Ojen Nov 12 '12 at 15:58

Branko Dimitrijevic · Accepted Answer · 2012-11-12T16:36:36.640

Say I want to delete from Permissions where defID is 20 and permissiontype is 'OptDef'. There may be another row in Permissions that has a defID of 20, but has a permissiontype of 'Member'. That show should not be deleted because it pertains to Members and not Opt data.

Storing table names in fields prevents foreign keys from working properly, as you have discovered.

I recommend you fix the root problem and separate these two foreign keys, so each of them can be individually enforced. For example:

CREATE TABLE Permissions (
    ...
    OptDefId int,
    MemberId int,
    FOREIGN KEY (OptDefId) REFERENCES OptDef ON DELETE CASCADE,
    FOREIGN KEY (MemberId) REFERENCES Members ON DELETE CASCADE,
    CHECK (
        (OptDefId IS NOT NULL AND MemberId IS NULL)
        OR (OptDefId IS NULL AND MemberId IS NOT NULL)
    )
)

The CHECK makes sure only one of the FKs is non-NULL and only non-NULL FKs are enforced.

Alternatively, you could avoid changing your current design and enforce this "special" FK through a trigger, but declarative constraints should be preferred to triggers when possible - declarative constraints tend to leave less room for error and be more "self-documenting".

Deleting related records in another table with "Where"-like considerations

3 Answers3