Constraints
What are Constraints?
Constraints enforce rules at the table level.
Constraints prevent the deletion of a table if there are dependencies.
The following constraint types are valid:
Constraint Description
NOT NULL : Specifies that the column cannot contain a null value.
UNIQUE : Specifies a column or combination of columns whose values must be unique
UNIQUE : Specifies a column or combination of columns whose values must be unique
for all rows in the table.
PRIMARY KEY : Uniquely identifies each row of the table.
FOREIGN KEY : Establishes and enforces a foreign key relationship between the column and
PRIMARY KEY : Uniquely identifies each row of the table.
FOREIGN KEY : Establishes and enforces a foreign key relationship between the column and
1) NOT NULL Constraint :
The NOT NULL constraint ensures that the column contains no null values. Columns without the NOT
NULL constraint can contain null values by default.
The NOT NULL constraint can be specified only at the column level, not at the table level.
Example: CREATE TABLE XX_EMP_EMPID(
emp_id NUMBER(6),
first_name VARCHAR2(20),
job_id VARCHAR2(10)
CONSTRAINT emp_job_id_nn NOT NULL;
2) UNIQUE Constraint :
A UNIQUE key integrity constraint requires that every value in a column or set of columns (key) be unique—that is, no two rows of a table can have duplicate values in a specified column or set of columns.
The column (or set of columns) included in the definition of the UNIQUE key constraint is called the unique key.
UNIQUE constraints can be defined at the column or table le vel.
Example: CREATE TABLE employees(
employee_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
...
CONSTRAINT emp_email_uk UNIQUE(email));
3) Primary Key Constraint :
PRIMARY KEY constraints can be defined at the column level or table level.
Example: CREATE TABLE XX_DEPT_EMPID (department_id NUMBER(4),
department_name VARCHAR2(30)
CONSTRAINT dept_name_nn NOT NULL,
manager_id NUMBER(6),
location_id NUMBER(4),
CONSTRAINT dept_id_pk PRIMARY KEY(department_id));
A table can have only one PRIMARY KEY constraint but can have several UNIQUE constraints.
4) Foreign Key Constraint :
The FOREIGN KEY or referential integrity constraint, designates a column or combination of columns as a foreign key and establishes a relationship between a primary key or a unique key in the same table or a different table.
A foreign key value must match an existing value in the parent table or be NULL.
FOREIGN KEY constraints can be defined at the column or table constraint level.
Example: CREATE TABLE XX_EMP_EMPID(emp_id NUMBER(6),
last_name VARCHAR2(25) NOT NULL,
email VARCHAR2(25),
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE NOT NULL,
...
department_id NUMBER(4),
CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
REFERENCES departments(department_id),
CONSTRAINT emp_email_uk UNIQUE(email));
5 ) Delete Cascade:
CREATE TABLE table_name ( column1 datatype null/not null,
column2 datatype null/not null,
column2 datatype null/not null,
...
CONSTRAINT fk_column
FOREIGN KEY (column1, column2, ... column_n)
REFERENCES parent_table (column1, column2, ... column_n)
ON DELETE CASCADE );
On Delete Cascade clause sets all the records of the column which is defined as a foreign key in the child table to delete if the corresponding record in the parent table is deleted.
Ex: If Row in a table_name is deleted then it related all rows in another tables also deleted.
On Delete Null:
Similarly On Delete Null clause sets all the records of the column which is defined as a foreign key in the child table to Null if the corresponding record in the parent table is deleted.
The Check Constraint :
The CHECK constraint defines a condition that each row must satisfy.
A single column can have multiple CHECK constraints which refer to the column in its definition.
There is no limit to the number of CHECK constraints which you can define on a column.
CHECK constraints can be defined at the column level or table level.
Example:
CREATE TABLE employees (...
salary NUMBER(8,2)
CONSTRAINT emp_salary_min CHECK (salary > 0),...
Adding a Constraint Syntax :
Use the ALTER TABLE statement to:
- Add or drop a constraint, but not modify its structure
- Enable or disable constraints
- Add a NOT NULL constraint by using the MODIFY clause
Syntax:
ALTER TABLE table
ADD [CONSTRAINT constraint] type (column);
In the syntax:
table is the name of the table
constraint is the name of the constraint
type is the constraint type
column is the name of the column affected by the constraint
- Add or drop a constraint, but not modify its structure
- Enable or disable constraints
- Add a NOT NULL constraint by using the MODIFY clause
Syntax:
ALTER TABLE table
ADD [CONSTRAINT constraint] type (column);
In the syntax:
table is the name of the table
constraint is the name of the constraint
type is the constraint type
column is the name of the column affected by the constraint
Dropping a Constraint :
Syntax:
ALTER TABLE table
DROP PRIMARY KEY | UNIQUE (column) |
CONSTRAINT constraint [CASCADE];
In the syntax:
table is the name of the table
column is the name of the column affected by the constraint
constraint is the name of the constraint
ALTER TABLE table
DROP PRIMARY KEY | UNIQUE (column) |
CONSTRAINT constraint [CASCADE];
In the syntax:
table is the name of the table
column is the name of the column affected by the constraint
constraint is the name of the constraint
When you drop an integrity constraint, that constraint is no longer enforced by the Oracle server and is no longer available in the data dictionary..
Disabling a Constraint :
• Execute the DISABLE clause of the ALTER TABLE statement to deactivate an integrity constraint.
• Apply the CASCADE option to disable dependent integrity constraints.
Syntax:
ALTER TABLE table
DISABLE CONSTRAINT constraint [CASCADE];
In the syntax:
table is the name of the table
constraint is the name of the constraint
• The CASCADE clause disables dependent integrity constraints
• Disabling a unique or primary key constraint removes the unique index.
Enabling a Constraint :
Activate an integrity constraint currently disabled in the table definition by using the ENABLE clause.
A UNIQUE or PRIMARY KEY index is automatically created if you enable a UNIQUE key or PRIMARY KEY constraint.
Syntax:
ALTER TABLE table
ENABLE CONSTRAINT constraint;
In the syntax:
table is the name of the table
constraint is the name of the constraint
Enabling a primary key constraint that was disabled with the CASCADE option does not enable any foreign keys that are dependent upon the primary key.
No comments:
Post a Comment