Foreign Key Constraints

[Previous] [Next]

Foreign key constraints are upheld by the database. If your application tries to break a foreign key constraint by

• Adding a child that doesn't refer to an existing parent
• Deleting a parent that has children referring to it
• Changing the key of a parent
• Changing the foreign key of a child so that it no longer refers to an existing parent

the database will deny the operation.

Trying to Break Foreign Key Constraints

Let's see a few code samples in which we try to break foreign key constraints.

Adding a child with invalid parent reference

Knowing that no trainer with the trainer ID of 0 exists in the database, let's try to insert a horse that refers to that nonexistent trainer:

 INSERT INTO Horses (HorseId, HorseName, BredIn, Sex, Birthyear, Active, Trainer) VALUES (99999, 'New Horse', 'SWE', 's', 1989, 1, 0) 

Microsoft SQL Server wisely denies the operation, returning the following message:

 Server: Msg 547, Level 16, State 1, Line 1 INSERT statement conflicted with COLUMN FOREIGN KEY constraint  'FK_Horses_Trainers'. The conflict occurred in database  'MSPress_ADBRacing', table 'Trainers', column 'TrainerId'. The statement has been terminated. 

Deleting a parent with children

Let's also try to delete a true parent, by which we (in this context) mean a trainer that actually has horses in training:

 DELETE Trainers WHERE TrainerId = 16 

Because trainer 16 is Michael Kahn, who trains more horses than any other trainer in the Scandinavian countries (trotters not counted), SQL Server denies this operation as well. Here's the message it gives us:

 Server: Msg 547, Level 16, State 1, Line 1 DELETE statement conflicted with COLUMN REFERENCE constraint  'FK_Horses_Trainers'. The conflict occurred in database  'MSPress_ADBRacing', table 'Horses', column 'Trainer'. The statement has been terminated. 

Most interestingly, the server message number 547 is the same one given in the preceding case.

Updating a primary key with foreign keys referencing it

Trying to update the trainer ID of Michael Kahn from 16 to 0 gave the same, or at least a similar, result. The message number was the same as in both the preceding cases:

 Server: Msg 547, Level 16, State 1, Line 1 UPDATE statement conflicted with COLUMN REFERENCE constraint  'FK_Horses_Trainers'. The conflict occurred in database  'MSPress_ADBRacing', table 'Horses', column 'Trainer'. The statement has been terminated. 

Notice that this statement was sent to a SQL Server 7.0 database. SQL Server 2000, not yet released as we write this, allows another kind of foreign key constraint named CASCADE. If such a constraint were specified on the Trainer column in the Horses table, all horses having 16 in the Trainer column would change to 0 together with the TrainerId column in the Trainers table. The delete operation wouldn't be denied with such a constraint specified; it would be cascaded.

Moving the horse to a nonexistent trainer

Just to experience the happiness of having a move operation accepted by SQL Server, let's move horse 525, Lambada Hot, from trainer Michael Kahn to trainer Tommy Gustafsson, using the SQL UPDATE statement shown here.

 UPDATE Horses SET Trainer = 80 WHERE HorseId = 525 

As expected, this works without any problems:

 (1 row(s) affected) 

Now let's try to move the same horse to a nonexistent trainer:

 UPDATE Horses SET Trainer = 0 WHERE HorseId = 525 

As you might expect, because there's no trainer with an ID of 0, SQL Server doesn't accept this movement. Instead it returns the following message, again using error number 547:

 Server: Msg 547, Level 16, State 1, Line 1 UPDATE statement conflicted with COLUMN FOREIGN KEY constraint  'FK_Horses_Trainers'. The conflict occurred in database  'MSPress_ADBRacing', table 'Trainers', column 'TrainerId'. The statement has been terminated. 

Checking for Foreign Key Violations

To handle foreign key constraint errors, the error handler of your data-modifying components should check for message number 547 coming from SQL Server.