Table Constraints in SQL 2005

In this article we will explore different table constraints with suitable examples.

1. Introduction

 

Constraints are rules that decide what kind of data can enter into the database tables. SQL server has six types of constraints and we will explore all these constraints here with suitable examples. The constraints that we are going to explore are listed below:

1. Primary Key Constraint

2. Foreign Key Constraint

3. Not Null Constraint

4. Unique constraint

5. Default Constraint

6. Check Constraint

2. First Create two tables

 

To explain these constraints we need two tables. First let us create these tables. Run the scripts shown below to create the tables. Copy and paste the code into the new Query Editor window then execute it.

 

CREATE TABLE Student(StudId smallint, StudName varchar(50), Class tinyint);

CREATE TABLE TotalMarks(StudentId smallint, TotalMarks smallint);

Go

 

Note that there are no constraints at present on these tables. We will add the constraints one by one.

 

3. Primary Key Constraint

 

A table column with this constraint is called the key column for the table. This constraint helps the table to make sure that the value is not repeated and also that there are no null entries. We will mark the StudId column of the Student table as the primary key. Follow these steps:

1. Right click the student table and click on the modify button

2. From the displayed layout select the StudId row by clicking the Small Square like button on the left side of the row.

3. Click on the Set Primary Key toolbar button to set the StudId column as primary key column.

 

Now this column does not allow null values and duplicate values. You can try inserting values to violate these conditions and see what happens. A table can have only one Primary key. Multiple columns can participate on the primary key column. Then the uniqueness is considered among all the participant columns by combining their values.

 

4. Not Null Constraint

 

This constraint is useful to stop storing the null entries in the specified columns. We will mark student name column as not null column. This allows us to always having some entries in the student name column of the student table without having NULL. Follow the steps below:

1. As you did previously, bring up the table design view by clicking the modify context menu for the table.

2. Remove the check mark as shown in the picture below. This action will enable the Not Null constraint for the StudName column.

 

 

5. Default Constraint

 

Default constraint allows you set a default value for the column. That means that when a row is created for the first time, and there is no entry specified for the column that has a default constraint on it, then the default value is stored in the column. Note that this not a Not Null constraint and do not confuse the default value constraint with disallowing the Null entries. Default value for the column is set only when the row is created for the first time and column value is ignored on the Insert. Modification to the column with NULL value  or even the Insert operation specifying the Null value for the column is allowed.

 

Let us set the Default value of 1 for the Class. Follow these steps:

1. Bring up the table designer

2. Select the Class Row as you already did.

3. At the bottom of the layout, you will see a Column properties as shown in the below picture. Set the default as shown below:

 

 

6. Unique constraint

 

A unique constraint does not allow the duplicate values. But, the column can have multiple Null values. For our example we do not require any unique constraints. Follow the example below if you want to set a unique constraint for a column:

 

ALTER TABLE Student ADD CONSTRAINT UQ_CONSName UNIQUE (StudName)

 

The above T-SQL sets a unique constraint for the column StudName. UQ_CONSName is the name given to the constraint. It will be useful if we want to delete the constraint later.

 

7. Check Constraints

 

Check constraints are custom rules that will be applied on the column to take decision of the data is satisfying the checked rules. Let us set some check constraint for the TotalMarks column of the TotalMarks table. The data can be stored on this column only when the data is greater than zero or less then 1200.

 

1. First set the not null constraint for both the columns in the table.

 

 

2. Expand the TotalMarks table and right click the Constraints folder. From the displayed context menu select the New Constraint

 

 

3. From the displayed check constraints dialog, set the above said check rules for the column. To do that, type the expression for the column TotalMarks in the Expression field. The expression is: (TotalMarks < 1201) AND (TotalMarks > 0)

 

 

4. Change the name of the constraint to CK_TotalMarks. Then click close button. Refresh the Constraints folder by selecting the refresh from the context menu of the Constraints folder. The added constrains with the name is shown below:

 

 

Now the Total Marks column does not allow the negative marks or marks more than 1200.

 

8. Foreign Key Constraint

 

Foreign key constrains are useful for maintaining the relationship between the tables. If a column allows the values only when the value exists on the column of some other table then the column is said to have the foreign key.  A foreign key should refer primary key or unique key column of some other tables.

 

In our example we will create a foreign key for the column StudentId of the TotalMarks table. Let me introduce the Database diagram to do this task. You can play around it as the diagrams are not only to showing the existing database relationship, they exist actually to design your database easily. You can change the data types, add relationships (That we are going to see now), introduce constraints etc..

 

Follow these steps to create the Foreign Key constraint using the database diagram [The easy way]:

1. Expand the database in which you created the two tables for this example. Then Right click the Database diagram and choose New Database Diagram
 

 

 

2. In the add table dialog select both the tables. If you use different database then select the tables that we created for this example. Then click the Add button. Click the close button to dismiss the dialog.
 

 

3. You will see the following two tables on the design surface

 

 

4. Now hold down the left mouse button on the StudId and drag & drop the mouse pointer on the StudentId. This will create a foreign key in the Total Marks table.

 

 

5. Accept the default  by clicking the OK button from the displayed dialog after reviewing the details it displayed

 

 

6. The database diagram now shows the relationship between the tables

 

 

Note: All the Steps are done using the SQL Server 2005. I am stopping it here. You can go ahead and examine each constraint by inserting some sample data to the tables.