What are constraints in SQL Server?

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)
);
What is an index?
Indexes of SQL Server are similar to the indexes in books. They help SQL Server retrieve the data quicker.........
What is RAID?
RAID stands for Redundant Array of Inexpensive Disks. It provide fault tolerance to database servers........
Reasons of poor performance of query
No indexes. Excess recompilations of stored procedures. Procedures and triggers without SET NOCOUNT ON........
Post your comment