Lesson 6 | FOREIGN KEY, CHECK, and UNIQUE constraints |
Objective | Identify the syntax components of the FOREIGN KEY, CHECK, and UNIQUE constraints |
FOREIGN KEY CONSTRAINT:
This constraint defines a foreign key as a reference to the primary key of another table.CHECK CONSTRAINT:
This constraint allows you to attach validation to a column.FOREIGN KEY
constraint.
column list 1 | This list is columns in the current table that make up the foreign key columns. |
column list 2 | This list of columns is the primary key columns in the referenced table. You can leave this off if you wish. If left off, Oracle simply assumes that the entire primary key in its original order will be used. |
ON DELETE{CASCADE | If you include the ON DELETE CASCADE parameter, this means that when the parent row in the referenced table is deleted, the child rows in this table are deleted. |
SET NULL | If you include the ON DELETE SET NULL parameter, when a parent row is deleted, the child rows in this table have their foreign key data set to null values. |
] | Eliminating the ON DELETE parameter means that the parent row cannot be deleted if there are any child rows in this table. |
FOREIGN KEY
constraint within a
column specification
(rather than at the end of the column list). However, the syntax is somewhat different.
FOREIGN KEY
constraint. It has several restrictions:
REFERENCES [user.]table [(column_name)] [ ON DELETE { CASCADE | SET NULL} ]
CREATE TABLE PET_CARE_LOG (PRODUCT_ID NUMBER(10) REFERENCES PRODUCT ON DELETE SET NULL, LOG_DATETIME DATE, LOG_TEXT VARCHAR2(500))
CHECK
constraint.
CONSTRAINT |
If you leave out CONSTRAINT and constraint name, the system names the constraint for you. | |
CHECK | pop-up caption | |
Condition | The condition parameter can be any valid condition that evaluates to TRUE or FALSE. Think of the WHERE clause in a query. The same type of condition that works in a WHERE clause will work in a CHECK constraint. | |
TYPE_OF_LOG | Add the CHECK constraint to a column specification if the constraint deals only with that column. |
CHECK
constraint: