ON DELETE CASCADE OPTION

As mentioned earlier, after a foreign key is defined, Oracle will NOT allow any parent row to be deleted if it has dependent rows in the child table.
For example, if CCODE in COURSE_FACULTY table is defined as foreign key referencing CCODE column of COURSES table then it is NOT possible to delete rows from COURSES table if dependent rows exists in COURSE_FACULTY table.

However, by using ON DELETE CASCADE it is possible to delete all child rows while parent row is being deleted.

The following code shows how to use ON DELETE CASCADE option.

CREATE TABLE course_faculty

(ccode varchar2(5)

CONSTRAINT course_faculty_ccode_fk REFERENCES courses(ccode)
ON DELETE CASCADE,
...
);

CHECK Constraint :

Defines the condition that should be satisfied before insertion or updation is done.
The condition used in CHECK constraint may NOT contain:

• A reference to pseudo column SYSDATE
• Subquery

If it is given as column constraint, it can refer only to current column. But if it is given as table constraint, it can refer to more than one column of the table. In neither case it can refer to a column of other tables.
The following example shows how to create CHECK constraint to make sure GRADE column of
COURSE_FACULTY contains letters A, B and C only.

CREATE TABLE course_faculty
( ...,
grade char(1) CONSTRAINT course_faculty_grade_chk
CHECK ( grade in (‘A’,’B’,’C’) ),
...
);
The above CHECK constraint does not allow any other characters other than A, B and C. It must be noted that character comparison is always case sensitive. So to ignore case differences you can convert GRADE to uppercase before comparison made as follows:
CREATE TABLE course_faculty
( ...,
grade char(1) CONSTRAINT course_faculty_grade_chk
CHECK ( upper(grade) in (‘A’,’B’,’C’) ),
...
);
The following is an example of CHECK constraint at table level. The constraint makes sure the starting date (STDATE) of a batch is less than or equal to ending date (ENDDATE) of the batch.
CREATE TABLE batches
( ...,
CONSTRAINT batches_dates_chk
CHECK ( stdate <= enddate),

);