Oracle supports the following SQL constraints:
• Primary Key constraint
• Unique Key constraint
• Foreign Key constraint
• Check constraint
• Not Null constraint
Primary Key Constraint:
• A single or a combination of one or more columns that is used to identify the distinct rows of a table
• The columns which are defined as primary key cannot have null or empty values
• There can be only one primary key for a table
Unique constraint:
• Single or a combination of one or more columns that is used to identify the distinct rows of a table
• Allows null values for the columns which are defined as unique keys
• There can be more than one unique key for a table
• The column values, when data is present, must be unique. This is
enforced with the UNIQUE constraint.
Foreign Key Constraint:
• A foreign key is a reference to the primary key of some other table
• Data manipulations on the child table which uses a foreign key
gets violated when there is no matching records in the referenced parent table.
• For eg. Let us have two tables EMPLOYEE and DEPARTMENT
Structure of DEPARTMENT table
-- PK_DEPT_ID (primary key)
-- DEPT_NAME
Structure of EMPLOYEE table
-- PK_EMP_ID
-- EMP_NAME
-- FK_DEPT_ID (references PK_DEPT_ID of DEPARTMENT table ie Foreign key)
-- BIRTH_DATE
-- AGE
• On insert or update, and when no data found in the parent table, an error such as
ORA-02291: integrity constraint (DEMO.FK_DEPT_ID) violated - parent key not found
• On deletion of rows from the parent table (DEPARTMENT in our case) and when
matching child rows are found in EMPLOYEE table then the error message would be
ORA-02291: integrity constraint (DEMO.FK_DEPT_ID) violated - parent key not found
ORA-02292: integrity constraint (DEMO.FK_DEPT_ID) violated – child record found
>>Check Constraint:
• On nutshell, it is a validation or a rule on the data of the table
• We can constraint the table values to have specific values by defining the check constraint
• Consider the following example. In the EMPLOYEE table, if the BIRTH_DATE column does not have value
then you should allow AGE column to have null value; and the vice versa. if AGE column contains data,
then BIRTH_DATE column should not be left blank. In this scenario, you can create a CHECK CONSTRAINT
as shown below:
ALTER TABLE EMPLOYEE ADD CONSTRAINT CK_DOB_AGE CHECK
((BIRTH_DATE IS NULL AND AGE IS NULL) OR
(BIRTH_DATE IS NOT NULL AND AGE IS NOT NULL));
Now see the results for the queries given below:
UPDATE EMPLOYEE SET (BIRTH_DATE, AGE) = ('05-FEB-1975', 33) WHERE PK_EMP_ID=1; -- success
UPDATE EMPLOYEE SET (BIRTH_DATE, AGE) = (NULL, NULL) WHERE PK_EMP_ID =1; -- success
UPDATE EMPLOYEE SET (BIRTH_DATE, AGE) = ('05-FEB-1975', NULL) WHERE PK_EMP_ID =1; -- failure
UPDATE EMPLOYEE SET (BIRTH_DATE, AGE) = (NULL, 33) WHERE PK_EMP_ID =1; -- failure
--failure would be constraint CK_DOB_AGE check constraint violated...cannot update AGE to null
(something like this)
>>Not Null Constraint:
• a special case of CHECK constraint.
• can be used to have a column not to contain empty or null values
> Index:
• An index is an object that is created in a tablespace.
• It is a physical structure that consumes disk space.
• When you create a Primary Key or Unique constraint, an index is either automatically created or an existing index may be reused.
• An index is based on a tree structure.
• Indexes are used by Oracle to execute SELECT statements.
• The execution of a SELECT using an index is generally faster than a SELECT that does not use an index.
|
| Author: mahendrakiran 13 Nov 2008 | Member Level: Gold Points : 2 |
Oracle supports the following SQL constraints:
1) Primary Key constraint
2)Unique Key constraint
3) Foreign Key constraint
4) Check constraint
5) Not Null constraint
Primary Key Constraint:
A single or a combination of one or more columns that is used to identify the distinct rows of a table
The columns which are defined as primary key cannot have null or empty values
There can be only one primary key for a table
Unique constraint:
Single or a combination of one or more columns that is used to identify the distinct rows of a table
Allows null values for the columns which are defined as unique keys
There can be more than one unique key for a table
The column values, when data is present, must be unique. This is enforced with the UNIQUE constraint.
Foreign Key Constraint:
A foreign key is a reference to the primary key of some other table
Data manipulations on the child table which uses a foreign key gets violated when there is no matching records in the referenced parent table.
|