delete primary key row which is foreign key of another table [closed]

Question

Suppose there is a main table containing a primary key and there is another table which contains a foreign key to this main table. So if we delete the row of main table only not child table.

Answer 1

You should set some specific options on your FKey, such as ON DELETE {CASCADE, SET NULL, SET DEFAULT}

Instead you'll not be able to delete referenced row, because it is prohibited by sql server due to referrential integrity.

So, the option is to set referencing table's value to NULL or any other DEFAULT value.

Or delete it too

Or, if your referencing row has some meaning without parent row - then something is wrong with your DB design - either you do not need the FKey or schema is not 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 u want some thing like this.

ON DELETE CASCADE Specifies 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 that contain those foreign keys are also deleted.

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