Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
We can use these statements together with the Foreing Key constraints. First of all, it is necessary to understand in detail what Foreign Key is. You can benefit from the article “What is Primary Key and Foreign Key“.
If you have defined Foreing key as I have described in the article “What is Primary Key and Foreign Key“, you cannot normally delete the record in the primary table without deleting the record in the table with the foreing key. When you try to delete it, you receive the error as follows.
Msg 547, Level 16, State 0, Line 4
The DELETE statement conflicted with the REFERENCE constraint “FK_xx”. The conflict occurred in database “xx”, table “dbo.xx”, column ‘xx’.
The statement has been terminated.
What is ON DELETE CASCADE
If you created the Foreign Key by specifying “ON DELETE CASCADE”, when you delete the record in the primary table, it automatically deletes the records in the child table that the foreign key is defined in. You wouldn’t get the above error. Of course, this is not always desirable. Thats why this is not the default behavior.
Create Foreing Key With ON DELETE CASCADE
You can create a foreign key by specifying “ON DELETE CASCADE” with the script below.
1 2 3 4 5 | ALTER TABLE [dbo].[CityFeatures] WITH CHECK ADD CONSTRAINT [FK_cityfeatures_City] FOREIGN KEY([CityID]) REFERENCES [dbo].[City] ([ID]) ON DELETE CASCADE GO ALTER TABLE [dbo].[CityFeatures] CHECK CONSTRAINT [FK_cityfeatures_City] GO |
What is ON UPDATE CASCADE
If you specify “ON UPDATE CASCADE” when creating Foreign Key, this ensures that when there is an update in the column in the primary table, this update is reflected in the table with the foreign key.
If you did not create the Foreign Key with the ON UPDATE CASCADE, you will get an error like the following when you try to update the column referenced by foreing key in the primary table.
Msg 547, Level 16, State 0, Line 4
The UPDATE statement conflicted with the REFERENCE constraint “FK_xx”. The conflict occurred in database “xx”, table “dbo.xx”, column ‘xx’.
The statement has been terminated.
Create Foreing Key With ON UPDATE CASCADE
You can create a foreign key by specifying “ON UPDATE CASCADE” with the script below.
1 2 3 4 5 | ALTER TABLE [dbo].[CityFeatures] WITH CHECK ADD CONSTRAINT [FK_cityfeatures_City] FOREIGN KEY([CityID]) REFERENCES [dbo].[City] ([ID]) ON UPDATE CASCADE GO ALTER TABLE [dbo].[CityFeatures] CHECK CONSTRAINT [FK_cityfeatures_City] GO |
Can we alter primary key column if we created Foreign Key With ON UPDATE CASCADE?
If you try to alter primary key column you will receive the below error.
Msg 5074, Level 16, State 1, Line 8
The object ” is dependent on column ”.
Msg 4922, Level 16, State 9, Line 8
ALTER TABLE ALTER COLUMN failed because one or more objects access this column.
If you want to change primary key column you may want to read the article name “What is Primary Key and Foreign Key”
