Some Constraints Example

CONSTRAINTS

PRIMARY CONSTRAINTS        : PRIMARY, UNIQUE, CHECK, REFERENCES)
SECONDARY CONSTRAINTS      : NOT NULL, DEFAULT)

CONSTRAINTS BASED ON 2 LEVELS COLUMN LEVEL and TABLE LEVEL
Except Not null all others can be defined as both table and column level.
But not null as only column level.

Eg. For SECONDARY CONSTRAINTS
~~~~~~~~~~~~~~~~~~~~~~~

1. NOT NULL CONSTRAINT

  CREATE TABLE EMP9( ENO NUMBER(3)  NOT NULL,
           ENAME VARCHAR2(10));


   2. DEFAULT CONSTRAINT

CREATE TABLE EMP9(ENO NUMBER(3)  NOT NULL,
        ENAME VARCHAR2(10),DOJ DATE DEFAULT SYSDATE);

   3. PRIMARY CONSTRAINT(COLUMN LEVEL)

UNIQUE

CREATE TABLE EMP9(
ENO NUMBER(3)  NOT NULL CONSTRAINT UNIEMP UNIQUE,
ENAME VARCHAR2(10));

PRIMARY KEY

CREATE TABLE EMP9(
ENO NUMBER(3) CONSTRAINT PKEMP9 PRIMARY KEY,
ENAME VARCHAR2(10));

   4. CHECK CONSTRAINT

CREATE TABLE BANK(
ACNO NUMBER(2) CONSTRAINT PKBANK PRIMARY KEY,
ACTYPE VARCHAR2(2) CONSTRAINT CKBANK CHECK ( ACTYPE IN
        ('SB','CA','RD')),
ACNAME VARCHAR2(10),
AMOUNT NUMBER(4));


   5. REFERENCES

CREATE TABLE EMP9(
ENO NUMBER(3) CONSTRAINT PKE9 PRIMARY       KEY,
JOB VARCHAR2(10),
ENAME VARCHAR2(10),
MGR NUMBER(4) REFERENCES EMP9(ENO));

   6. REFERENCES(REFERING TO DIFFERENT TABLE)

CREATE TABLE DEPT9(DEPTNO NUMBER(2) CONSTRAINT PKDNO PRIMARY  KEY, DNAME VARCHAR2(10), LOC VARCHAR2(10));


CREATE TABLE EMP9( EMPNO NUMBER(4),ENAME VARCHAR2(10),
       SAL NUMBER(7,2),DEPTNO NUMBER(2) CONSTRAINT FKDNO REFERENCES DEPT9(DEPTNO));




7. TABLE LEVEL CONSTRAINTS


     UNIQUE  TABLE LEVEL

CREATE TABLE BANK( ACNO NUMBER(3),ACTYPE VARCHAR2(10),
        BAL NUMBER(7,2),PLACE VARCHAR2(10),CONSTRAINT UNIBANK          UNIQUE(ACNO,ACTYPE));

     PRIMARY KEY(TABLE LEVEL)

CREATE TABLE BANK( ACNO NUMBER(2), ACTYPE VARCHAR2(2) CONSTRAINT   CKBANK CHECK (ACTYPE IN ('SB','CA','RD')),AMOUNT NUMBER)