SQL Table Constraints

When creating tables you would want to use constraints to enforce data integrity. In the picture above you can see a list of the constraints and why they are used in your tables. Next we will demonstrate how to use these constraints in our code and on our HR.Employees table.

In the following code example on Line 2 we see a demonstration of using a PRIMARY KEY constraint for the EmpID field. This will be the value that uniquely identifies each of the records in the table. This will also ensure that a NULL value cannot be placed in that field. Another way to ensure that NULL values cannot be used in a field is to use the NOT NULL constraint as seen on line 3, 4, and 5.

You may also notice that on line 7 we are explicitly allowing NULL values for the BirthDate field. If NULL or NOT NULL is not specified on a column then those fields will also allow NULL values. (such as line 6, 8, and 9)

Another way of ensuring a value is placed into a field is to use a DEFAULT constraint such as the one used for the HireDate field on line 6. In this case the DEFAULT constraint will put the current date into the field if a date was not supplied when the record was inserted.

On fields that would not be useful as a PRIMARY KEY but will still require distinct values you can use the UNIQUE constraint to ensure that duplicate values will not be used in a column. In the example above we are using a UNIQUE constraint on the PhoneNumber field to ensure that all employees have a unique phone number. While a PRIMARY KEY will not allow any NULL values, the UNIQUE constraint will allow one record to have a NULL value.

If you want to add a constraint after a table has already been created you can do so by altering the table and adding the constraint. Be sure to give the constraint a descriptive name.

In the following example we will add a CHECK constraint to ensure that any date that is added to the HireDate column will need to be 18 years greater than the BirthDate field.

Finally, before we can add a FOREIGN KEY constraint we will first need to create a parent table named HR.Departmens for the DeptCode field. We will keep it simple with just two columns DeptID and DeptName. We will also INSERT four rows into the HR.Departments table. Then we will alter the Employees table to create the relationship that will enforce referential integrity between the two tables.

You may have noticed that we are taking the DeptCode field from the HR.Employees table and connecting it to the DeptID field in the HR.Departments table. While the names for the fields can be different you will need to ensure that the data types and fields sizes are the same for both columns.

The final line of the Foreign Key constraint is what action to take if a value was updated or deleted in the parent table. In this case, if someone updated a value of the DeptID field in the HR.Departments table it would CASCADE down and update the same values in the child table, which would be the DeptCode field in the HR.Employees table. If someone deleted a value in the DeptID field in the HR.Departments table then the DeptCode field in the HR.Employees table that previously matched that value would be set to NULL. If this line is not added to the Foreign Key constraint, updated and deletes would not be allowed on the DeptID field of the HR.Departments tables.

Be the first to comment on "SQL Table Constraints"

Leave a comment

Your email address will not be published.


*


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