What are constraints in SQL Server?
- Constraints enforce the data integrity in the database and protect columns of the table from unwanted values.
- It defines the rules regarding the values allowed in the columns.
- It uses constraint definitions to build high-performance query execution plans.
Following are the constraints in SQL Server:1. NOT NULL
2. CHECK
3. UNIQUE
4. PRIMARY KEY
5. FOREIGN KEY
1.
NOT NULL:- It specifies that the column does not accept the NULL values.
Example:CREATE TABLE employee
(
empId int NOT NULL,
empName varchar(20) NOT NULL
);
2.
CHECK: - It enforces the domain integrity by limiting the values that can be put in a column.
- It specifies a boolean value(TRUE / FALSE).
Example:CREATE TABLE employee
(
empID int NOT NULL,
empName varchar2(20),
CHECK (empId > 0)
);
3.
UNIQUE:- It enforces the uniqueness of the values in a set of columns.
Example:CREATE TABLE employee
(
empId int NOT NULL,
empName varchar2(20),
UNIQUE (empId)
);
4.
PRIMARY KEY:- It identifies the column or set of columns that values are uniquely identify a row in a table.
- Each table has only one primary key.
Example:CREATE TABLE employee
(
empId int NOT NULL,
empName varchar2(20),
PRIMARY KEY(empiId)
);
5.
FOREIGN KEY:- It identifies the relationships between tables.
- A foreign key in one table points to a candidate key in another table.
Example:CREATE TABLE employee
(
empId int NOT NULL,
empName varchar2(20),
deptId int FOREIGN KEY REFERENCES department (deptId)
);
