CODEWITHSUNDEEP
sqlforeign-keys
va.
va.

Reputation: 780

SQL delete column with Foreign key

What is the correct order if I have to delete column O_CLIENT?

  1. Delete relation; then delete column O_CLIENT
  2. Delete column o_CLIENT
CREATE TABLE client (
C_ID int PRIMARY KEY AUTOINCREMENT,
....
);

CREATE TABLE order (
O_ID int PRIMARY KEY AUTOINCREMENT,
.......
O_CLIENT long FOREIGN KEY REFERENCES client(C_ID)
);

Upvotes: 1

Views: 3849

Answers (3)

Justin Pihony
Justin Pihony

Reputation: 67075

You have to delete the relationship first, or else you will end up with a foreign key constraint error. This is built in to protect you from having dangling references (ie. you delete client 1, but there are still 3 orders tied to client 1. If you try to get the client from the order, you will get nothing, which would be invalid)

You can also look into cascading deletes, so that the top level delete, deletes all of the relationships automatically. Then you can just perform one delete that clears everything out

Upvotes: 4

Tiago César Oliveira
Tiago César Oliveira

Reputation: 810

If you are doing this by hand, you first need to drop the relation, then delete the column. If you are doing this by tool (like MySql Workbench) it will take care of this for you.

Upvotes: 1

Oded
Oded

Reputation: 498942

When deleting a column that is a foreign key column (that is, references a key from another table), it is normally safe to simply delete the column.

This will also delete the foreign key constraint defined on this column.

Upvotes: 1

Related Questions