Delete the primary key row, which is the foreign key of another table

Question

Suppose there is a primary table containing the primary key, and there is another table that contains the foreign key for this primary table. Therefore, if we delete the row of the main table, and not the child table.

Answer 1

You must set some specific parameters in your FKey, for example ON DELETE {CASCADE, SET NULL, SET DEFAULT}

Instead, you cannot delete the reference string, because it is prohibited by the sql server due to referential integrity.

Thus, the option should set the table binding value to NULL or any other DEFAULT value.

Or delete it too

Or , if your link string has some meaning without a parent string - then something is wrong with your DB design - either you don't need FKey, or the schema hasn’t normalized.

Answer 2

Try something like this

ALTER TABLE MainTable ADD CONSTRAINT fk_xyz FOREIGN KEY (xyz) REFERENCES ChildTable (xyz) ON DELETE CASCADE 

Answer 3

I think you need something like this.

ENABLE CASCADE Indicates that if an attempt is made to delete a row with a key referenced by foreign keys in existing rows in other tables, all rows containing these foreign keys are also deleted.

ref: http://msdn.microsoft.com/en-us/library/ms186973%28SQL.90%29.aspx