1. Introduction to Constraints

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

1. Primary Key
2. Foreign Key
3. Not Null
4. Unique
5. Default
6. Check

2. Create Sample Tables

To explain all these constraints, we need two tables. First, let us create these tables. One can run the below scripts to create the tables.

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

3. Primary Key

A table column with ‘Primary Key’ is called as the ‘key column’ for the table. This constraint helps the table to make sure that the values for the columns are repeated. Also, it makes sure there will not be any null entries. We will mark the StudId column of the Student table as a primary key. The steps are below:

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 a button on the left side of the row.
3. Click on the “Set Primary Key” toolbar button to set the StudId column as a primary key column.

The below picture shows setting a column as a Primary Key:

Now StudId column does not allow null values and duplicate values. One can try inserting values to violate these conditions and see what happens. A table can have only one Primary key. “Multiple columns” can also play on the primary key and in that case, we call it as a “Composite Primary Key”. When the key is composite, we see the uniqueness among all the participant columns by mixing their values.

4. Not Null Constraint

The Not Null  constraint is useful to stop storing the null entries in the specified columns. We will mark student name column as a not null column. This allows us always having some entries in the student name column of the student table without having NULL. The steps below shows how to add a Not Null Constraint:

1. As we 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 below picture. This action will enable the Not Null for the StudName column.

5. Default Constraint

Default Constraint allows you to set a default value for the column. This means when a row is created for the first time without column value, the Default Constraint supplies a value for it. Note that this is not a Not Null. Later through the update statement, one can set null to this column. The default value for the column is set only when the row is created for the first time. For example, when the column value is ignored on the Insert, Default constraint comes in rescue. 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 column of Student table. The steps are below:

1. Bring up the table designer.
2. Select the Class Row as we already did.
3. At the bottom of the layout, you will see a Column property as shown in the below picture. Here, one can set the default as ‘1’ in the grid entry ‘Default value or Binding’. The below picture shows this:

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 below example if you want to set it for a column:

The T-SQL in the above statement sets a Unique Constraint for the column StudName. We gave the name UQ_CONSName for it in the alter statement. It will be useful if we want to delete the Constraint later.

7. Check Constraint

When the user enters the data for a column through Insert or Update, we can validate the value and discard it. We can do this through Check Constraints. SQL Server will use this Check rule to decide whether the value can enter the table column or not. Here, We will set the Check Constraint for the TotalMarks  column of the Student table. It will allow the values for this column only when it is greater than zero or less than 1200.

1. First, set the not null 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, we set the above said check rules for the column. To do that, type the expression for the TotalMarks column in the Expression field. The expression is: (TotalMarks < 1201) AND (TotalMarks > 0)
4. Change the name of the constraint as CK_TotalMarks. Then click the close button. Refresh the Constraints folder by selecting the refresh from the context menu of the Constraints folder. The added constraints with the name is shown below: 

Now the Total Marks column does not allow the negative marks or marks more than 1200. One can try it with a Insert or Update Statements.

8. Foreign Key

Foreign Key constraints are useful for maintaining the relationship between two tables. If a column ‘X’ allows the values only when the value exists on the column of some other table, then the column ‘X’ is a Foreign Key.  This key will refer a primary key or a unique key column of some other tables.

In our example, we will create a Foreign Key for the column StudentId of the TotalMarks table. We will use the SQL Server’s Database Diagram to do this task. One can play around it as the diagrams are not only to show the existing database relationship, they exist to design database easily. One can change the data types, add relationships (that we will see now), introduce constraints and so on.

8.1 Steps to Create Foreign Key using Database Diagram

Follow these steps to create the Foreign Key constraint using the Database Diagram:

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

In the ‘Add Table’ dialog, select both the tables (If you use a different database, then select the tables you created for this example). Then click the Add button. Finally, click the close button to dismiss the dialog.

Now, we will see the following two tables on the design surface

Now hold down the left mouse button on the StudId. Then drag & drop the mouse pointer on the StudentId. This will create a foreign key in the TotalMarks table.

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

The database diagram now shows the relationship between the tables.

Note: All the Steps were done using the SQL Server 2005. We will stop it here. One can examine each constraint by inserting some sample data to the tables.

• C# HTTP Remote Server & SoapSuds
• C# StatusStrip Control Explained

Categories: SQL Server

Tags: Check Constraint, Default Constraint, Foreign Key, Not Null Constraint, Primary Key, Unique Constraint

This site uses Akismet to reduce spam. Learn how your comment data is processed.