Creating Your First Table in Microsoft SQL Server
- 2/15/2013
Adding constraints to a table
SQL Server 2012 allows you to add several constraints to a table. The primary goal of most constraints is data integrity. In other words, their purpose is to improve the validity and consistency of your data. This section covers five constraints: primary key, default, unique, check, and foreign key.
Primary key constraints
As previously stated, a primary key is a column that contains a unique list of values. Often an integer column is added to a table with the identity property and is used as the primary key. However, you can create a primary key from almost any column or combination of columns. The main limitations are that the column cannot allow nulls, the values must be unique, and you can have only one primary key per table. Since you’ve already created two tables, you’ll create primary keys on those tables. Both the Employee and Address tables have ID values that are unique and can be used as primary keys.
Default constraints
Default constraints are perfect when you have a column that typically contains a specific value. A really good candidate for this is a column that has a data type of bit. The bit data type only accepts 1 or 0 (true or false). If you add an Active column to the Employee table that specifies whether an employee is currently working for the company, the default value will probably be true or 1. Therefore, you should set the default value for that column accordingly.
Unique constraints
Unique constraints are sometimes confused with primary keys. These constraints simply ensure that duplicate values cannot be inserted into the corresponding column. For example, assume that you must add a column for Social Security numbers to the Employee table. Since Social Security numbers are truly unique values, you should add a unique constraint to ensure that a given Social Security number is entered only once.
Check constraints
The final constraint, check, allows you to check the value that is being inserted against logical expressions. This constraint is similar to the foreign key column in that it controls what values are inserted. The foreign key column gets its values from another table, while check constraints use expressions.
Add constraints using SSMS
Execute the following query prior to following the steps in this exercise:
USE SBSChp4TSQL;
ALTER TABLE HumanResources.Employee
ADD Active bit NOT NULL;
ALTER TABLE HumanResources.Employee
ADD SocialSecurityNumber varchar(10) NOT NULL;
USE SBSChp4SSMS;
ALTER TABLE HumanResources.Employee
ADD Active bit NOT NULL;
ALTER TABLE HumanResources.Employee
ADD SocialSecurityNumber varchar(10) NOT NULL;
Ensure that SSMS is open and you are connected to your server.
Expand the Databases folder.
Expand the SBSChp4SSMS database.
Expand the Tables folder.
Right-click the HumanResources.Employee table, and then select Design.
Right-click the EmployeeID column, and then select Set Primary Key from the context menu.
Select the Active column.
In the Properties window, locate Default Value or Binding property.
Type 1 as the property value.
In the menu bar, click the Manage Indexes and Keys icon.
Click the Add button in the Indexes/Keys window.
Locate the Name property and type UQ_Employee_SSN as the property value.
Locate the Is Unique property and set the value to Yes.
Locate the Type property and set the value to Unique Key.
Click Close.
In Object Explorer, expand the HumanResources.Employee table if it is not already expanded.
Right-click the Constraints column, and then select New Constraint from the context menu.
In the Check Constraint dialog box, change the value for the Name property to CK_Employee_Gender_MF.
Click the Value box for the Expression property, and click the ellipsis that appears.
In the Expression box, enter ([Gender = ’Female’ OR [Gender] = ’Male’).
Click Close.
Click Save.
)
)
)
Add a computed column using T-SQL
Open the query editor in SSMS.
In the query editor, enter and execute the following T-SQL code:
USE SBSChp4TSQL; ALTER TABLE HumanResources.Employee ADD CONSTRAINT PK_HumanResourcesEmployee_EmployeeID PRIMARY KEY (EmployeeID); ALTER TABLE HumanResources.[Address] ADD CONSTRAINT PK_HumanResourcesAddress_AddressID PRIMARY KEY (AddressID);ALTER TABLE HumanResources.Employee ADD CONSTRAINT DF_HumanResourcesEmployee_Active_True DEFAULT(1) FOR Active; ALTER TABLE HumanResources.Employee ADD CONSTRAINT UQ_HumanResourcesEmployee_SocialSecurityNumber UNIQUE (SocialSecurityNumber);
Foreign key constraints
The integrity of data is the most important concern in a database. If you allow the insertion of bad data, then that is what is going to come out. Foreign keys play a vital role in enforcing the referential integrity of the database. You may have noticed the EmployeeID column in the Address table. To ensure that only employee IDs that exist in the Employee table are inserted into the Address table, you need to create a foreign key constraint.
Create foreign key constraints using SSMS
Prior to following the steps of this exercise, execute this script:
USE SBSChp4SSMS
ALTER TABLE HumanResources.Address
ADD CONSTRAINT PK_HumanResourcesAddress_AddressID
PRIMARY KEY (AddressID);
Ensure that SSMS is open and you are connected to your server.
Expand the Databases folder.
Expand the SBSChp4SSMS database.
Expand the Tables folder.
Expand the HumanResources.Address table.
Right-click the Keys folder and select New Foreign Key.
In the Foreign Key Relationships dialog box, locate the Name property and type FK_Employee_To_Address_On_EmployeeID as the value.
Click in the text box next to the Table and Columns Specification property.
Click the ellipsis button that appears.
In the Tables and Columns dialog box, select Employee(HumanResources) from the Primary Key Table drop-down list.
Select EmployeeID from the drop-down list directly below the Primary Key Table drop-down list.
In the drop-down list to the right, select EmployeeID.
Click OK.
Click Close.
Click Save.
If a warning window appears, click Yes.
)
Create foreign key constraints using T-SQL
Open the query editor in SSMS.
In the query editor, enter and execute the following T-SQL code:
USE SBSChp4TSQL; ALTER TABLE HumanResources.Address ADD CONSTRAINT FK_Employee_To_Address_On_EmployeeID FOREIGN KEY (EmployeeID) REFERENCES HumanResources.Employee(EmployeeID);