CONSTRAINTS
==>It Enforce the rule on table.
==>We can create Constrains while creating the table and After the table has been created.
1.For Column level(Cont Name ...SYS_C000001)
2.For Table Level(We assign the OWN names)
Type Of Constraints::
There are five types of constraints are there.
1.Primary Key
2.Unique Key
3.Foreign Key
4.CHECK Constraint
5.NOT NULL
------------------------------------------------------------------------------------------
TYPES || DUPLICATE || NULL
-------------------------------------------------------------------------------------------
1.Primary Key(PK) NOT ALLOW NOT ALLOW
--------------------------------------------------------------------------------------------
2.Unique Key(U) NOT ALLOW ALLOW
--------------------------------------------------------------------------------------------
3.Forign Key(RK) ALLOW ALLOW
--------------------------------------------------------------------------------------------
4.CHECK(CK) It's Our Own Condition
---------------------------------------------------------------------------------------------
5.NOT NULL(NN) NOT ALLOW
---------------------------------------------------------------------------------------------
Naming Rules::
Examples::
Employee_Id ------------------------> emp_id_pk
email_Id ------------------------> emp_mail_uk
First_Name ------------------------> emp_fname_nn
Salary ------------------------> emp_Salary_ck
Department_Id ------------------------> emp_did_rk
Examples::
-
CREATE TABLE STUDENTS_DB
(
STU_ID NUMBER(5),
STU_NAME VARCHAR2(50) NOT NULL,
STU_GENDER CHAR,
STU_EMAIL VARCHAR2(50),
STU_DID NUMBER(10),
CONSTRAINT STU_ID_PK PRIMARY KEY(STU_ID),
CONSTRAINT STU_GENDER_CK CHECK(STU_GENDER IN ('M','F','m','f')),
CONSTRAINT STU_EMAIL_UK UNIQUE(STU_EMAIL),
CONSTRAINT STU_DID_RK FORIGN KEY(STU_DID) REFERENCES (DEPARTMENT_ID)
);
ALTER SYNTAX::
ALTER TABLE <TABLE_NAME...>
ADD CONSTRAINTS.........
USING ALTER::
-
ALTER TABLE STUDENT_DB
MODIFY STU_NAME VARCHAR2(50) NOT NULL;
CONDITIONS TO USE CONSTRAINTS::
*We can use more than one constraints for a single column.
*We cant alter or Modify constraint except (NOT NULL) Column
*If u want to change Drop and re create again
*We can Alter or Modify the NOT NULL column contraints
*While Create Primary & Unique Constraint automatically the "UNIX INDEX" will created.
NOTE::
-
*Using "DICT" table we can see the data dictionary tables.
Example::
-
SELECT * FROM DICT WHERE TABLE_NAME like '%CONST%';
To Find the Constraint Tables::
-
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('STUDENTS_DB');
SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME = UPPER('STUDENTS_DB');
ON DELETE CASCADE & ON DELETE SET NULL::
-
If We use the "ON DELETE CASCADE" the related data's also deleted from the Child table.
If we us the "ON DELETE SET NULL" the related data's has changed into "NULL" in the CHILD table.
Examples::
-
CONSTRAINTS STU_DID_RK FORIGN KEY (STU_DID) REFERENCES Departments(Department_Id)
ON DELETE CASCADE;
CONSTRAINTS STU_DID_RK FORIGN KEY (STU_DID) REFERENCES Departments(Department_Id)
ON DELETE SET NULL;
ON DELETE CASCADE::
1.When we use ON DELETE CASCADE While Create the FOREIGN Key,If the Parent
table Primary key Value is deleted
then the Child table Value is also deleted
2.When we delete the FOREIGN key Column in a table it will be deleted. but
its not affect the Parent table(Primary KEY
table)
3.In this same case if we try to Drop the Primary key Shows error
4.In this case if we Drop FOREIGN key it will be drop.
CREATE TABLE supplier
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);
Table created.
CREATE TABLE products
( product_id numeric(10) not null,
supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
CONSTRAINT fk_supplier_comp
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name)
ON DELETE CASCADE
);
SQL> INSERT INTO SUPPLIER VALUES('100','Arul','XXX');
1 row created.
SQL> INSERT INTO SUPPLIER VALUES('101','XAVIER','YYY');
1 row created.
SQL> INSERT INTO PRODUCTS VALUES('1001','100','Arul');
1 row created.
SQL> INSERT INTO PRODUCTS VALUES('1002','101','XAVIER');
1 row created.
SQL> SELECT * FROM SUPPLIER;
SUPPLIER_ID SUPPLIER_NAME CONTACT_NAME
----------- -----------------------------------------------------
100 Arul XXX
101 XAVIER YYY
SQL> SELECT * FROM PRODUCTS;
PRODUCT_ID SUPPLIER_ID SUPPLIER_NAME
---------- ----------- --------------------------------
1001 100 Arul
1002 101 XAVIER
SQL> DELETE FROM SUPPLIER WHERE SUPPLIER_ID=100;
1 row deleted.
SQL> SELECT * FROM SUPPLIER;
SUPPLIER_ID SUPPLIER_NAME CONTACT_NAME
----------- --------------- -------------------------------------
101 XAVIER YYY
SQL> SELECT * FROM PRODUCTS;
PRODUCT_ID SUPPLIER_ID SUPPLIER_NAME
---------- ----------- --------------------------------
1002 101 XAVIER
iii)In Primary Key Case::
SQL> ALTER TABLE SUPPLIER
2 DROP CONSTRAINT supplier_pk;
DROP CONSTRAINT supplier_pk
*
ERROR at line 2:
ORA-02273: this unique/primary key is referenced by some foreign keys
iv)In Foreign Key Case::
SQL> ALTER TABLE PRODUCTS
2 DROP CONSTRAINT fk_supplier_comp;
Table altered.
=======================================================================
ON DELETE SET NULL::
1.When we Set the ON DELETE SET NULL in Child table ,
While delete the Parent table Primary column the refered Child
column values changed into NULL.
2.If we delete the Foreign kay table values it will be deleted.
3.if we try to Drop primary key Shows ERROR .
4.If we dro the foreign key it will dropped.
CREATE TABLE supplier
( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
);
Table created.
CREATE TABLE products
( product_id numeric(10) not null,
supplier_id numeric(10) ,
supplier_name varchar2(50) ,
CONSTRAINT fk_supplier_comp
FOREIGN KEY (supplier_id, supplier_name)
REFERENCES supplier(supplier_id, supplier_name)
ON DELETE SET NULL
);
SQL> DELETE FROM SUPPLIER WHERE SUPPLIER_ID=100;
1 row deleted.
SQL> SELECT * from SUPPLIER;
SUPPLIER_ID SUPPLIER_NAME CONTACT_NAME
----------- ---------------- -----------------------------------------
101 XAVIER YYY
SQL> SELECT * from PRODUCTS;
PRODUCT_ID SUPPLIER_ID SUPPLIER_NAME
---------- ----------- ------------------------------
1001
1002 101 XAVIER
CASE 3::
--------
SQL> ALTER TABLE SUPPLIER
2 DROP CONSTRAINT supplier_pk;
DROP CONSTRAINT supplier_pk
*
ERROR at line 2:
ORA-02273: this unique/primary key is referenced by some foreign keys
CASE 4::
SQL> ALTER TABLE PRODUCTS
2 DROP CONSTRAINT fk_supplier_comp;
Table altered.
================================================================
UNIQUE KEY + NOT NULL ====>PRIMARY KEY:
Example::
CREATE TABLE College_Master(Clg_Id NUMBER NOT NULL,
UNIQUE (Clg_Id),
College_Name VARCHAR2(30)
);
SQL> INSERT INTO College_Master VALUES(1,'XYZ');
1 row created.
SQL> INSERT INTO College_Master VALUES(1,'ABC');
INSERT INTO College_Master VALUES(1,'ABC')
*
ERROR at line 1:
ORA-00001: unique constraint (HR.SYS_C004129) violated
SQL> INSERT INTO College_Master VALUES(NULL,'ABC');
INSERT INTO College_Master VALUES(NULL,'ABC')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."COLLEGE_MASTER"."CLG_ID")
TO ENABLE & DISABLE & DROP CONSTRAINTS::
ENABLE::
ALTER TABLE TABLE_NAME
ENABLE CONSTRAINT CONSTRAINT_NAME....
DISABLE::
ALTER TABLE TABLE_NAME
DISABLE CONSTRAINT CONSTRAINT_NAME....
DROP::
ALTER TABLE TABLE_NAME
DROP CONSTRAINT CONSTRAINT_NAME....
-------END CONSTRAINTS------
