/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu




Wednesday, July 8, 2015

SQL Integrity Constraints Foreign Key Not Null Unique Check

SQL Integrity Constraints

Integrity Constraints are used to apply business rules for the database tables.
Enforcing data integrity ensures the quality of the data in the database. 

For example, if an employee is entered with an employee_id value of 123, the database should not allow another employee to have an ID with the same value.

Data integrity falls into these categories:

Entity Integrity

Entity integrity defines a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier column(s) or the primary key of a table (through indexes, UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY properties).

Domain Integrity

Domain integrity is the validity of entries for a given column. You can enforce domain integrity by restricting the type (through data types), the format (through CHECK constraints and rules), or the range of possible values (through FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions, and rules).

Referential Integrity

Referential integrity preserves the defined relationships between tables when records are entered or deleted. In Microsoft® SQL Server™ 2000, referential integrity is based on relationships between foreign keys and primary keys or between foreign keys and unique keys (through FOREIGN KEY and CHECK constraints). Referential integrity ensures that key values are consistent across tables. Such consistency requires that there be no references to nonexistent values and that if a key value changes, all references to it change consistently throughout the database.

User-Defined Integrity

User-defined integrity allows you to define specific business rules that do not fall into one of the other integrity categories. All of the integrity categories support user-defined integrity (all column- and table-level constraints in CREATE TABLE, stored procedures, and triggers).

The Constraints available in SQL are Foreign Key, Not Null, Unique, and 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 INT PRIMARY KEY, 
name char(20),
dept char(10),
age INT,
salary INT,
location char(10)
);

or

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

Primary Key at column level:

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

Primary Key at table level:

CREATE TABLE employee
(id INT NOT NULL,
name char(20),
dept char(10),
age INT,
salary INT,
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.

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".

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

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

Foreign Key at table level:

CREATE TABLE order_items
( order_id INT,
product_id INT,
product_name char(20),
supplier_name char(20),
unit_price INT,
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 INT PRIMARY KEY,
name char(20),
dept char(10),
age INT,
mgr_id INT REFERENCES employee(id),
salary INT,
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 INT,
name char(20) CONSTRAINT nm_nn NOT NULL,
dept char(10),
age INT,
salary INT,
location char(10)
);

ALTER COLUMN DATA TYPE: -

ALTER TABLE employee ALTER COLUMN name char(20) NOT NULL

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 INT PRIMARY KEY,
name char(20),
dept char(10),
age INT,
salary INT,
location char(10) UNIQUE
);

Unique Key at table level:

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

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.

The CHECK constraint is used to limit the value range that can be placed in a column.
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 INT PRIMARY KEY,
name char(20),
dept char(10),
age INT,
gender char(1) CHECK (gender in ('M','F')),
salary INT,
location char(10)
);

Check Constraint at table level:

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

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

SQL CHECK Constraint on ALTER TABLE

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

To DROP a CHECK Constraint

ALTER TABLE Persons

DROP CONSTRAINT chk_Person

No comments:

Post a Comment

Recent Posts

My Blog List