Saturday, November 19, 2011

SQL 2005 - How do I use various Constraints in a database?

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 all these constraints, we need two tables. First, let us create these tables. Run the below scripts to create the tables. Copy paste the code on 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);

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 "Primary Key" constraint is called as the "key column" for the table. This constraint helps the table to make sure that the value is not repeated and also no null entries. We will mark the StudId column of the Student table as a 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 a primary key column.
Fig 1. Setting key column for the Table
Now StudId 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 also participate on the primary key and in that we call that as "Composite Primary Key". When the key is composite, the uniqueness is considered among all the participant columns by combining 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. 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 below picture. This action will enable the Not Null constraint for the StudName column.

Fig 2. Setting Not Null Constraint

5. Default Constraint

"Default" constraint allows you set a default value for the column. That means when a row is created for the first time, and there is no entry specified for the column that has default constraint set, then the default value is stored in the column. Note that this is not a Not Null constraint and do not confuse the default value always enters the column disallowing the Null entries. The 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 column of Student table. 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:
Fig 3. Setting default constraint

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 a unique constraint for a column:


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 can be applied on the column to take the decision of value is allowed or not. Let us set some check constraint for the TotalMarks column of the Student table. The data can be stored in this column only when the data is greater than zero or less than 1200.

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

Fig 4. Set Not Null

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

Fig 5. Add new check 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 column TotalMarks in the Expression field. The expression is: (TotalMarks < 1201) AND (TotalMarks > 0)

Fig 6. Add check condition for check constraint

4) Change the name of the constraint to 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:

Fig 7. Check constraint in the database object tree

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

8. Foreign Key Constraint

"Foreign key" constraints are useful for maintaining the relationship between two 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 show the existing database relationship, they exist actually to design your database in an easy way. 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"

Fig 8. New database diagram

2) In the add table dialog select both the tables. If you use a 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.

Fig 9. Add Tables to database diagram

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

Fig 10. Selected tables in the database diagram

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.

Fig 11. Add Table relationship using database diagram

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

Fig 12. Set Name for Foreign Key

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

Fig 13. Table relationship as shown in the database diagram

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.

No comments:

Post a Comment

Leave your comment(s) here.

Like this site? Tell it to your Firend :)