How to create foreign key so when we delete parent value in child will be updated to null

Question

I am new in T-SQL please help: I have two tables

User
  Id
  Name

History
  Id
  Result
  UserId

How to create relationship so once I delete User from User table the History table will be updated for the column UserId to NULL

Sachin · Accepted Answer · 2013-06-06T16:43:27.780

5

You can use on delete set null like this

ALTER TABLE USERS 
ADD CONSTRAINT Users_ThemeID_FK 
FOREIGN KEY (ThemeID) REFERENCES Themes(ThemeID) 
ON DELETE SET NULL

edited Jun 06 '13 at 16:43

answered Mar 22 '13 at 19:20

Sachin

40,216
7
90
102

Thank you! Exactly what I wanted!!!! You are the best! I will accept this answer in 3 minutes when it will be available – Sergey Mar 22 '13 at 19:23

score 1 · Answer 2 · answered Mar 22 '13 at 19:19

1

ALTER TABLE dbo.History ADD CONSTRAINT
    FK_History_User FOREIGN KEY
    (
    UserID
    ) REFERENCES dbo.[User]
    (
    ID
    ) ON UPDATE  NO ACTION 
     ON DELETE  SET NULL

answered Mar 22 '13 at 19:19

bummi

27,123
14
62
101

score 0 · Answer 3 · edited May 23 '17 at 12:04

0

Make sure to turn on Cascading deletes, then setup a pK relationship then you just use regular Delete syntax like Delete From Table Where PK =

edited May 23 '17 at 12:04

Community

1
1

answered Mar 22 '13 at 19:17

Vortex

663
6
7

score 0 · Answer 4 · answered Mar 22 '13 at 19:20

You can change the action on the FK relationship to set to null:

-- Create FK relationship
IF EXISTS (SELECT * FROM sys.objects
WHERE name = N'FK_EmpEducation_Employees' AND [type] = 'F')
ALTER TABLE EmpEducation
DROP Constraint FK_EmpEducation_Employees
GO

ALTER TABLE EmpEducation
ADD CONSTRAINT [FK_EmpEducation_Employees]
FOREIGN KEY (empno)REFERENCES employees(empno)
ON DELETE SET NULL ON UPDATE SET NULL
GO

See Here: http://www.mssqltips.com/sqlservertip/2365/sql-server-foreign-key-update-and-delete-rules/

How to create foreign key so when we delete parent value in child will be updated to null

4 Answers4