Best way to delete records in two tables having foreign key?

Question

I created two tables like marks and users. I maintained foreign key relation between two tables, When I delete a row in marks table, I need to delete that particular user in user table based on uid that exists in both tables commonly.can anyone suggest me?

i tried like in one users table with columns as sid,name,pwd,uid(PK),,similarily in marks table name all subject marks and uid(FK). if i tried to delete on row in marks based on that uid can i delete records in users table?? — Dinesh Reddy, Jun 01 '15 at 04:48
[How do I use cascade delete with SQL Server?](http://stackoverflow.com/questions/6260688/how-do-i-use-cascade-delete-with-sql-server) — har07, Jun 01 '15 at 04:50
possible duplicate of [Using triggers to implement referential integrity actions (SQL Server)](http://stackoverflow.com/questions/30086504/using-triggers-to-implement-referential-integrity-actions-sql-server) — Zohar Peled, Jun 01 '15 at 09:26

score 3 · Answer 1 · 2015-06-01T04:59:14.963

Use the ON DELETE CASCADE option if you want rows deleted in the child table when corresponding rows are deleted in the parent table.

But your case is reverse from it.There is no way to do it reverse automatically.

You need to use delete trigger explicitly whenever record are delete from child table.

BTW its not safe to do reverse as there might be many marks record for single user and if you delete any one of them then user is removed from user table.

I suggest to do it logically in sproc.

you can check in sproc that all record for user is deleted in mark table than remove user from user table.

Ubaid Ashraf · Answer 2 · 2015-06-01T04:52:32.287

2

Well for your case, I will recommend using on delete cascade

More about it :
A foreign key with cascade delete means that if a record in the parent table is deleted, then the corresponding records in the child table will automatically be deleted. This is called a cascade delete in SQL Server.

The syntax for creating a foreign key with cascade delete using a CREATE TABLE statement in SQL Server (Transact-SQL) is:

CREATE TABLE child_table
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT fk_name
    FOREIGN KEY (child_col1, child_col2, ... child_col_n)
    REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n)
    ON DELETE CASCADE
    [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
);

For more read this

edited Jun 01 '15 at 04:52

answered Jun 01 '15 at 04:49

Ubaid Ashraf

1,049
5
15
43

if i want to delete records from asp.net ,how can i implement that in sql command ?? – Dinesh Reddy Jun 01 '15 at 04:57
as far deleting is concerned, you can surely, but you have to be carefull, for example, you delete child record with uid 1, and then delete from parent table record with uid 1, there can be still more child records having uid as 1, and in this case it wont allow deletion – Ubaid Ashraf Jun 01 '15 at 05:00
actually my main theme is if we delete records in marks table,then that person user pwd also wana delete,in that case only the deleted records person cant have permission to access.after that record deletion in both tables paralelly while login the user details not exist naa ? thats my situation.if anyone understand please rply,bcz i'm new to db that's y i'm not explaining clearly .. – Dinesh Reddy Jun 01 '15 at 05:16

score 0 · Answer 3 · answered Jun 01 '15 at 04:51

In design just use on delete cascade

CREATE TABLE child_table
(
  column1 datatype [ NULL | NOT NULL ],
  column2 datatype [ NULL | NOT NULL ],
  ...

  CONSTRAINT fk_name
    FOREIGN KEY (child_col1, child_col2, ... child_col_n)
    REFERENCES parent_table (parent_col1, parent_col2, ... parent_col_n)
    ON DELETE CASCADE
    [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ] 
);

Now when you delete parent . child will automatically deleted... you don't need to do any thing

check Link for detail On delete cascade

score 0 · Answer 4 · answered Jun 01 '15 at 05:21

As I don't like to DELETE any row from related tables, I suggest you this solution:

Add a status field with default value of 1 to your table(s).
Create a VIEW that shows only rows with status <> 0 and use this VIEW to show your valid data.

For parent-child or related tables just show rows with status <> 0 for both of parent and child table like parent.status * child.status <> 0.
[Optional & additional]^* Create a log table or a journal for your database or your tables or just your important tables and store some actions like Create, Edit\Modify, Delete, Undelete and so on.

With this solution you can:

Support Undo and Redo.
Support Undelete action!
Be not worry about a child that has no parent.
^*Found old data, changes of data and many other information.

And many other benefits and you just store more data that it is not concern with a good RDBMS.

I use DELETE just for a table that is at the end child point and its data is not so important.

Best way to delete records in two tables having foreign key?

4 Answers4