Getting Started:
Constraints are rules assigned to columns in your SQL tables. The main use of a Constraint is to help ensure that data integrity measures are met. Data integrity is very important. The whole idea of Data Integrity is to maintain a consistency of data. Constraints assist with Data Integrity, referential integrity(Primary Keys and Foreign Keys), and aids Normalization for your Database tables.
Data Integrity:
Lets say that we have a SQL table named “Contacts” and one of the columns in this table is “PhoneNumber”. Now when you think of storing a phone number, there’s many formats that come to mind:
- (xxx) xxx – xxxx
- xxx-xxx-xxxx
- xxxxxxxxxx
Data Integrity is ensuring that you pick a format for “Phone Numbers” and stick to that format. You would be violating Data Integrity if you chose to store format #1, #2 and #3 into the PhoneNumber column of the Contacts Table.
Types of Constraints:
On SQL Server, there are many types of Constraints that you can utilize. Below are the most commonly used:
- PRIMARY KEY
- FOREIGN KEY
- UNIQUE
- NOT NULL
- CHECK
Using “PRIMARY KEY” Constraint:
The Primary Key Constraint, enforces that a column value must be unique and specifies the column as the Primary Key.
Example of Creating a Primary key on a CREATE TABLE statement:
-----------------------------------------------
CREATE TABLE TableName
(
[ID] INT IDENTITY(1,1) PRIMARY KEY,
[Column1] NVARCHAR(200),
[Column2] NVARCHAR(200)
)
-----------------------------------------------
You can add a Primary Key Constraint to an existing table:
-----------------------------------------------
ALTER TABLE TableName
ADD CONSTRAINT Give_Constraint_a_Name PRIMARY KEY(ColumnName)
-----------------------------------------------
Using “FOREIGN KEY” Constraint:
The Foreign Key Constraint allows you to specify that a column in your table is the Primary key to another table, thus granting you linking access to the other table via the value in this column. This promotes referential integrity, meaning that you're making your SQL Table a dependent to another table(Child of Parent).
I’ve created a simple example, there are two tables “Table1” and “Table2”":
-----------------------------------------------
/*Parent Table */
CREATE TABLE Table1
(
[Table1ID] INT IDENTITY(1,1) PRIMARY KEY,
[Column1] NVARCHAR(200),
[Column2] NVARCHAR(200),
[Column3] NVARCHAR(200)
)
/*Dependent Table */
CREATE TABLE Table2
(
[Table2ID] INT IDENTITY(1,1) PRIMARY KEY,
[Table1ID] INT FOREIGN KEY REFERENCES Table1 (Table1ID),
[Column1] NVARCHAR(200),
[Column2] NVARCHAR(200),
[Column3] NVARCHAR(200)
)
-----------------------------------------------
NOTE: By adding a FOREIGN KEY constraint to your table. Consider the following:
- You can’t add a record to Table2 unless a record exists in Table1 and the Table1ID matches in both tables.
- You must delete a record from Table1 then Table2. You can’t delete from Table2 and then Table1.
You can add a FOREIGN KEY constraint to an existing table with the following syntax:
-----------------------------------------------
ALTER TABLE Table2
ADD CONSTRAINT Name_of_Constraint FOREIGN KEY (Table1ID) REFERENCES Table1 (Table1ID)
-----------------------------------------------
Using “UNIQUE” Constraint:
The Unique Constraint ensures that a column will contain a Unique Value:
-----------------------------------------------
CREATE TABLE TableName
(
[Table2ID] INT IDENTITY(1,1) PRIMARY KEY,
[Column1] NVARCHAR(200) UNIQUE,
[Column2] NVARCHAR(200),
[Column3] NVARCHAR(200)
)
-----------------------------------------------
As you can see I’ve used the UNIQUE Constraint on the [Column1] column on Table1. This is specifying that all Column1 values must be unique/different. If you attempt to INSERT a record with a value for Column1 which already exists in a record of Table1, then you will receive an error.
UNIQUE Constraints - Although functioning like a Primary Key Constraint, the difference is that you can apply this to multiple columnsand this constraint means that not a single value can be replicated in this column. This type of column could be used for Social Security Numbers, Credit Card Numbers, email addresses, etc.. The difference is that Primary key is used as the column to link other tables together via referential integrity. Unique doesn't carry this responsibility, even though it could.
You can add a UNIQUE Constraint to an existing table:
-----------------------------------------------
ALTER TABLE TableName
ADD CONSTRAINT Name_of_Constraint UNIQUE (Column1)
-----------------------------------------------
Using “NOT NULL” Constraint:
The NOT NULL Constraint enforces that a value must exist and cannot contain "NULL" / unknown. Below is an example:
-----------------------------------------------
CREATE TABLE TableName
(
[Table2ID] INT IDENTITY(1,1) PRIMARY KEY,
[Column1] NVARCHAR(200) NOT NULL,
[Column2] NVARCHAR(200) NOT NULL,
[Column3] NVARCHAR(200) NOT NULL
)
-----------------------------------------------
You can add this constraint to columns in an existing table:
-----------------------------------------------
ALTER TABLE TableName
ALTER COLUMN [Column1] NVARCHAR(200) NOT NULL
-----------------------------------------------
Using “CHECK” Constraint:
The CHECK CONSTRAINT allows you to verify that a value is met for a column. This assists by restricting any unwanted values in a column:
-----------------------------------------------
CREATE TABLE Table1
(
[Table2ID] int identity(1,1) PRIMARY KEY,
[Column1] nvarchar(200),
[Column2] nvarchar(200),
[Column3] money,
CONSTRAINT Name_of_Constraint CHECK ([Column3] >= 0.00)
)
-----------------------------------------------
You can add a CHECK Constraint to check for multiple values:
-----------------------------------------------
CREATE TABLE TableName
(
[Table2ID] int identity(1,1) PRIMARY KEY,
[Column1] nvarchar(200),
[Column2] nvarchar(200),
[Column3] money,
CONSTRAINT Name_of_Constraint CHECK ([Column2] = 'Single', 'Married', 'Divorced')
)
-----------------------------------------------
NOTE: That when adding a CHECK Constraint accepting multiple values as demonstrated above, the value you’re inserting into [Column2] must match the values in the Constraint(‘Single’, ‘Married’, ‘Divorced’), or else you’ll receive an error and the INSERT will fail.
You can add a CHECK Constraint to a column in an existing table:
-----------------------------------------------
ALTER TABLE TableName
ADD CONSTRAINT Name_of_Constraint CHECK ([Column3] > 0.00)
-----------------------------------------------
DROPPING CONSTRAINTS:
The syntax to drop a constraint is in the following manner:
-----------------------------------------------
ALTER TABLE Table1
DROP CONSTRAINT Name_of_Constraint
-----------------------------------------------
IMPORTANT NOTES FOR REFERENTIAL CONSTRAINTS: USING THE RIGHT ORDER:
- You must define a Primary Key constraint on a column before using a Foreign Key constraint on that column in another table.
- When dropping a table or removing constraints you must do so in the right order. Foreign key first, Primary key after.