Constraints

It Enforce Rule On Table

We can create constraint at the time of Creating Table

      1. Column Level Declaration 
2. Table Level Declaration

1. We can provide own name. 

          Format : [part_of_project_name-table_name-column_name-constraint_type]
           Example : gmind_emp_id_pk _uk _ck _fk

            2. system name name                                                

             Format  :  sys_cn 
Example : SYS_C405323

We can create constraint after the table has been created

Types

 

Primary Key

Not allowed Null value
Not allowed you to enter duplicate value
Ex :  Employee_id,  Student_id

Unique Key

allowed Null value
Not allowed you to enter duplicate value
Ex  :  Contact_no, email

  Foreign Key

 It will allowed you to enter NULL and Duplicate value.

c1(pk)  :  1  2   3    
c2(fk)  :  1  1  2  3       4  Not allowed


Allowed you to enter duplicate value and null value

Not Null

It will not allowed you to enter null value

   Check

 You can check your own condition

Simple Table for understanding


------------------------------------
             Duplicate    NULL
------------------------------------
Primary Key      X       X
Unique Key       X       Allowed 
Foreign Key   Allowed    Allowed
------------------------------------


creating table with all constraint type

 
 Create table my_stu_details
(
S_id       Number(2),
S_name     Varchar2(30) Not Null,   --SYS_Cn
s_mail     Varchar2(30),
s_gender   char(1),
s_did      Number(3),
Constraint my_stu_sid_pk   PRIMARY KEY (s_id),
constraint my_stu_mail_uk  UNIQUE  (s_mail)  ,
Constraint my_stu_gen_ck   CHECK (s_gender IN ('M', 'F', 'm', 'f')),
Constraint my_stu_did_fk   FOREIGN KEY (s_did) References Departments(Department_id)
);


DESC my_stu_details;

------------------------------------
Name       Type            Nullable
------------------------------------
S_ID     NUMBER(2)
S_NAME   VARCHAR2(30)
S_MAIL   VARCHAR2(30)      Y
S_GENDER  CHAR(1)           Y
S_DID    NUMBER(3)         Y
------------------------------------


Insert into my_stu_details (s_id, s_name  , s_mail            , s_gender, s_did) 
                    values (1   , 'name1' , 'name1@gmail.com' , 'm'     , 60   );



 Select * from my_stu_details;


 
Validating Primary Key

 
Insert into my_stu_details (s_id, s_name  , s_mail            , s_gender, s_did) 
                    values (1   , 'name2' , 'name2@gmail.com' , 'F'     , 90   );

ORA-00001: unique constraint (HR.MY_STU_SID_PK) violated

Insert into my_stu_details (s_id, s_name  , s_mail            , s_gender, s_did) 
                    values (NULL, 'name2' , 'name2@gmail.com' , 'F'     , 90   );
                    
ORA-01400: cannot insert NULL into (HR.MY_STU_SID_PK) violated

Insert into my_stu_details (s_id, s_name  , s_mail            , s_gender, s_did) 
                    values (2   , 'name2' , 'name2@gmail.com' , 'F'     , 90   );

Select * from my_stu_details;

 
Validating NOT NULL Constraint


 
Insert into my_stu_details (s_id, s_name  , s_mail            , s_gender, s_did) 
                    values (3   , NULL    , 'name3@gmail.com' , 'F'     , 80   );

ORA-01400: cannot insert NULL into ("HR"."MY_STU_DETAILS"."S_NAME")

Insert into my_stu_details (s_id, s_name  , s_mail            , s_gender, s_did) 
                    values (3   , 'Name3' , 'name3@gmail.com' , 'F'     , 80   );

Select * from my_stu_details;

 
Validating Unique Key Constraint


 
--Duplicate Check


 
Insert into my_stu_details (s_id, s_name  , s_mail            , s_gender, s_did) 
                    values (4   , 'Name4' , 'name3@gmail.com' , 'F'     , 80   );

ORA-00001: unique constraint (HR.MY_STU_MAIL_UK) violated

--Null Check

Insert into my_stu_details (s_id, s_name  , s_mail  , s_gender, s_did) 
                    values (4   , 'Name4' , null    , 'M'     , 70   );


Select * from my_stu_details;

 
Validating Check constraint


 
Insert into my_stu_details (s_id, s_name  , s_mail             , s_gender , s_did) 
                    values (5   , 'Name5' , 'name5@gmail.com'  , 'j'      , 70   );

ORA-02290: check constraint (HR.MY_STU_GEN_CK) violated

Insert into my_stu_details (s_id, s_name  , s_mail             , s_gender , s_did) 
                    values (5   , 'Name5' , 'name5@gmail.com'  , 'M'      , 70   );

Select * from my_stu_details;

 
Validating Foreign Key


 
Select * from Departments;

Insert into my_stu_details (s_id, s_name  , s_mail             , s_gender , s_did) 
                    values (6   , 'Name6' , 'name6@gmail.com'  , 'F'      , 3   );

 
ORA-02291: integrity constraint (HR.MY_STU_DID_FK) violated - parent key not found


 
Departments table  :  parent table
my_stu_details     :  Child table

Insert into my_stu_details (s_id, s_name  , s_mail             , s_gender , s_did) 
                    values (6   , 'Name6' , 'name6@gmail.com'  , 'F'      , 90   );
                    

Select * from my_stu_details;

--------------------------------------------------------
s_id     s_name   s_mail                s_gender  s_did
--------------------------------------------------------
 1 name1  name1@gmail.com   m     60
 2 name2  name2@gmail.com   F     90
 3 Name3  name3@gmail.com   F     80
 4 Name4                   M     70
 5 Name5  name5@gmail.com   M     70
 6 Name6  name6@gmail.com   F     90
--------------------------------------------------------



Deleting the Primary key value which is referred by Foreign Key


Select * from Departments;

DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------------------------------------------------------
60        IT        103        1400           <----  Delete


-- don't execute this query

--Delete From Departments
--Where  Department_id = 60;

1) ON DELETE SET NULL

-------------------------------------------------
S_ID S_NAME S_MAIL         S_GENDER  S_DID
-------------------------------------------------
6 Name6 name6@gmail.com F   90
1 name1 name1@gmail.com m   -
2 name2 name2@gmail.com F   90
3 Name3 name3@gmail.com F   80
4 Name4 -               M   70
5 Name5 name5@gmail.com M   70
-------------------------------------------------

2) ON DELETE SET CASCADE

-------------------------------------------------
S_ID S_NAME S_MAIL         S_GENDER  S_DID
-------------------------------------------------
6 Name6 name6@gmail.com F   90
2 name2 name2@gmail.com F   90
3 Name3 name3@gmail.com F   80
4 Name4 -               M   70
5 Name5 name5@gmail.com M   70
-------------------------------------------------

Syntax

Constraint my_stu_did_fk   FOREIGN KEY (s_did) References Departments(Department_id)
ON DELETE CASCADE

Constraint my_stu_did_fk   FOREIGN KEY (s_did) References Departments(Department_id)
ON DELETE SET NULL


Constraint Related Data Dictionary Table information.


To view constraint Information

SELECT c.owner,
       c.constraint_name,
       c.constraint_type,
       c.table_name,
       c.r_constraint_name,
       c.status
  FROM user_constraints c
 WHERE c.table_name = 'MY_STU_DETAILS';

 
------------------------------------------------------------------------------------
owner  constraint_name constraint_type table_name        r_constraint_name  status 
------------------------------------------------------------------------------------
 HR SYS_C004023       C       MY_STU_DETAILS                    ENABLED
 HR MY_STU_GEN_CK     C       MY_STU_DETAILS              ENABLED
 HR MY_STU_SID_PK     P       MY_STU_DETAILS              ENABLED
 HR MY_STU_MAIL_UK    U       MY_STU_DETAILS              ENABLED
 HR MY_STU_DID_FK     R       MY_STU_DETAILS     DEPT_ID_PK    ENABLED
------------------------------------------------------------------------------------



To view constraint name with corresponding column name

SELECT * FROM user_cons_columns c
WHERE c.table_name = 'MY_STU_DETAILS';

-------------------------------------------------------------
owner  constraint_name   table_name    column_name  position 
-------------------------------------------------------------
  HR    MY_STU_MAIL_UK MY_STU_DETAILS  S_MAIL 1
  HR    MY_STU_SID_PK MY_STU_DETAILS  S_ID           1
  HR    MY_STU_GEN_CK    MY_STU_DETAILS  S_GENDER
  HR SYS_C004023 MY_STU_DETAILS  S_NAME
  HR MY_STU_DID_FK    MY_STU_DETAILS  S_DID          1 
-------------------------------------------------------------


Creating Composite Primary Key


Creating Primary Key with more than one number of column

ALTER TABLE table_name
ADD CONSTRAINT constraint_name PRIMARY KEY(column1, column_2 , column3);

In user_cons_columns  table position if the column1 will be 1, position of column2 will be 2 and column3 will be 3.


creating constraint after the table creation


ALTER TABLE table_name
ADD CONSTRAINT constraint_name CONSTRAINT_TYPE(column_name); 

ALTER TABLE table_name
DROP CONSTRAINT constraint_name; 

ALTER TABLE table_name
DROP PRIMARY KEY; 


To drop related foreign key


ALTER TABLE table_name
DROP PRIMARY KEY CASCADE;

ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name;  

 
Disable constraint with related foreign key constraint

 
ALTER TABLE table_name
DISABLE CONSTRAINT constraint_name CASCADE;

ALTER TABLE table_name
ENABLE CONSTRAINT constraint_name;  

Interview Questions

• What are all the type of constraint available in oracle?
• Difference between PRIMARY KEY and UNIQUE key.
• Difference between PRIMARY KEY ans FOREIGN KEY.
• Difference between UNIQUE key and FOREIGN KEY.
• Is it possible to create tow primary key in single table.
• Write a syntax to create FOREIGN constraint.
• How to create constraint after the table has been created?
• How to enable and disable a constraint.
• Which data dictionary table contains information about constraints?
• Which Data Dictionary table contains information about constraint with corresponding column name?
• What is composite primary key?
• Explain ON DELETE CASCADE and ON DELETE SET NULL option.

~Thanks For Reading~
Post your valuable feedback below

listagg in Oracle 11g

Oracle 11gR2 listagg built-in function allows for many table columns to be displayed within a single row, a non-first-normal form display

Syntax :

     listagg(column_value,'delimiter') WITHIN GROUP(ORDER BY column_name)


--Example 1:

CREATE TABLE test_ins(empid NUMBER,product VARCHAR2(30));

BEGIN
   INSERT INTO test_ins VALUES( 101,'pendrive');
   INSERT INTO test_ins VALUES( 102,'toy');
   INSERT INTO test_ins VALUES( 101,'ipod');
   INSERT INTO test_ins VALUES( 102,'hat');
   INSERT INTO test_ins VALUES( 103,'cpu');
   INSERT INTO test_ins VALUES( 104,'pen');
   INSERT INTO test_ins VALUES( 104,'car');
   INSERT INTO test_ins VALUES( 104,'mat');
   INSERT INTO test_ins VALUES( 105,'tv');
   INSERT INTO test_ins VALUES( 106,'laptop');
   commit;
END;

SELECT * FROM test_ins;

    SELECT empid "Employee",
           listagg(product,
               ',') within
    GROUP (ORDER BY empid) "Products"
     FROM test_ins
 GROUP BY empid;

-->-- Result set --<--

 

DROP TABLE test_ins;

-->-- Example 2

select department_id "Department id", listagg(first_name,', ') WITHIN GROUP(ORDER BY employee_id) "Employees"
FROM employees
WHERE department_id IN (10,20,30,60)
GROUP BY department_id;

-->-- Result set --<--