Pages

Thursday, September 2, 2010

foreign key constraint violation

A foreign key constraint violation can happen if you delete a row that is referenced in another table. When the constraint is violated the error message in SQL Server is "INSERT statement conflicted with COLUMN FOREIGN KEY constraint".

table named Person with an identity column PersonID
table named PersonPhones references Person.PersonID
the constraint is on the Person table

ALTER TABLE [dbo].[Person]  WITH CHECK ADD  CONSTRAINT [FK_PersonID] FOREIGN KEY([PersonID])
REFERENCES [dbo].[PersonPhones] ([PersonID])

The row cannot be deleted from Person if the PersonID exists in PersonPhones. The concept is the same in any RDBMS. The constraint could also be set up to automatically update and delete rows from the child table. It would look like this:

ALTER TABLE [dbo].[Person]  WITH CHECK ADD  CONSTRAINT [FK_PersonID] FOREIGN KEY([PersonID])
REFERENCES [dbo].[PersonPhones] ([PersonID])
ON UPDATE CASCADE
ON DELETE CASCADE

It's just something the program should check. If you add a new person all fields should contain a value before you can say CRUD was successful. (CRUD=Create, Read, Update, Delete) If nothing else the user should be given a more friendly error message.

2 comments:

Anonymous said...
This comment has been removed by a blog administrator.
Anonymous said...

Sorry for my bad english. Thank you so much for your good post. Your post helped me in my college assignment, If you can provide me more details please email me.