Page 83 - DCAP402_DCAO204_DATABASE MANAGEMENT SYSTEM_MANAGING DATABASE

Database Management Systems/Managing Database

Notes The Foreign Key References constraint are as follows:
1. Rejects an INSERT or UPDATE of a value, if a corresponding value does not currently exist
in the primary key table

2. Rejects a DEL_TE, if it would invalidate a REFERENCES constrain
3. Must reference a PRIMARY KEY or UNIQUE column(s) in primary key table
4. Will reference the PRIMARY KEY of the primary key table if no column or group of
columns is specified in the constraint
5. Must reference a table, not a view or cluster;
6. Requires that you own the primary key table, have REFERENCE privilege on it, or have
column-level REFERENCE privilege on the referenced colwnns in the primary key table;
7. Doesn’t restrict how other constraints may reference the same tables;
8. Requires that the FOREIGN KEY column(s) and the CONSTRAINT column(s) have matching
data types;
9. May reference the same table named in the CREATE TABLE statement;
10. Must not reference the same column more than once (in a single constraint).

Example: Create table sales_order _details with primary key as s_order_no and
product_no and foreign key as s_order_no referencing column s_order_no in the sales order
table.
FOREIGN KEY as a Column Constraint:
CREATE TABLE sales order details
( s_order_no varchar2(6) REFERENCES sales_order,
product_no varchar2(6),
qty _ordered number(8), qty - disp number(8), product_rate number(8,2),
PRIMARY KEY (s_order_no, product_no));

FOREIGN KEY as a Table Constraint:
CREATE TABLE sales order details
( s _order_no varchar2( 6),
product_no varchar2(6),
qty_ordered number(8), qty_disp number(8),
product_rate number(8,2),
PRIMARY KEY (s_order_no, product_no),
FOREIGN KEY (s_order_no) REFERENCES sales_order);

CHECK Integrity Constraints

Use the CHECK constraint when you need to enforce integrity rules that can be evaluated based
on a logical expression. Never use CHECK constraints if the constraint can be defined using the
not null, primary key or foreign key constraint.
Following are a few examples of appropriate CHECK constraints:
1. a CHECK constraint on the client no column of the client master so that no client no value
starts with ‘C’.

76 LOVELY PROFESSIONAL UNIVERSITY