CONSTRAINTS

CONSTRAINTS

1. NAMING CONVENTION

2. CONSTRAINT CLAUSE OR KEYWORD

   • CREATE & ALTER - (TABLE and VIEW)

3. NOT NULL CONSTRAINT

   • SHOULD BE DEFINED ONLY AT COLUMN LEVEL
   • ERROR
     • ORA -1400 CANNOT INSERT NULL INTO THE COLUMN
   • GENERALLY WE DECALRE NOT NULL CONSTRAINT WHEN THE COLUMN IS NOT NULL

4. UNIQUE CONSTRAINT

   • COMPOSITE UNIQUE CONSTRAINT
     • TWO COLUMNS IN COMBINATION ACT AS UNIQUENESS.HERE UNIQUE IS WORKING ON TWO COLUMNS IN COMBINATION AT A TIME
       • Here one column is maintaining the uniqueness of another column
         • DISTINCT also works on same concept
     • COMPOSITE UNIQUE CONSTRAINT WILL BE DECLARED ONLY AT TABLE LEVEL
     • ONE COMPOSITE UNIQUE CONSTRAINT CAN MAXIMUM HAVE 32 COLUMNS
     • THE MOMENT YOU DECLARED THE COMPOSITE CONSTRAINT YOU ARE WORKING ON 1 - MANY RELATIONSHIP
       • HERE WHEN YOU TAKE COMPOSITE-UNQ.BECAUSE OF COMPOSITE YOUR TABLE PICKS UP MANY TO MANY RELATIONSHIP. TO MAKE IT 1- MANY YOU NEED TO SOME MORE ADJUSTMENTS
     • (1,SAMPLE01,SYSDATE) (1,SAMPLE01,SYSDATE) SINCE SAME COMBINATION GIVES ERROR
     • (NULL,SAMPLE02,SYSDATE) (NULL,SAMPLE02,SYSDATE) SINCE SAME COMBINATION GIVES ERROR
     • (3,NULL,SYSDATE) (3,NULL,SYSDATE) SINCE SAME COMBINATION GIVES ERROR
     • (NULL,NULL,SYSDATE) (NULL, NULL, SYSDATE) IF BOTH ARE NULLS ACCEPTS THE RECORDS
   • NULLS ARE ALLOWED
   • USED IF YOU WANT DATA TO BE UNIQUE BY NATURE
   • INDEX CREATION
     • IMPORTANT POINT: WHEN UNIQUE CONSTRAINT IS CREATED AUTOMATICALLY WITHOUT YOUR KNOWLEDGE AN INDEX IS CREATED
     • INDEX IS CREATED BECAUSE TO SPEED UP THE PROCESSING OF QUERIES
   • RDBMS
     • THEORY OF RDBMS - UNIQUE COLUMN IS SUITABLE FOR KEY STATUS
       • ANY COLUMN THAT IS SUITABLE FOR INDEXING IS A KEY COLUMN
     • A TABLE OR VIEW CAN HAVE ONLY ONE UNIQUE KEY AS A CONSTRAINT
       • BUT ONE TABLE CAN HAVE MANY UNIQUE CONSTRAINTS
       • UNIQUE KEY CONSTRAINT & UNIQUE CONSTRAINT ARE NOT SAME
   • IMPORTANT POINT: IF THERE ARE TWO UNIQUE CONSTRAINTS DECLARED IN A TABLE THEN THE ANALYST MAY FIX ONLY ONE OF THEM AS THE KEY , THEN REMAINING WILL BECOME ALTERNATE KEYS
   • A TABLE CAN HAVE MULTIPLE NUMBER OF UNIQUE CONSTRAINTS. BUT A TABLE CAN HAVE ONLY ONE KEY COLUMN
   • ERROR
     • 0RA- 00001 UNIQUE CONSTRAINT VIOLATED

5. PRIMARY_KEY CONSTRAINT

6. FOREIGN_KEY CONSTRAINT

7. NULL CONSTRAINT

   • DEFAULT CONSTRAINT IF NO CONSTRAINT IS SPECIFIED
   • BECAUSE OF THIS ONLY ALWAYS NULLS ARE ALLOWED DEFAULT IN THE TABLE