Column Constraints: NOT NULL, UNIQUE, CHECK, PRIMARY KEY, DEFAULT, REFERENCES,
On delete Cascade: Using this key whenever a parent row is removed in a referenced table then all the corresponding child rows are removed from the referencing table. This constraint is a form of referential integrity constraint.
Example 1:
CREATE TABLE product
(
pno number (4) PRIMARY KEY, pname char (20) NOT NULL,
qoh number (5) DEFAULT (100),
class char (1) NOT NULL,
rate number (8,2) NOT NULL,
CHECK ((class='A' AND rate<1000) OR (class='B' AND rate>1000 AND rate<4500) OR (class='C' AND rate>4500))
);
The command above makes a table. Primary key constraint makes sure that product number (pno) is not null and unique (both are the properties of primary key). Please note down the use of data type char (20). In many executions of SQL on commercial DBMS such as Oracle and SQL server, a data type known as varchar and varchar2 is used respectively. Varchar mainly is variable length character type subject to a maximum specified in the declarations. We will use them at most of the places soon.
Please note the use of check constraints in the table formed above. It correlates two dissimilar attribute values.