Hello,
SQL Server Constraints can be defined as rules that govern the values that are inserted into a column. These rules help in enforcing data integrity. For example if a NOT NULL constraint is defined over a column in a particular table, it means that column will not accepted any NULL values.
Constraints used in SQL Server could be classified as follows:
1) NOT NULL
2) CHECK
3) PRIMARY KEY
4) UNIQUE KEY
5) FOREIGN KEY
Lets discuss each one briefly.
NOT NULL Constraint
NOT NULL Constraints are those that disallow a null value. The NOT NULL constraint is defined at the time the table is created. Also if a column is defined to be the Primary key column, it automatically becomes a not null column, because NULL cannot be a key for a row.
CHECK Constraint
Check constraints are those rules that enforce domain level integrity of data by limiting the values entered within a column. They can be created using any logical (Boolean) expression that returns either TRUE or FALSE, based on the operators used. CHECK constraints work at row level.
Note: The CHECK constraint can be overridden by NULL value.
They are not validated during DELETE operation.
Let us take an example. Suppose we need to enter the marks obtained by students in Science subject. The constraint applied to table dbo.Science_Marks will make sure that Max marks should not exceed 100 and Min marks should not be below 0.
--Creating the Table dbo.Science_Marks
IF OBJECT_ID ('dbo.Science_Marks', 'U') IS NOT NULL
DROP TABLE dbo.Science_Marks;
GO
CREATE TABLE dbo.Science_Marks
(StuID int PRIMARY KEY, StuName nvarchar (50),
Sci_Marks tinyint)
GO
--Applying the Constraint
ALTER TABLE dbo.Science_Marks ADD CONSTRAINT CK_Marks_Sci_Marks
CHECK (Sci_Marks >=0 AND Sci_Marks <= 100)
--Inserting records
INSERT INTO dbo.Science_Marks values(1,'Abhishek',85)
INSERT INTO dbo.Science_Marks values(2,'Abhi',67)
INSERT INTO dbo.Science_Marks values(3,'Saurav',76)
INSERT INTO dbo.Science_Marks values(5,'Wasim',85)
INSERT INTO dbo.Science_Marks values(6,'Ritesh',99)
--The above entries would be allowed, but on inserting the below record, SQL Server will throw an error message as the constraint does no allow the marks to be above 100 or below 0.
INSERT INTO dbo.Science_Marks values(4,'Aniket',102)
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "CK_Marks_Sci_Marks". The conflict occurred in database "BLOG_TEST", table "dbo.Science_Marks", column 'Sci_Marks'.
The statement has been terminated.
PRIMARY KEY CONSTRAINT
Primary key can be defined as that value which uniquely identifies one and only one row of a table. Each row has its own primary key value. Every table has at least one column that uniquely identifies each row of the table, thus maintaining the entity integrity. Setting a column as primary key automatically enforces the NOT NULL constraint on that column.
A table can have only one Primary Key Constraint. If a Primary key constraint is applied to a column of an existing table, SQL server checks the column for any NULL value or any duplicate values, if found, Primary key cannot be defined till the redundancy is rectified or the NULL value is supplied with a valid entry.
Suppose in the previous example if we try to insert another row, having the same value for StuID column, SQL Server will throw an error.
INSERT INTO dbo.Science_Marks values(6,'Akanksha',83) Msg 2627, Level 14, State 1, Line 1 Violation of PRIMARY KEY constraint 'PK__Science___6CDFAB757F60ED59'. Cannot insert duplicate key in object 'dbo.Science_Marks'. The statement has been terminated.
UNIQUE KEY CONSTRAINT
A Unique Key Constraint enforces uniqueness of the values in the column over which they are defined. Even primary key constraint imposes the rule of uniqueness but it also imposes NOT NULL constraint. Talking of uniqueness, among other unique values, NULL is also a unique value, i.e. the column having a unique key constraint may allow just one NULL value (unlike Primary key that does not allow even a single NULL value).
Suppose in the previous example we want to add a Unique constraint on the StuName column, we can use the below script:
ALTER TABLE dbo.Science_Marks ADD CONSTRAINT IX_Student_Name UNIQUE(StuName) GO Now if we try to make another entry with same value for StuName column, SQL Server throws an error. INSERT INTO dbo.Science_Marks values(7,'Ritesh',83) Msg 2627, Level 14, State 1, Line 1 Violation of UNIQUE KEY constraint 'IX_Student_Name'. Cannot insert duplicate key in object 'dbo.Science_Marks'. The statement has been terminated.
FOREIGN KEY CONSTRAINT
Also known as Referential Constraints or Referential integrity constraints, foreign key constraints are those that establish relationships between two or more tables.
Fig 1
In the above diagram, we can see that the two tables have ShiftID column common in them. ShiftID column is a Primary key column in Shift table, but not in EmployeeDepartmentHistory table. Here ShiftID column in Shift table is a foreign key to EmployeeDepartmentHistory table.
A foreign key column does not have to be a primary key column in the other table but should be a unique key column.
A foreign key constraint can be created during creating the table.
CREATE TABLE CUSTOMERS(
CUST_ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
PRIMARY KEY (CUST_ID)
);
CREATE TABLE ORDERS (
ID INT NOT NULL,
DATE DATETIME,
CUSTOMER_ID INT references CUSTOMERS(CUST_ID),
AMOUNT float,
PRIMARY KEY (ID)
);
In case the Order table has already been created and the foreign key constraint has not been set, we could use the below script to create one:
ALTER TABLE ORDERS ADD FOREIGN KEY (Customer_ID) REFERENCES CUSTOMERS (CUST_ID);
Regards
Abhishek Kumar
Like us on FaceBook | Follow us on Twitter
Join the fastest growing SQL Server group on FaceBook
