Delete row in sql from table

Question

Sorry for the title but I didn't know how to name it..

I'm using C# and have a WinForms application I have 2 tables, each of them have a primary key, those 2 tables are strangers. It means that I have a third table that connects between them. the third table have as columns : table's A primary key and table's B primary key.

I just want to know that if I'm deleting one row from the third table, is the related data from table A and B will be also deleted?

depends if there is an enforced relationship between the tables and a delete trigger. — Jay Walker, Mar 11 '13 at 20:55
table A have a relationship with the Third table as well as table B. — Elior, Mar 11 '13 at 20:57

score 6 · Accepted Answer · edited May 23 '17 at 10:25

6

If you created a foreign key constraint with the ON DELETE CASCADE option, then yes, it will delete the related rows in other tables.

If you created a foreign key constraint WITHOUT the ON DELETE CASCADE option, then the DBMS will prevent you from deleting the original row at all.

If you did not create a foreign key constraint then only the original row will be deleted.

See this SO answer for example usage of the cascading delete option.

edited May 23 '17 at 10:25

Community

1
1

answered Mar 11 '13 at 20:55

ean5533

8,884
3
40
64

what does it mean ON DELETE CASCADE? – Elior Mar 11 '13 at 21:05
@Elior I think I explained it pretty clearly. Perhaps you should try googling if you need a deeper explanation. – ean5533 Mar 11 '13 at 21:06

score 1 · Answer 2 · answered Mar 11 '13 at 20:54

1

No, it doesn't your third table is just associate table which stores references of table A and B.

But it will have different functionality other way, if a record is deleted in table A and it has any references in associate table C. Depending upon your cascade options, record in table C will also be deleted. If there are no cascade options mentioned, it gives exception.

answered Mar 11 '13 at 20:54

Sunny

4,765
5
37
72

so if i'll delete a row from table a, it will delete the reference row from table c and then from table b? or just the rows from tables a and c? – Elior Mar 11 '13 at 21:02
@Elior, only from table a and c. – Sunny Mar 11 '13 at 21:09

score 1 · Answer 3 · answered Mar 11 '13 at 20:55

1

Sql Server wouldn't let you delete the row from 3rd table because of a Foreign Key constraint. You'd have to delete the values referencing the 3rd table from table A and B first and then delete from your 3rd table.

answered Mar 11 '13 at 20:55

Brent

86
3

score 0 · Answer 4 · answered Mar 11 '13 at 20:55

0

No, as the third table is an association table of table a and table b, no records from table a and table b will be deleted, if the deletion is on third table.

answered Mar 11 '13 at 20:55

Piyas De

1,786
15
27

Delete row in sql from table

4 Answers4