Foreign key constraints are upheld by the database. If your application tries to break a foreign key constraint by
the database will deny the operation.
Let's see a few code samples in which we try to break foreign key constraints.
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. |
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.
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.
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. |
To handle foreign key constraint errors, the error handler of your data-modifying components should check for message number 547 coming from SQL Server.