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