Primary Key or Unique Key constraints

Consider a table known as Customer that contains the details of the customers who have ordered for products and an Order table which maintains the information about the orders placed.

The structures of the tables look like:

In the table, the Cust_master is the master table and order_master is the child table.

CREATE TABLE cust_master (ccode NUMBER (5) CONSTRAINT pk_ccode PRIMARY KEY, Name VARCHAR2 (40), Address    VARCHAR2 (100));
CREATE  TABLE  order_master  (ocode  NUMBER(4),  ccode  NUMBER(5)  CONSTRAINT fk_ccode REFERENCES cust_master(ccode), odate DATE, oval NUMBER);

In the above instance, note that the master table must be created first and only then the child table is created. To form the relationship, add REFERENCES clause that refers to the master table. In case the child table value is refers to a non-existing record in the parent table then an error is raised.

Example
INSERT INTO cust_master VALUES (1,’Arun’,’Chennai’); INSERT INTO cust_master VALUES (2,’Ramesh’,’Bangalore’); Inserting records into child table.

INSERT INTO order_master VALUES(1,1,’01-JAN-2000’,3000);
All record is inserted in to the child table since the value 1 of customer code column refers to the parent record in Cust_master table.
INSERT INTO order_master VALUES (2,4,’01-Jul-2000’, 1000);
INSERT INTO order_master VALUES (2,4,'01-Jul-2000', 1000)
*
ERROR at line 1:

ORA-02291: integrity constraint (HEMA.FK_CCODE) violated - parent key not found
This increment to an error because there is no reference record in the master table for Customer code 4.

The above-discussed kinds of constraints are known as Column level constraints because they are defined along with the table definition. Another kind of constraint is known as the Table Level Constraint that is shown below:

CREATE TABLE employee_master (Empcode NUMBER, empname VARCHAR2 (20),
Salary NUMBER, sex NUMBER,
CONSTRAINT pk_empno PRIMARY KEY (empcode), CONSTRAINT chk_sal CHECK (salary BETWEEN 1000 and 7000), CONSTRAINT chk_sex CHECK (sex IN (‘M’,’F’,’m’,’f’)));

For adding a referential integrity constraint the table-level constraint is shown as follows:

CREATE TABLE order_master (ocode NUMBER (4), ccode NUMBER (5),odate DATE, oval NUMBER,
CONSTRAINT fk_ccode FOREIGN KEY (ccode) REFERENCES cust_master(ccode));

Although creating Table-level constraints for referential Integrity constraint, FOREIGN KEY along with REFERENCES must be used.

Note: NOT NULL constraints cannot be provided for Table-level constraints