-
"... on delete cascade ..."
Guys, could anyone tell me if MSSQL Server 7 has 'on delete cascade' option when creating a foreign key constraint or something similar to it. I'd really like MSSQL to remove all dependent records (child records) automatically from one table when I'm deleting a parent record from another table. I know that I can do it via trigger, but the FK constraint should be removed or disabled. I would really appreciate your help. Thank you very much.
-
"... on delete cascade ..." (reply)
SQL Server supports only reference of foreign key values; cascading updates and deletes are not supported through the foreign key constraint mechanism.
deepak.
------------
Val at 12/8/99 5:36:38 PM
Guys, could anyone tell me if MSSQL Server 7 has 'on delete cascade' option when creating a foreign key constraint or something similar to it. I'd really like MSSQL to remove all dependent records (child records) automatically from one table when I'm deleting a parent record from another table. I know that I can do it via trigger, but the FK constraint should be removed or disabled. I would really appreciate your help. Thank you very much.
-
"... on delete cascade ..." (reply)
Thanks for that.
So, I guess I have to remove FKs and use triggers to look after data integrity?
------------
deepak at 12/8/99 6:18:44 PM
SQL Server supports only reference of foreign key values; cascading updates and deletes are not supported through the foreign key constraint mechanism.
deepak.
------------
Val at 12/8/99 5:36:38 PM
Guys, could anyone tell me if MSSQL Server 7 has 'on delete cascade' option when creating a foreign key constraint or something similar to it. I'd really like MSSQL to remove all dependent records (child records) automatically from one table when I'm deleting a parent record from another table. I know that I can do it via trigger, but the FK constraint should be removed or disabled. I would really appreciate your help. Thank you very much.
-
"... on delete cascade ..." (reply)
I don't understand why you need to remove foreign key constraints to delete the child records. You can delete them write away.
Deepak.
------------
at 12/8/99 6:24:53 PM
Thanks for that.
So, I guess I have to remove FKs and use triggers to look after data integrity?
------------
deepak at 12/8/99 6:18:44 PM
SQL Server supports only reference of foreign key values; cascading updates and deletes are not supported through the foreign key constraint mechanism.
deepak.
------------
Val at 12/8/99 5:36:38 PM
Guys, could anyone tell me if MSSQL Server 7 has 'on delete cascade' option when creating a foreign key constraint or something similar to it. I'd really like MSSQL to remove all dependent records (child records) automatically from one table when I'm deleting a parent record from another table. I know that I can do it via trigger, but the FK constraint should be removed or disabled. I would really appreciate your help. Thank you very much.
-
"... on delete cascade ..." (reply)
Because I'm getting this error:
Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint
'FK_2_1'. The conflict occurred in database 'Northwind',
table 'VAL_2', column 'ID_LINK'. The statement has been terminated.
I have VAL_1 table (id_1 is PK) and VAL_2 (id_2 is PK and ID_LINK is FK ref. VAL_1(id_1).
I'm trying to remove one record from VAL_1, which has children in VAL_2. I have a trigger on
'delete' for VAL_1, which looks like this:
CREATE TRIGGER [trg_del_val_1] ON [val_1]
FOR DELETE
AS
BEGIN
DELETE val_2 FROM val_2 INNER JOIN deleted
on val_2.id_link = deleted.id_1
END
If I run the delete statement I'm getting that error. If I'm disabling the FK_2_1 constraint,
everything is fine - the parent record from VAL_1 disappears, so do the children from VAL_2.
Any ideas?
Thank you very much.
------------
deepak at 12/8/99 6:34:33 PM
I don't understand why you need to remove foreign key constraints to delete the child records. You can delete them write away.
Deepak.
------------
at 12/8/99 6:24:53 PM
Thanks for that.
So, I guess I have to remove FKs and use triggers to look after data integrity?
------------
deepak at 12/8/99 6:18:44 PM
SQL Server supports only reference of foreign key values; cascading updates and deletes are not supported through the foreign key constraint mechanism.
deepak.
------------
Val at 12/8/99 5:36:38 PM
Guys, could anyone tell me if MSSQL Server 7 has 'on delete cascade' option when creating a foreign key constraint or something similar to it. I'd really like MSSQL to remove all dependent records (child records) automatically from one table when I'm deleting a parent record from another table. I know that I can do it via trigger, but the FK constraint should be removed or disabled. I would really appreciate your help. Thank you very much.
-
"... on delete cascade ..." (reply)
You should delete from child first. SQL checks fkey constraint before firing trigger.
------------
Val at 12/8/99 6:53:56 PM
Because I'm getting this error:
Server: Msg 547, Level 16, State 1, Line 1
DELETE statement conflicted with COLUMN REFERENCE constraint
'FK_2_1'. The conflict occurred in database 'Northwind',
table 'VAL_2', column 'ID_LINK'. The statement has been terminated.
I have VAL_1 table (id_1 is PK) and VAL_2 (id_2 is PK and ID_LINK is FK ref. VAL_1(id_1).
I'm trying to remove one record from VAL_1, which has children in VAL_2. I have a trigger on
'delete' for VAL_1, which looks like this:
CREATE TRIGGER [trg_del_val_1] ON [val_1]
FOR DELETE
AS
BEGIN
DELETE val_2 FROM val_2 INNER JOIN deleted
on val_2.id_link = deleted.id_1
END
If I run the delete statement I'm getting that error. If I'm disabling the FK_2_1 constraint,
everything is fine - the parent record from VAL_1 disappears, so do the children from VAL_2.
Any ideas?
Thank you very much.
------------
deepak at 12/8/99 6:34:33 PM
I don't understand why you need to remove foreign key constraints to delete the child records. You can delete them write away.
Deepak.
------------
at 12/8/99 6:24:53 PM
Thanks for that.
So, I guess I have to remove FKs and use triggers to look after data integrity?
------------
deepak at 12/8/99 6:18:44 PM
SQL Server supports only reference of foreign key values; cascading updates and deletes are not supported through the foreign key constraint mechanism.
deepak.
------------
Val at 12/8/99 5:36:38 PM
Guys, could anyone tell me if MSSQL Server 7 has 'on delete cascade' option when creating a foreign key constraint or something similar to it. I'd really like MSSQL to remove all dependent records (child records) automatically from one table when I'm deleting a parent record from another table. I know that I can do it via trigger, but the FK constraint should be removed or disabled. I would really appreciate your help. Thank you very much.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|