Integrity Constraints (ICs) are enforcement to business-validations that ensures consistency and accuracy of relational databases
1. Introduction
- Constraints are used to enforce business validations.
- PRIMARY KEY, FOREIGN KEY, UNIQUE, DEFAULT & CHECK are the examples of constraints.
- Constraints can be defined along with Table creation using CREATE TABLE or can be added later with ALTER TABLE.
- All constraints are removable.
- Constraints can be defined over a column or entire table.
- You can define ICs in two-ways: Column-Level or Table-level
Integrity constraints cannot be defined for views
2. Types of Integrity Constraints (ICs)
Concern to RDBMs, you can maintain Integrity of database by 4-Integrity Constraints:- Entity Integrity: "Every row within the table must be uniquely identifiable" . This is achieved by adding Primary key to column. Primary-key have 3-important behaviors:
- Primary Key value cannot be NULL:
- Primary key value should be UNIQUE:
- Primary key implicitly creates PRIMARY-Index on Table.
UNIQUE-key cannot be used to identify a row within a table, because UNIQUE-key can contain null-value and a table can have multiple UNIQUE-Keys. - Domain Integrity: It enforces what type of data a column can contain. This is achieved by defining column with required Data Types, length and ranges, and Integrity Constraints: NOT-NULL DEFAULT, so that you can compare column values more appropriately.
- Referential Integrity: It enforces consistency between two tables by defining a containment relationship between them so that a column(s) of table can contain values of only tables’ column(s). It is achieved by adding foreign-key to child.
- User-Defined Integrity: A custom business validations or constraints defines. It validates structure (pattern), size, range etc. It is achieved by adding CHECK-Constraint to table.
- PRIMARY-Key Constraint: A column or set-of-columns, that unique identify each row in the table. It cannot be accept Null, or duplicate values. A Table can have at-most one primary key. Creating of Primary Key automatically defines Clustered INDEX on that column(s).
- UNIQUE-Key Constraint: A Column or combination-of-columns, that contains non-repeated values. It can contain Only-single NULL values. Creating of Unique-key automatically defines Non-Clustered Index on that column(s)
- NOT-NULL Constraint: Enforces that a column cannot contain Null-values, you can use this constraint with combination with UNIQUE, DEFAULT, CHECK, FOREIGN-Key.
- DEFAULT Constraint: Specifies default value for a column when it contain empty-value. You cannot default multiple columns using single DEFULT constraint. Implicit value is NULL for default. However, using DEFAULT constraint you can customize default value to business value;
NULL itself a value, it is not empty-field.
- FOREIGN-Key Constraint: “ Foreign-Key is a column(s) of a table that act as primary key in another table” . It enforces a containment relationship between two tables as one of them is called PARENT-Table and another one called CHILD-Table. The primary-key values of CHILD-Table must exist in Parent-Tables referenced column(s).
- CHECK Constraint: It ensures that all value sin a column satisfy certain predicate, pattern or both. Note: Unlike other Constraints, visibility of CHECK-Constraint is row not a column(s), i.e., using single CHECK-constraint you can validate multiple columns.
3. Creating & Deleting Integrity Constraints:
Syntax: Creating Integrity Constraints
[ CONSTRAINT constraint_name ]
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ] (column [ ASC | DESC ] [ ,...n ] )
| FOREIGN KEY( column [ ,...n ] )
REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ NOT FOR REPLICATION ]
| DEFAULT constant_expression FOR column [ WITH VALUES ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
}
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ] (column [ ASC | DESC ] [ ,...n ] )
| FOREIGN KEY( column [ ,...n ] )
REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
[ NOT FOR REPLICATION ]
| DEFAULT constant_expression FOR column [ WITH VALUES ]
| CHECK [ NOT FOR REPLICATION ] ( logical_expression )
}
Examples
- CREATE TABLE Table1 ( Id int Primary Key, Name varchar2(15));
- CREATE TABLE Table1 ( Id int CONSTRAINT Table1_Pk PRIMARY KEY,..);
- CREATE TABLE Table1 ( Id int, ......,
CONSTRAINT Table1_Pk PRIMARY KEY(Id) ); - CREATE TABLE Table1 ( Id int, ......,
CONSTRAINT Table1_Pk PRIMARY KEY(Id) ); - CREATE TABLE Table2 ( …… , Name varchar2(15) UNIQUE );
- CREATE TABLE Table2 ( …… , Name varchar2(15),
CONSTRAINT Table1_NameUnique UNIQUE); - CREATE TABLE Table2 ( ......., Name varchar2(15),
CONSTRAINT Table1_NameUnique UNIQUE(Name) ); - CREATE TABLE Table2 ( ......., Name varchar2(15),
CONSTRAINT Table1_NameUnique UNIQUE(Id,Name) ); - CREATE TABLE Table3 ( …., name char(20)
CONSTRAINT C1 NOT NULL,…..); - CREATE TABLE Table3 ( …. , name char(20),
CONSTRAINT C1 NOT NULL(name) ); - CREATE TABLE Table4 ( …. , name char(20),
gender char(1) CHECK (gender in ('M','F'))); - CREATE TABLE Table4 (….., name char(20), gender char(1),
CONSTRAINT gender_ck CHECK (gender in ('M','F')) ); - ALTER TABLE Table5 ADD CONSTRAINT C1 UNIQUE (col1,col2)
- ALTER TABLE Table5 ADD CONSTRAINT C2 PRIMARY KEY (col1,col2)
- ALTER TABLE Table5 ADD CONSTRAINT C3 REFERENCES Table1(Id)
- ALTER TABLE Table5 ADD CONSTRAINT C4 CHECK(Id>=10 and Id<=100)
Syntax: Dropping Constraints
ALTER TABLE DROP CONSTRAINT
Examples:
- Deleting Primary Key : ALTER TABLE EMPLOYEES DROP PRIMARY KEY;
- Deleting Default Key : ALTER TABLE CUSTOMERS
ALTER COLUMN SALARY DROP DEFAULT; - Deleting Foreign Key : ALTER TABLE ORDERS DROP FOREIGN KEY;
- Deleting any IC : ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;
To see information about constraints EXEC sp_helpconstraint TableName
4. Different ways of defining Integrity Constraints
You define Integrity Constraints in two ways:-
Column Level
A column constraint is defined followed by column definition and applied to only that column only. You cannot apply column constraint to multiple columns. For this use Table constraints, which is defined independently and applicable on multiple columns.
CREATE TABLE Table1 (
Id int CONSTRAINT Table1_Pk PRIMARY KEY,....);
Typically, Column-constraints is used while defining table using CREATE TABLE statement. Table Level
The constraints can be specified after all the columns are defined. It can refer any column of table. For defining composite Primary Key, or Composite Unique-Key, only Table-level Constrains is used.
CREATE TABLE Table1 ( Id int, ......,
CONSTRAINT Table1_Pk PRIMARY KEY(Id) );
Typically, it is used while altering table ALTER TABLE statement.
5. Conclusion
- Integrity Constraints (ICs) guaranteed about accuracy and integrity of database if and only if you well-defined all constraints. Failure to defining needed ICs causes data inconsistency.
- Always prefer Integrity Constraints as oppose to Db-Triggers for enforcing business validations, since they are light-weight hence maximizes performance.
- For custom business validations possibly use CHECK constraints, as this constraint can access whole record instead of particular column.
- For a table, list out all data validations required for business and then apply.
No comments:
Post a Comment