SETHA SITE INFORMATION

  • Increase font size
  • Default font size
  • Decrease font size

How to Add Foreign Key, Delete and Update CASCADE to Existing Table in T-SQL?

E-mail Print PDF
Microsoft SQL Server uses T-SQL (Transact SQL) to access database objects and manipulate data in the Database. If we create one Table and forgot to put a foreign key constraint including CASCADE options on a field that is a foreign key of other Table, we can use the following CODE.
ALTER TABLE table_name
WITH NOCHECK ADD CONSTRAINT fk_constraint_name
	FOREIGN KEY(foreign_key_field)
	REFERENCES reference_table(primary_key_field)
	[ON DELETE CASCADE]
	[ON UPDATE CASCADE]

For example, we have existing Tables as below:
student(id, stname, gender)
studentphone(id, phone_number)
 
Table studentphone is a child table of student but we forgot to put a foreign key constraint on field [id] in studentphone. To add a foreign key constraint on field [id] with CASCADE options in Table studentphone please see the code below.
ALTER TABLE studentphone
WITH NOCHECK ADD CONSTRAINT fk_student_phone
	FOREIGN KEY(id)
	REFERENCES studenet(id)
	ON DELETE CASCADE
	ON UPDATE CASCADE