question

nicelatefa90 avatar image
nicelatefa90 asked

Can you DROP a table if its primary key is used in a foreign key constraint? Why or why not? What error message will you get if you try to do so

Can you DROP a table if its primary key is used in a foreign key constraint? Why or why not? What error message will you get if you try to do so
dropadd
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Your question looks like it's a theoretical problem rather than a practical one - i.e. you've been set this task as some sort of study work. If that is the case, why not try it? You will learn far more that way. You could simply create two tables, create the relationship you describe and attempt to drop the first. You could try this in the express edition of SQL server, which is free to download. If this isn't a "homework" question, perhaps you could give us more of the background so we can be sure what it is that you are trying to solve?
3 Likes 3 ·
Or it's an interview question. This one is too simple to just test to see what will happen.
0 Likes 0 ·

1 Answer

· Write an Answer
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
Like Dave and Grant already said - asking this question and looking for the answer needs more effort than actually creating two tables, a relationship and a drop table-command to see what happens. The "what happens" part of the answer is: No, you can't drop a main table if it has child tables related to it. The "why" part: It wouldn't make sense if it was possible. A foreign key constraint is there to protect you from deleting parent data if there's child data related to it. Dropping the actual container of parent data would yield the foreign key constraint (which is created on the child table) invalid. What error message do you get: See for yourself. create table main(id int primary key); create table sub(id int primary key, main_id int constraint fk_sub_main foreign key references main(id));
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.