Constraints are some rules that enforce on the data to be enter into the database table. Basically constraints are used to restrict the type of data that can insert into a database table.Constraints can be defined in two ways:

Column Level

The constraints can be specified immediately after the column definition with the CREATE TABLE statement. This is called column-level constraints.
Table Level

The constraints can be specified after all the columns are defined with the ALTER TABLE statement. This is called table-level constraints.

Types of SQL Constraints

In Microsoft SQL Server we have six types of constraints

Primary Key Constraints

Primary key is a set of one or more fields/columns of a table that uniquely identify each record/row in database table. It can not accept null, duplicate values.

Primary key constraint at column level
```
 CREATE TABLE table_name
(
col1 datatype [CONSTRAINT constraint_name] PRIMARY KEY,
col2 datatype
); 
```
Primary key constraint at table level
```
 ALTER TABLE table_name
ADD[CONSTRAINT constraint_name] PRIMARY KEY (col1,col2)
```
Unique Key Constraints

Unique key is a set of one or more fields/columns of a table that uniquely identify each record/row in database table.It is like Primary key but it can accept only one null value and it can not have duplicate values

Unique key constraint at column level
```
 CREATE TABLE table_name
(
col1 datatype [CONSTRAINT constraint_name] UNIQUE,
col2 datatype
); 
```
Unique key constraint at table level
```
 ALTER TABLE table_name
ADD[CONSTRAINT constraint_name] UNIQUE (col1,col2)
```

Foreign Key Constraints

Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values.

Foreign key constraint at column level


 CREATE TABLE table_name
(
col1 datatype [CONSTRAINT constraint_name] REFERENCES referenced_table_name(referenced_table_column_name),
col2 datatype
);

Foreign key constraint at table level


 ALTER TABLE table_name
ADD[CONSTRAINT constraint_name] REFERENCES referenced_table_name(referenced_table_col)

Not Null Constraints

This constraint ensures that all rows in the database table must contain value for the column which is specified as not null means a null value is not allowed in that column.

Not Null constraint at column level
```
 CREATE TABLE table_name
(
col1 datatype [CONSTRAINT constraint_name] NOT NULL, 
col2 datatype
);
```
Not Null constraint at table level
```
 ALTER TABLE table_name
ALTER COLUMN col1 datatype NOT NULL
```

Check Constraints

This constraint defines a business rule on a column in the database table that each row of the table must follow this rule.

Check constraint at column level


 CREATE TABLE table_name
(
col1 datatype [CONSTRAINT constraint_name] CHECK (condition), 
col2 datatype
);

Check constraint at table level


 ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK(condition) 

----------------------------------

SQL Integrity Constraints

Integrity Constraints are used to apply business rules for the database tables.

The constraints available in SQL are Foreign Key,Not Null, Unique, Check.

Constraints can be defined in two ways
1) The constraints can be specified immediately after the column definition. This is called column-level definition.
2) The constraints can be specified after all the columns are defined. This is called table-level definition.

1) SQL Primary key:

This constraint defines a column or combination of columns which uniquely identifies each row in the table.

Syntax to define a Primary key at column level:

column name datatype [CONSTRAINT constraint_name] PRIMARY KEY

Syntax to define a Primary key at table level:

[CONSTRAINT constraint_name] PRIMARY KEY (column_name1,column_name2,..)

column_name1, column_name2 are the names of the columns which define the primary Key.
The syntax within the bracket i.e. [CONSTRAINT constraint_name] is optional.

For Example: To create an employee table with Primary Key constraint, the query would be like.

Primary Key at column level:

CREATE TABLE employee 
( id number(5) PRIMARY KEY, 
name char(20), 
dept char(10), 
age number(2), 
salary number(10), 
location char(10) 
);

CREATE TABLE employee
( id number(5) CONSTRAINT emp_id_pk PRIMARY KEY, 
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10)
);

Primary Key at column level:

CREATE TABLE employee 
( id number(5), 
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10),
CONSTRAINT emp_id_pk PRIMARY KEY (id)
);

Primary Key at table level:

CREATE TABLE employee 
( id number(5), NOT NULL,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10),
ALTER TABLE employee ADD CONSTRAINT PK_EMPLOYEE_ID PRIMARY KEY (id)
);

2) SQL Foreign key or Referential Integrity :

This constraint identifies any column referencing the PRIMARY KEY in another table. It establishes a relationship between two columns in the same table or between different tables. For a column to be defined as a Foreign Key, it should be a defined as a Primary Key in the table which it is referring. One or more columns can be defined as Foreign key.

Syntax to define a Foreign key at column level:

[CONSTRAINT constraint_name] REFERENCES Referenced_Table_name(column_name)

Syntax to define a Foreign key at table level:

[CONSTRAINT constraint_name] FOREIGN KEY(column_name) REFERENCES referenced_table_name(column_name);

For Example:

1) Lets use the "product" table and "order_items".

Foreign Key at column level:

CREATE TABLE product 
( product_id number(5) CONSTRAINT pd_id_pk PRIMARY KEY, 
product_name char(20),
supplier_name char(20),
unit_price number(10)
);

CREATE TABLE order_items
( order_id number(5) CONSTRAINT od_id_pk PRIMARY KEY,
product_id number(5) CONSTRAINT pd_id_fk REFERENCES, product(product_id),
product_name char(20),
supplier_name char(20),
unit_price number(10)
);

Foreign Key at table level:

CREATE TABLE order_items
( order_id number(5) ,
product_id number(5),
product_name char(20),
supplier_name char(20),
unit_price number(10)
CONSTRAINT od_id_pk PRIMARY KEY(order_id),
CONSTRAINT pd_id_fk FOREIGN KEY(product_id) REFERENCES product(product_id)
);

2) If the employee table has a 'mgr_id' i.e, manager id as a foreign key which references primary key 'id' within the same table, the query would be like,

CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
mgr_id number(5) REFERENCES employee(id),
salary number(10),
location char(10) 
);

3) SQL Not Null Constraint :

This constraint ensures all rows in the table contain a definite value for the column which is specified as not null. Which means a null value is not allowed.

Syntax to define a Not Null constraint:

[CONSTRAINT constraint name] NOT NULL

For Example: To create a employee table with Null value, the query would be like

CREATE TABLE employee
( id number(5),
name char(20) CONSTRAINT nm_nn NOT NULL,
dept char(10),
age number(2),
salary number(10),
location char(10) 
);

4) SQL Unique Key:

This constraint ensures that a column or a group of columns in each row have a distinct value. A column(s) can have a null value but the values cannot be duplicated.

Syntax to define a Unique key at column level:

[CONSTRAINT constraint_name] UNIQUE

Syntax to define a Unique key at table level:

[CONSTRAINT constraint_name] UNIQUE(column_name)

For Example: To create an employee table with Unique key, the query would be like,

Unique Key at column level:

CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10) UNIQUE 
);

CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10) CONSTRAINT loc_un UNIQUE 
);

Unique Key at table level:

CREATE TABLE employee
( id number(5) PRIMARY KEY,
name char(20),
dept char(10),
age number(2),
salary number(10),
location char(10),
CONSTRAINT loc_un UNIQUE(location) 
);

5) SQL Check Constraint :

This constraint defines a business rule on a column. All the rows must satisfy this rule. The constraint can be applied for a single column or a group of columns.

Syntax to define a Check constraint:

[CONSTRAINT constraint_name] CHECK (condition)

For Example: In the employee table to select the gender of a person, the query would be like

Check Constraint at column level:

CREATE TABLE employee 
( id number(5) PRIMARY KEY, 
name char(20), 
dept char(10), 
age number(2), 
gender char(1) CHECK (gender in ('M','F')), 
salary number(10), 
location char(10) 
);

Check Constraint at table level:

CREATE TABLE employee 
( id number(5) PRIMARY KEY, 
name char(20), 
dept char(10), 
age number(2), 
gender char(1), 
salary number(10), 
location char(10), 
CONSTRAINT gender_ck CHECK (gender in ('M','F')) 
);

Crack .Net Interview - 8 Year Experience

Friday, March 4, 2016

Database : SQL Integrity Constraints or Constraints

Column Level

Table Level

Types of SQL Constraints

Primary Key Constraints

Unique Key Constraints