Oracle- Integrity constraints
http://www.oracle-dba-online.com/sql/integrity_constraints.htm
Highlights of the links
Primary Key
Each table can have one primary key, which uniquely identifies each row in a table and ensures that no duplicate rows exist. Use the following guidelines when selecting a primary key:
Whenever practical, use a column containing a sequence number. It is a simple way to satisfy all the other guidelines.
Minimize your use of composite primary keys. Although composite primary keys are allowed, they do not satisfy all of the other recommendations. For example, composite primary key values are long and cannot be assigned by sequence numbers.
Choose a column whose data values are unique, because the purpose of a primary key is to uniquely identify each row of the table.
Choose a column whose data values are never changed. A primary key value is only used to identify a row in the table, and its data should never be used for any other purpose. Therefore, primary key values should rarely or never be changed.
Choose a column that does not contain any nulls. A PRIMARY KEY constraint, by definition, does not allow any row to contain a null in any column that is part of the primary key.
Choose a column that is short and numeric. Short primary keys are easy to type. You can use sequence numbers to easily generate numeric primary keys.
For example in EMP table EMPNO column is a good candidate for PRIMARY KEY.
To define a primary key on a table give the following command.
Alter table emp add constraint emppk primary key (empno);
Important points about Foreign key
Some points to remember for referential integrity
You cannot delete a parent record if any existing child record is there. If you have to first delete the child record before deleting the parent record. In the above example you cannot delete row of employee no. 101 since it’s child exist in the ATTENDANCE table. However, you can delete the row of employee no. 103 since no child record exist for this employee in ATTENDANCE table. If you define the FOREIGN KEY with ON DELETE CASCADE option then you can delete the parent record and if any child record exist it will be automatically deleted.
To define a foreign key constraint with ON DELETE CASCADE option give the following command.
Alter table attendance add constraint empno_fk
Foreign key (empno) references emp(empno)
On delete cascade;
In Oracle 9i oracle has also given a new feature i.e. ON DELETE SET NULL . That is it sets the value for foreign key to null whenever the parent record is deleted.
To define a foreign key constraint with ON DELETE SET NULL option give the following command.
Alter table attendance add constraint empno_fk
Foreign key (empno) references emp(empno)
On delete set null;
You also cannot drop the parent table without first dropping the FOREIGN KEY constraint from attendance table. However if you give CASCADE CONSTRAINTS option in DROP TABLE statement then Oracle will automatically drop the references and then drops the table.
UNIQUE KEY
Unique Key constraint is same as primary key i.e. it does not accept duplicate values, except the following differences
· There can be only on Primary key per table. Whereas, you can have as many Unique Keys per table as you want.
· Primary key does not accept NULL values whereas, unique key columns can be left blank.
You can also refer to Unique key from Foreign key of other tables.
On which columns you should put Unique Key Constraint ?
It depends on situations, first situation is suppose you have already defined a Primary key constraint on one column and now you have another column which also should not contain any duplicate values, Since a table can have only one primary key, you can define Unique Key constraint on these columns. Second situation is when a column should not contain any duplicate value but it should also be left blank. For example in the EMP table IDNO is a good candidate for Unique Key because all the IDNO’s are unique but some employees might not have ID Card so you want to leave this column blank.
To define a UNIQUE KEY constraint on an existing table give the following command.
Alter table emp add constraint id_unique unique (idno);
Again the above command will execute successfully if IDNO column contains complying values otherwise you have to remove non complying values and then add the constraint.
Defining Constraints in CREATE TABLE statement.
create table emp (empno number(5) constraint emppk
Primary key,
ename varchar2(20) constraint namenn
not null,
sal number(10,2) constraint salcheck
check (sal between 1000 and 20000)
idno varchar2(20) constraint id_unique
unique );
create table attendance (empno number(5) constraint empfk
references emp (empno)
on delete cascade,
month varchar2(10),
days number(2) constraint dayscheck
check (days <= 31) );
The name of the constraints are optional. If you don’t define the names then oracle generates the names randomly like ‘SYS_C1234’
Another way of defining constraint in CREATE TABLE statement.
create table emp (empno number(5),
ename varchar2(20) not null,
sal number(10,2),
idno varchar2(20),
constraint emppk Primary key (empno)
constraint salcheck
check (sal between 1000 and 20000)
constraint id_unique unique (idno) );
create table attendance (empno number(5),
month varchar2(10),
days number(2),
constraint empfk foreign key (empno)
references emp (empno)
on delete cascade
constraint dayscheck
check (days <= 31) );
Deferring Constraint Checks
You may wish to defer constraint checks on UNIQUE and FOREIGN keys if the data you are working with has any of the following characteristics:
Tables are snapshots
Tables that contain a large amount of data being manipulated by another application, which may or may not return the data in the same order
Update cascade operations on foreign keys
When dealing with bulk data being manipulated by outside applications, you can defer checking constraints for validity until the end of a transaction.
Ensure Constraints Are Created Deferrable
After you have identified and selected the appropriate tables, make sure their FOREIGN, UNIQUE and PRIMARY key constraints are created deferrable. You can do so by issuing a statement similar to the following:
create table attendance (empno number(5),
month varchar2(10),
days number(2),
constraint empfk foreign key (empno)
references emp (empno)
on delete cascade
DEFERRABLE
constraint dayscheck
check (days <= 31) );
Now give the following statement
Set constraint empfk deferred;
Update attendance set empno=104 where empno=102;
Insert into emp values (104,’Sami’,4000,’A123’);
Commit;
You can check for constraint violations before committing by issuing the SET CONSTRAINTS ALL IMMEDIATE statement just before issuing the COMMIT. If there are any problems with a constraint, this statement will fail and the constraint causing the error will be identified. If you commit while constraints are violated, the transaction will be rolled back and you will receive an error message.
Highlights of the links
Primary Key
Each table can have one primary key, which uniquely identifies each row in a table and ensures that no duplicate rows exist. Use the following guidelines when selecting a primary key:
Whenever practical, use a column containing a sequence number. It is a simple way to satisfy all the other guidelines.
Minimize your use of composite primary keys. Although composite primary keys are allowed, they do not satisfy all of the other recommendations. For example, composite primary key values are long and cannot be assigned by sequence numbers.
Choose a column whose data values are unique, because the purpose of a primary key is to uniquely identify each row of the table.
Choose a column whose data values are never changed. A primary key value is only used to identify a row in the table, and its data should never be used for any other purpose. Therefore, primary key values should rarely or never be changed.
Choose a column that does not contain any nulls. A PRIMARY KEY constraint, by definition, does not allow any row to contain a null in any column that is part of the primary key.
Choose a column that is short and numeric. Short primary keys are easy to type. You can use sequence numbers to easily generate numeric primary keys.
For example in EMP table EMPNO column is a good candidate for PRIMARY KEY.
To define a primary key on a table give the following command.
Alter table emp add constraint emppk primary key (empno);
Important points about Foreign key
Some points to remember for referential integrity
You cannot delete a parent record if any existing child record is there. If you have to first delete the child record before deleting the parent record. In the above example you cannot delete row of employee no. 101 since it’s child exist in the ATTENDANCE table. However, you can delete the row of employee no. 103 since no child record exist for this employee in ATTENDANCE table. If you define the FOREIGN KEY with ON DELETE CASCADE option then you can delete the parent record and if any child record exist it will be automatically deleted.
To define a foreign key constraint with ON DELETE CASCADE option give the following command.
Alter table attendance add constraint empno_fk
Foreign key (empno) references emp(empno)
On delete cascade;
In Oracle 9i oracle has also given a new feature i.e. ON DELETE SET NULL . That is it sets the value for foreign key to null whenever the parent record is deleted.
To define a foreign key constraint with ON DELETE SET NULL option give the following command.
Alter table attendance add constraint empno_fk
Foreign key (empno) references emp(empno)
On delete set null;
You also cannot drop the parent table without first dropping the FOREIGN KEY constraint from attendance table. However if you give CASCADE CONSTRAINTS option in DROP TABLE statement then Oracle will automatically drop the references and then drops the table.
UNIQUE KEY
Unique Key constraint is same as primary key i.e. it does not accept duplicate values, except the following differences
· There can be only on Primary key per table. Whereas, you can have as many Unique Keys per table as you want.
· Primary key does not accept NULL values whereas, unique key columns can be left blank.
You can also refer to Unique key from Foreign key of other tables.
On which columns you should put Unique Key Constraint ?
It depends on situations, first situation is suppose you have already defined a Primary key constraint on one column and now you have another column which also should not contain any duplicate values, Since a table can have only one primary key, you can define Unique Key constraint on these columns. Second situation is when a column should not contain any duplicate value but it should also be left blank. For example in the EMP table IDNO is a good candidate for Unique Key because all the IDNO’s are unique but some employees might not have ID Card so you want to leave this column blank.
To define a UNIQUE KEY constraint on an existing table give the following command.
Alter table emp add constraint id_unique unique (idno);
Again the above command will execute successfully if IDNO column contains complying values otherwise you have to remove non complying values and then add the constraint.
Defining Constraints in CREATE TABLE statement.
create table emp (empno number(5) constraint emppk
Primary key,
ename varchar2(20) constraint namenn
not null,
sal number(10,2) constraint salcheck
check (sal between 1000 and 20000)
idno varchar2(20) constraint id_unique
unique );
create table attendance (empno number(5) constraint empfk
references emp (empno)
on delete cascade,
month varchar2(10),
days number(2) constraint dayscheck
check (days <= 31) );
The name of the constraints are optional. If you don’t define the names then oracle generates the names randomly like ‘SYS_C1234’
Another way of defining constraint in CREATE TABLE statement.
create table emp (empno number(5),
ename varchar2(20) not null,
sal number(10,2),
idno varchar2(20),
constraint emppk Primary key (empno)
constraint salcheck
check (sal between 1000 and 20000)
constraint id_unique unique (idno) );
create table attendance (empno number(5),
month varchar2(10),
days number(2),
constraint empfk foreign key (empno)
references emp (empno)
on delete cascade
constraint dayscheck
check (days <= 31) );
Deferring Constraint Checks
You may wish to defer constraint checks on UNIQUE and FOREIGN keys if the data you are working with has any of the following characteristics:
Tables are snapshots
Tables that contain a large amount of data being manipulated by another application, which may or may not return the data in the same order
Update cascade operations on foreign keys
When dealing with bulk data being manipulated by outside applications, you can defer checking constraints for validity until the end of a transaction.
Ensure Constraints Are Created Deferrable
After you have identified and selected the appropriate tables, make sure their FOREIGN, UNIQUE and PRIMARY key constraints are created deferrable. You can do so by issuing a statement similar to the following:
create table attendance (empno number(5),
month varchar2(10),
days number(2),
constraint empfk foreign key (empno)
references emp (empno)
on delete cascade
DEFERRABLE
constraint dayscheck
check (days <= 31) );
Now give the following statement
Set constraint empfk deferred;
Update attendance set empno=104 where empno=102;
Insert into emp values (104,’Sami’,4000,’A123’);
Commit;
You can check for constraint violations before committing by issuing the SET CONSTRAINTS ALL IMMEDIATE statement just before issuing the COMMIT. If there are any problems with a constraint, this statement will fail and the constraint causing the error will be identified. If you commit while constraints are violated, the transaction will be rolled back and you will receive an error message.
Comments