Sql Server 2005 Constraint Examples

Note 2010-01-06: See here for examples of how to do constraint in a separate statement (not in the CREATE TABLE statement)

The following is a script showing an example of how to declare the following types of constraints.

  • Primary Key Constraint
  • Foreign Key Constraint
  • Default Constraint
  • Unique Constraint
  • Check Constraint

Each of the constraints (bar the Default Constraint) are violated at some point in the script also to show the error that each constraint violation provides.

Please note that I do something in this script that may seem a bit odd to some. For example, I don’t use an IDENTITY column instead I generate the JunkID Primary Key manually. Otherwise the script should be fairly standard to those people…

/* Creates a Constraining Table with a UNIQUE Constraint */ CREATE TABLE JunkTypes( [Type] CHAR(10) NOT NULL CONSTRAINT U_JunkTypes_Type UNIQUE ) INSERT INTO JunkTypes([Type]) VALUES('Lots') INSERT INTO JunkTypes([Type]) VALUES('Some') INSERT INTO JunkTypes([Type]) VALUES('ABitSpose') INSERT INTO JunkTypes([Type]) VALUES('MegaHeaps') INSERT INTO JunkTypes([Type]) VALUES('PitAnts') -- Insert Duplicate... Prevented by UNIQUE constraint INSERT INTO JunkTypes([Type]) VALUES('PitAnts') SELECT * FROM JunkTypes /* Creates a table with - Primary key constraint - Check Constraint with a range of values - Foreign Key constraint - Default constraint */ CREATE TABLE Junk ( JunkID int NOT NULL CONSTRAINT PK_Junk_JunkID PRIMARY KEY , SalesPrice money NOT NULL CONSTRAINT CH_Junk_SalesPrice CHECK (SalesPrice >= 1 AND SalesPrice <=100) , [Type] CHAR(10) NOT NULL CONSTRAINT FK_Junk_Type FOREIGN KEY REFERENCES JunkTypes([Type]) , DateMade datetime NOT NULL CONSTRAINT DF_Junk_DateMade DEFAULT GetUtcDate() ); -- This Insert Prevented by CH_Junk_SalesPrice CHECK constraint INSERT INTO Junk (JunkID, SalesPrice, [Type]) SELECT COALESCE((SELECT MAX(JunkID) FROM Junk) + 1, 0) , 0 , 'Lots' -- This Insert Prevented by FK_Junk_Type FOREIGN KEY constraint INSERT INTO Junk (JunkID, SalesPrice, [Type]) SELECT COALESCE((SELECT MAX(JunkID) FROM Junk) + 1, 0) , 1 , 'Plots' -- OK INSERT INTO Junk (JunkID, SalesPrice, [Type]) SELECT COALESCE((SELECT MAX(JunkID) FROM Junk) + 1, 0) , 2 , 'Lots' -- OK INSERT INTO Junk (JunkID, SalesPrice, [Type]) SELECT COALESCE((SELECT MAX(JunkID) FROM Junk) + 1, 0) , 23 , 'ABitSpose' -- OK INSERT INTO Junk (JunkID, SalesPrice, [Type]) SELECT COALESCE((SELECT MAX(JunkID) FROM Junk) + 1, 0) , 2 , 'PitAnts' -- This insert Prevented by the PK_Junk_JunkID Primary Key Constraint INSERT INTO Junk (JunkID, SalesPrice, [Type]) SELECT 1 , 2 , 'PitAnts' -- This Insert Prevented by CH_Junk_SalesPrice CHECK constraint INSERT INTO Junk (JunkID, SalesPrice, [Type]) SELECT COALESCE((SELECT MAX(JunkID) FROM Junk) + 1, 0) , 101 , 'Lots' SELECT * FROM Junk DROP TABLE Junk DROP TABLE JunkTypes GO
Advertisements

One Response to “Sql Server 2005 Constraint Examples”

  1. Adding new Table Constraints to Already Existing Tables « Randomos Technos Says:

    […] here for example of how to do constraints in the CREATE TABLE statement […]


Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: