Constraints:
------------
1.it is used to controlling the data in a table.
2.it is used to prevent the invalid data entry into table.
3.it also used to maintain the security of the table.
4.it is used to implement the business rules.
or
The Process or enforcing the business rules on database tables is called "Data Integrity".
There are two types of data integrity:
1.Predefined integrity [constraints]
2.User defined integrity [ Database triggers]
oracle supports 3 types of Integrirty constriants:
**************************************************
1.Domain Integrity Constraints
2.Entity Integrity Consraints
3.Referential Integrity Constraints
Domain Integrity Constraints:
*****************************
-->Domain is nothing but column.It ensure that to check values with user define condition before accepting values into a column.
-->To perform this mechanism we can use CHECK,NOT NULL AND DEFAULT constraints.
1.check constraints---
2.not null constraints
3.default constraints
Entity Integrity Constraints:
*****************************
-->It ensure that each row uniquely identify in a table.
-->To implement this mechanism we can use PRIMARY KEY OR UNIQUE Constraint.
1.primary key constraints
2.unique key constraints
3.Referential integrity constraints:
************************************
-->It ensure that to create relatonship between tables.
-->To implement this mechanism we can use FOREIGN KEY(REFERENCIAL KEY) Constraint.
1)foreign key constraints
CONSTRAINTS:
*************
-->Constraints are used to restricted unwanted(Invalid) data into table.
-->All databases are supporting below constraints.
1)CHECK
2)NOT NULL
3)DEFAULT
4)UNIQUE
5)PRIMARY KEY
6)FOREIGN KEY(REFERENCES KEY)
- ALL DATABASES ARE SUPPORTING THE FOLLOWING TWO TYPES OF METHODS TO DEFINE CONSTRAINTS.THOSE ARE
1)COLUMN LEVEL:
****************
-->In this method we are defning constraints on individual columns.
SYNTAX:
********
CREATE TABLE <TN>(<COLUMN NAME1><DATATYPE>[SIZE] <CONSTRAINT> <CONSTRAINT NAME> <CONSTRAINT TYPE>,......);
2)TABLE LEVEL:
**************
-->In this method we are defining constraints after all columns are declared.(End of the table definition)
Constraint syntax:
*******************
Columnname Datatype<SIZE> constraint <constraint_name> <constraint_type>
NOT NULL:
*********
-->It is used to allow only not null values.
-->It does not allow null values.
-->It Can be defined at column level only.
-->NOT NULL constraint not supports "TABLE LEVEL".
COLUMN LEVEL:
*************
EX:
***
CREATE TABLE XX_STUDENT_NOTNULL
(
SNO NUMBER,
SNAME VARCHAR2(20) NOT NULL,
FEE NUMBER
);
/
--TESTING
INSERT INTO XX_STUDENT_NOTNULL VALUES (1,'A',1000); --VALID
/
INSERT INTO XX_STUDENT_NOTNULL VALUES (1,'B',1000); --VALID
/
INSERT INTO XX_STUDENT_NOTNULL VALUES (1,'A',1000); --VALID
/
INSERT INTO XX_STUDENT_NOTNULL VALUES (2,NULL,3000); --INVALID
/
INSERT INTO XX_STUDENT_NOTNULL VALUES (3,'',6000); --INVALID
SELECT *
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME='XX_STU_NOTNULL';
/
CREATE TABLE XX_STU_NN
(
SNO NUMBER NOT NULL,
SNAME VARCHAR2(30)
);
/
DESC XX_STU_NN;
/
TESTING:
********
INSERT INTO XX_STU_NN VALUES (100,'MOHAN');
INSERT INTO XX_STU_NN VALUES (100,'MADAN');
INSERT INTO XX_STU_NN VALUES (NULL,'MOHAN');
/
CREATE TABLE XX_EMP_NN
(
EMPNO NUMBER CONSTRAINT EMP_CON_NN NOT NULL,
ENAME VARCHAR2(20),
SAL NUMBER
);
/
DESC XX_EMP_NN;
/
CREATE TABLE XX_EMP_NN1_1
(
EMPNO NUMBER CONSTRAINT EMP_CON_NN11 NOT NULL,
ENAME VARCHAR2(20) NOT NULL,
SAL NUMBER
);
/
--TESTING
INSERT INTO XX_EMP_NN1_1 VALUES(1,'A',1000); --VALID
/
INSERT INTO XX_EMP_NN1_1 VALUES(NULL,'B',2000); --INVALID
/
INSERT INTO XX_EMP_NN1_1 VALUES(2,'',3000); --INVALID
/
INSERT INTO XX_EMP_NN1_1 VALUES(NULL,NULL,5000); --INVALID
/
INSERT INTO XX_EMP_NN1_1 VALUES(1,'A',1000); --VALID
/
CHECK:
******
-->It is used to check the condition
-->Allows valid range of values only.
-->It is used to validate the data based on the condition.
-->To check values with user defined condition before accepting values into a column.
-->It can be define at column level or table level.
1)COLUMN LEVEL:
*****************
EX:
***
CREATE TABLE XX_CHECK_CONSTRAINT
(
SNO NUMBER,
SNAME VARCHAR2(20),
FEE NUMBER CHECK(FEE>=3000),
JDATE DATE
);
/
INSERT INTO XX_CHECK_CONSTRAINT VALUES(1,'A',3000,SYSDATE); --VALID
/
INSERT INTO XX_CHECK_CONSTRAINT VALUES(2,'B',5000,SYSDATE); --VALID
/
INSERT INTO XX_CHECK_CONSTRAINT VALUES(3,'C',2000,SYSDATE); --INVALID
/
SELECT *
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME='XX_CHECK_CONSTRAINT';
/
CREATE TABLE XX_CHECK_STUDENT
(
SNO NUMBER,
SNAME VARCHAR2(20),
FEE NUMBER CHECK(FEE>=5000),
MARKS NUMBER CONSTRAINT CHS1 CHECK(MARKS BETWEEN 0 AND 100)
);
/
INSERT INTO XX_CHECK_STUDENT VALUES (1,'A',5000,90); --VALID
/
INSERT INTO XX_CHECK_STUDENT VALUES (2,'B',6000,80); --VALID
/
INSERT INTO XX_CHECK_STUDENT VALUES (3,'A',3000,100); --INVALID
/
INSERT INTO XX_CHECK_STUDENT VALUES (1,'A',8000,110); --INVALID
/
SELECT * FROM XX_CHECK_STUDENT;
/
SELECT *
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME='XX_CHECK_STUDENT';
/
CREATE TABLE XX_CHK_CON
(
SNO NUMBER,
SNAME VARCHAR2(20),
MARKS NUMBER CHECK(MARKS BETWEEN 0 AND 100)
);
/
INSERT INTO XX_CHK_CON VALUES(1,'A',100); --ALLOWED
/
INSERT INTO XX_CHK_CON VALUES(2,'B',90); --ALLOWED
/
INSERT INTO XX_CHK_CON VALUES(3,'C',101); --NOT ALLOWED
/
CREATE TABLE XX_CHK_CON1
(
SNO NUMBER,
SNAME VARCHAR2(20),
MARKS NUMBER CONSTRAINT CHK_CONS CHECK(MARKS>=0 AND MARKS<=100)
);
/
--Testing
INSERT INTO XX_CHK_CON1 VALUES(1,'A',100); --ALLOWED
/
INSERT INTO XX_CHK_CON1 VALUES(2,'B',85); --ALLOWED
/
INSERT INTO XX_CHK_CON1 VALUES(3,'C',-100); --NOT ALLOWED
/
INSERT INTO XX_CHK_CON1 VALUES(4,'D',NULL); --ALLOWED
/
SELECT * FROM XX_CHK_CON1;
/
ALL_CONSTRAINTS --Base Table
/
SELECT *
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME='XX_CHK_CON'; --SYS_C00208323 --SYSTEM DEFINED CONSTRAINTS
/
SELECT *
FROM ALL_CONSTRAINTS
WHERE TABLE_NAME='XX_CHK_CON1'; --CHK_CONS --USER DEFINED CONSTRAINTS
/
CREATE TABLE XX_TEST1
(EMPNO NUMBER,
SAL NUMBER(10) CHECK(SAL>=10000)
);
/
TESTING:
********
INSERT INTO XX_TEST1 VALUES(1,9500);---NOT ALLOW
INSERT INTO XX_TEST1 VALUES(1,10000);---ALLOW
/
CREATE TABLE XX_STU_CHECK
(
SNO NUMBER,
SNAME VARCHAR2(30),
AGE NUMBER(2) CHECK(AGE>=18)
);
/
TESTING:
********
INSERT INTO XX_STU_CHECK VALUES (100,'MOHAN',20); --VALID
INSERT INTO XX_STU_CHECK VALUES (100,'MOHAN',40); --VALID
INSERT INTO XX_STU_CHECK VALUES (100,'MOHAN',17); --INVALID
/
CREATE TABLE XX_CHECK_CON2
(
SNO NUMBER,
SNAME VARCHAR2(20),
MARKS NUMBER CONSTRAINT XXCHK3 CHECK(MARKS BETWEEN 0 AND 100),
FEE NUMBER CHECK(FEE>=3000)NOT NULL
);
/
INSERT INTO XX_CHECK_CON2 VALUES (3,'C',80,2500); --INVALID
/
INSERT INTO XX_CHECK_CON2 VALUES (3,'C',80,NULL); --INVALID
/
INSERT INTO XX_CHECK_CON2 VALUES (3,'C',80,4500); --VALID
/
TABLE LEVEL:
************
EX:
***
create table XX_STU_CHECK_1
(
sname varchar2(20),
sno number(3),
marks number(3),
constraint student_c1_marks_1 check(marks between 0 and 100)
);
/
INSERT INTO XX_STU_CHECK_1 VALUES ('A',1,100); --VLIID
/
INSERT INTO XX_STU_CHECK_1 VALUES ('B',2,-90); --INVALID
/
INSERT INTO XX_STU_CHECK_1 VALUES ('A',1,150); --INVALID
/
CREATE TABLE XX_STU_CHECK_2
(
ENAME VARCHAR2(10),
SAL NUMBER(10),
CHECK(ENAME=LOWER(ENAME) AND SAL>8000)
);
/
INSERT INTO XX_STU_CHECK_2 VALUES('ABC',9000); --INVALID
/
INSERT INTO XX_STU_CHECK_2 VALUES('def',7000); --invalid
/
INSERT INTO XX_STU_CHECK_2 VALUES('xyz',17000); --valid
/
CREATE TABLE XX_EMP_CHECK
(
EMPNO NUMBER,
ENAME VARCHAR2(20),
SAL NUMBER,
DEPTNO NUMBER CHECK(DEPTNO IN (10,20,30));
/
CREATE TABLE XX_EMP_CHECK1
(
EMPNO NUMBER,
ENAME VARCHAR2(20),
SAL NUMBER,
DEPTNO NUMBER CHECK(DEPTNO IS NOT NULL);
/
DEFAULT:
********
-->It is used to insert default values.
EX:
***
CREATE TABLE XX_EMP_DEFAULT
(
EMPNO NUMBER,
ENAME VARCHAR2(20),
SAL NUMBER DEFAULT 1000
);
/
--TESTING
INSERT INTO XX_EMP_DEFAULT VALUES(1,'A',3000); --VALIID
/
INSERT INTO XX_EMP_DEFAULT VALUES(2,'B',NULL); --VALID
/
SELECT * FROM XX_EMP_DEFAULT;
/
INSERT INTO XX_EMP_DEFAULT(EMPNO,ENAME) VALUES(3,'C');
/
SELECT * FROM XX_EMP_DEFAULT;
/
CREATE TABLE XX_STU_DEFAULT_1
(
SNO NUMBER,
SNAME VARCHAR2(20),
NATION VARCHAR2(10) DEFAULT('IND')
);
/
--TESTING
INSERT INTO XX_STU_DEFAULT_1 VALUES(1,'A','INDIA'); --VALID
/
INSERT INTO XX_STU_DEFAULT_1 VALUES(2,'B','UK'); --VALID
/
INSERT INTO XX_STU_DEFAULT_1 VALUES(1,'A','USA'); --VALID
/
INSERT INTO XX_STU_DEFAULT_1 VALUES(3,'D',''); --VALID
/
SELECT * FROM XX_STU_DEFAULT_1;
/
INSERT INTO XX_STU_DEFAULT_1(SNO,SNAME) VALUES(4,'E');
/
SELECT * FROM XX_STU_DEFAULT_1;
/
CREATE TABLE XX_EMP_DEFAULT
(
EMPNO NUMBER,
ENAME VARCHAR2(20),
JOB VARCHAR2(30),
SAL NUMBER,
DEPTNO numebr DEFAULT 10
);
/
CREATE TABLE XX_EMP_DEFAULT1
(
EMPNO NUMBER,
ENAME DEFAULT 'XYZ',
JOB VARCHAR2(30),
SAL NUMBER,
DEPTNO NUMBER
);
/
CREATE TABLE XX_EMP_DEFAULT2
(
EMPNO NUMBER,
ENAME VARCHAR2(20),
JOB VARCHAR2(30),
HIREDATE DATE DEFAULT SYSDATE,
SAL NUMBER,
DEPTNO NUMBER
);
/
CREATE TABLE XX_EMP_DEFAULT3
(
EMPNO NUMBER,
ENAME VARCHAR2(20),
JOB VARCHAR2(30),
HIREDATE DEFAULT SYSTIMESTAMP,
SAL NUMBER,
DEPTNO DEFAULT 20
);
/
UNIQUE:
******
-->It is allow only unique values.
-->It does not allow duplicate records
-->It accepts more than one null value.
-->A table allow more than one uniue key.
-->It restricted duplicate values but accepting nulls into a column.
1)COLUMN LEVEL:
****************
EX:
****
CREATE TABLE XX_EMP_UNIQUE
(
EMPNO NUMBER UNIQUE,
ENAME VARCHAR2(20) NOT NULL,
SAL NUMBER CHECK(SAL>=5000),
NATION VARCHAR2(20) DEFAULT 'INDIA'
);
/
INSERT INTO XX_EMP_UNIQUE VALUES(100,'A',2000,'INDIA'); --INVALID
/
INSERT INTO XX_EMP_UNIQUE VALUES(101,'',9000,'IUSA'); --INVALID
/
INSERT INTO XX_EMP_UNIQUE VALUES(102,'A',9000,'INDIA'); --VALID
/
INSERT INTO XX_EMP_UNIQUE VALUES(103,'B',10000,''); --VALID
/
INSERT INTO XX_EMP_UNIQUE VALUES(100,'A',12000,'INDIA'); --VALID
/
INSERT INTO XX_EMP_UNIQUE VALUES(100,'B',22000,'USA'); --INVALID
/
INSERT INTO XX_EMP_UNIQUE VALUES(NULL,'E',32000,'UK'); --VALID
/
INSERT INTO XX_EMP_UNIQUE VALUES(NULL,NULL,42000,'INDIA'); --INVALID
/
CREATE TABLE XX_STU_UNIQUE
(
SNO NUMBER UNIQUE,
SNAME VARCHAR2(20)
);
/
TESTING:
*********
INSERT INTO XX_STU_UNIQUE VALUES (100,'MOHAN'); --VALID
/
INSERT INTO XX_STU_UNIQUE VALUES (100,'MADAN'); --INVALID
/
INSERT INTO XX_STU_UNIQUE VALUES (NULL,'VINOD'); --VALID
/
INSERT INTO XX_STU_UNIQUE VALUES (NULL,'RAJA'); --VALID
/
2)TABLE LEVEL:
****************
EX:
****
CREATE TABLE XX_UNIQUE_CON
(SNO NUMBER,
SNAME VARCHAR2(10),
MARKS NUMBER,
UNIQUE(SNO,SNAME)
);
/
--TESTING
INSERT INTO XX_UNIQUE_CON VALUES(1000,'A',100); --VLID
/
INSERT INTO XX_UNIQUE_CON VALUES(1000,'B',90); --VALID
/
SELECT * FROM XX_UNIQUE_CON;
/
INSERT INTO XX_UNIQUE_CON VALUES(1000,'C',80); --VALID
/
INSERT INTO XX_UNIQUE_CON VALUES(1000,'B',70); --INVALID
/
NOTE:
******
-->When we apply UNIQUE constraint on group of columns then we called as "COMPOSITE UNIQUE" constraint.
-->In this mechanism individual columns are accepting duplicate values but duplicate combination of columns data is not allowed.
PRIMARY KEY:
************
-->It is used to define the key column of a table.
-->It can be used only once in table definition
-->It will not allow null values and duplicate values into key column
-->It is supported with an index automatically.
-->Numeric columns are prefered as primary keys than character columns.
1)COLUMN LEVEL:
****************
EX:
***
/
CREATE TABLE XX_EMP_PRIMAY_KEY
(
EMPNO NUMBER PRIMARY KEY,
ENAME VARCHAR2(20) ,
SAL NUMBER
);
/
--TESTING
INSERT INTO XX_EMP_PRIMAY_KEY VALUES(2000,'A',5000); --VALID
/
INSERT INTO XX_EMP_PRIMAY_KEY VALUES(2001,'',6000); --VALID
/
INSERT INTO XX_EMP_PRIMAY_KEY VALUES(2000,'B',8000); --INVALID
/
INSERT INTO XX_EMP_PRIMAY_KEY VALUES(NULL,'C',6000); --INVALID
/
CREATE TABLE XX_PK_CON
(
SNO NUMBER PRIMARY KEY,
SNAME VARCHAR2(20) NOT NULL,
BRANCH VARCHAR2(20) UNIQUE,
MARKS NUMBER CONSTRAINT PK_CH_CON CHECK(MARKS BETWEEN 0 AND 100)
);
/
--Testing
INSERT INTO XX_PK_CON VALUES(1,'A','CSE',87); --ALLOWED
/
INSERT INTO XX_PK_CON VALUES(2,'B','CSE',87); -- NOT ALLOWED UNIQUE CONSTRAINT VIOLATED
/
INSERT INTO XX_PK_CON VALUES(1,'A','EEE',77); --NOT ALLOWED UNIQUE CONSTRAINT VIOLATED
/
INSERT INTO XX_PK_CON VALUES(NULL,'A','CSE',87); --NOT ALLOWED NOT NULL CONSTRAINT VIOLATED
/
CREATE TABLE XX_TEST4
(
EMPNO NUMBER PRIMARY KEY,
ENAME VARCHAR2(10) PRIMARY KEY
);
ERROR at line 1:
ORA-02260: table can have only one primary key.
/
CREATE TABLE XX_STU_PK
(
SNO NUMBER PRIMARY KEY,
SNAME VARCHAR2(20)
);
/
INSERT INTO XX_STU_PK VALUES (100,'MOHAN'); --VALID
INSERT INTO XX_STU_PK VALUES (100,'VINOD'); --INVALID
INSERT INTO XX_STU_PK VALUES (101,'MADAN'); --VALID
INSERT INTO XX_STU_PK VALUES (NULL,'VINOD'); --INVALID
/
CREATE TABLE XX_emp2
(
ename varchar2(10),
empno number(4) constraint emp2_c1_empno primary key,
gender char(1) constraint emp2_c2_gender check(gender in('m','f','M','F')),
salary number(5) constraint emp2_c3_salary check(salary between 2000 and 5000),
panno varchar2(10) constraint emp2_c4_panno check(length(panno)=10) unique not null,
Aadhar number(12) constraint emp2_c5_Aadhar check(length(Aadhar)=12) unique not null
);
/
--TESTING
INSERT INTO XX_EMP2 VALUES('A',200,'M',4000,'ALTPG4031B','123456789123'); --VALID
/
INSERT INTO XX_EMP2 VALUES('A',201,'F',4000,'ALTPG4031','123456789123'); --INVALID
/
INSERT INTO XX_EMP2 VALUES('B',202,'M',5000,'ALTPG4031B','12345678912'); --INVALID
/
INSERT INTO XX_EMP2 VALUES('A',200,'M',4000,'ALTPG4031B','123456789123'); --INVALID
/
2)TABLE LEVEL:
**************
EX:
***
/
CREATE TABLE XX_CPK_CON
(
SNO NUMBER,
SNAME VARCHAR2(20),
MARKS NUMBER,
PRIMARY KEY(SNO,SNAME)
);
/
INSERT INTO XX_CPK_CON VALUES (1,'A',90); --ALLOWED
/
INSERT INTO XX_CPK_CON VALUES (2,'A',80); --ALLOWED
/
INSERT INTO XX_CPK_CON VALUES (3,'',90); --NOT ALLOWED
/
INSERT INTO XX_CPK_CON VALUES (NULL,'A',90); --NOT ALLOWED
/
INSERT INTO XX_CPK_CON VALUES (1,'A',90); --NOT ALLOWED
/
CREATE TABLE XX_TEST5
(
DEPTNO NUMBER,
DNAME VARCHAR2(10),
LOC VARCHAR2(10),
PRIMARY KEY(DEPTNO,DNAME));
TESTING:
*******
INSERT INTO XX_TEST5 VALUES(1021,'SBI','SRNAGAR');---ALLOW
INSERT INTO XX_TEST5 VALUES(1021,'SBI','MADHAPUR');---NOT ALLOW
INSERT INTO XX_TEST5 VALUES(1022,'SBI','MADHAPUR');---ALLOW
INSERT INTO XX_TEST5 VALUES(1021,'ICICI','SRNAGAR');----ALLOW
NOTE : WHEN WE APPLY PRIMARY KEY CONSTRAINT ON GROUP OF COLUMNS THEN WE CALLED AS "COMPOSITE PRIMARY KEY" CONSTRAINT.IN THIS MECHANISM INDIVIDUAL COLUMNS ARE ACCEPTING DUPLICATE VALUES BUT DUPLICATE COMBINATION OF COLUMNS DATA IS NOT ALLOWED.
COMPOSITE PRIMARY KEY:
**********************
-->It is used to create primary key on multiple columns.
EX:
***
CREATE TABLE XX_STU_CPK
(
SNO NUMBER,
SNAME VARCHAR2(30), PRIMARY KEY(SNO,SNAME)
);
/
INSERT INTO XX_STU_CPK VALUES (100,'MOHAN');
INSERT INTO XX_STU_CPK VALUES (100,'VINOD');
INSERT INTO XX_STU_CPK VALUES (100,'MOHAN');
/
CREATE TABLE ORDER_MASTER(ORDERDATE DATE,ORDERNO NUMBER(4),CONSTRAINT ORDER_MASTER_C1_ORDER PRIMARY KEY(ORDERDATE,ORDERNO))
INSERT INTO ORDER_MASTER VALUES('05-03-19',1);
INSERT INTO ORDER_MASTER VALUES('05-03-19',2);
INSERT INTO ORDER_MASTER VALUES('05-03-19',3);
INSERT INTO ORDER_MASTER VALUES('06-03-19',1);
SELECT * FROM ORDER_MASTER
/
FOREIGN KEY:
************
-->It is used to maintain a reference from one table to another table.
-->It is used to create parent child relationship between 2 or many tables.
-->It always refers to primary key column / Unique key of the parent table.
-->One table FOREIGN KEY must belongs to another table PRIMARY KEY and PRIMARY KEY & FOREIGN KEY column must be common column.
-->Primary key table is called as "PARENT TABLE" and Foreign key table is called as "CHILD TABLE".(i.e PARENT & CHILD RELATIONSHIP).
-->Generally Primary key is not allowed duplcate and null values where as Foreign key accepts duplicates and null values.
-->After create relationship parent table records cannot be deleted.
COLUMN LEVEL:
**************
SYNTAX:
--------------
<COMMON COLUMN NAME OF CHILD> <DT>[SIZE] REFERENCES
<PARENT TABLE NAME>(<COMMON COLUMN NAME OF PARENT>)
EX:
***
STEP1:
*******
/
CREATE TABLE DEPT_PARENT
(
DEPTNO NUMBER PRIMARY KEY,
DNAME VARCHAR2(20),
LOC VARCHAR2(20)
);
/
<COMMON COLUMN NAME OF CHILD> <DT>[SIZE] REFERENCES
<PARENT TABLE NAME>(<COMMON COLUMN NAME OF PARENT>)
/
CREATE TABLE EMP_CHILD
(
EMPNO NUMBER PRIMARY KEY,
ENAME VARCHAR2(20),
SAL NUMBER,
DEPTNO NUMBER(2) REFERENCES DEPT_PARENT(DEPTNO)
);
/
INSERT INTO DEPT_PARENT VALUES(10,'SALES','HYD');
/
INSERT INTO DEPT_PARENT VALUES(20,'HR','CHN');
/
INSERT INTO DEPT_PARENT VALUES(30,'RESEARCH','BNG');
/
INSERT INTO DEPT_PARENT VALUES(40,'OPERATIONS','MUM');
/
INSERT INTO DEPT_PARENT VALUES(10,'SALES','HYD'); --NOT ALLOWED
/
COMMIT;
/
SELECT * FROM DEPT_PARENT;
/
INSERT INTO EMP_CHILD VALUES(1,'A',1000,10);
/
INSERT INTO EMP_CHILD VALUES(2,'B',2000,10);
/
INSERT INTO EMP_CHILD VALUES(3,'C',1000,10);
/
INSERT INTO EMP_CHILD VALUES(4,'D',3000,20);
/
INSERT INTO EMP_CHILD VALUES(5,'E',4000,20);
/
INSERT INTO EMP_CHILD VALUES(6,'F',3000,30);
/
INSERT INTO EMP_CHILD VALUES(7,'G',5000,30);
/
INSERT INTO EMP_CHILD VALUES(8,'H',7000,40);
/
INSERT INTO EMP_CHILD VALUES(9,'I',8000,50); -- NOT ALLOWED --WITH OUT A PARENT YOU CAN NOT CREATE ANY CHILD
/
COMMIT;
/
SELECT *
FROM EMP_CHILD;
/
DELETE FROM DEPT_PARENT;
/
CREATE TABLE XX_DEPT1
(
DEPTNO NUMBER PRIMARY KEY,
DNAME VARCHAR2(10)
);
/
STEP2:
******
INSERT INTO XX_DEPT1 VALUES(10,'ORACLE');
INSERT INTO XX_DEPT1 VALUES(20,'JAVA');
STEP3:
*******
CREATE TABLE XX_EMP1
(EMPNO NUMBER PRIMARY KEY,
ENAME VARCHAR2(10),
DEPTNO NUMBER REFERENCES XX_DEPT1(DEPTNO));
STEP4:
******
INSERT INTO XX_EMP1 VALUES(1021,'SAI',10);
INSERT INTO XX_EMP1 VALUES(1022,'JONES',10);
INSERT INTO XX_EMP1 VALUES(1023,'MILLER',20);
--> ONCE WE ESTABLISH RELATIONSHIP BETWEEN TABLES THERE ARE TWO RULES ARE COME INTO PICTURE.THOSE ARE
1)INSERTION RULE:
=================
--> WE CANNOT INSERT VALUES INTO FOREIGN KEY(REFERENCES KEY) COLUMN THOSE VALUES ARE NOT EXISTING UNDER PRIMARY KEY COLUMN OF PARENT TABLE.
EX:
****
INSERT INTO EMP1 VALUES(1026,'SCOTT',30);
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.SYS_C005468) violated - parent key not found.
2) DELETION RULE:
******************
-->WHEN WE TRY TO DELETE A RECORD FROM PARENT TABLE AND THOSE ASSOCIATED RECORDS ARE AVAILABLE IN CHILD TABLE THEN ORACLE RETURNS AN ERROR IS,
EX:
***
DELETE FROM DEPT1 WHERE DEPTNO=20;
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.SYS_C005468) violated - child record found.
NOTE:
*****
IF WE WANT TO DELETE A RECORD FROM PARENT TABLE WHEN THEY HAVE CORRESPONDING CHILD RECORDS IN CHILD TABLE THEN WE PROVIDE SOME SET OF RULES TO PERFORM DELETE OPERATIONS ON PARENT TABLE.THOSE RULES ARE CALLED AS "CASCADE RULES".
i) ON DELETE CASCADE
ii) ON DELETE SET NULL
/
CREATE TABLE XX_COURSE_MASTER
(
CNO NUMBER PRIMARY KEY,
CNAME VARCHAR2(30)
);
/
CREATE TABLE XX_STU_CHILD
(
SNO NUMBER PRIMARY KEY,
SNAME VARCHAR2(30),CNO NUMBER REFERENCES XX_COURSE_MASTER(CNO)
);
/
INSERT INTO XX_COURSE_MASTER VALUES(10,'ORACLE');
INSERT INTO XX_COURSE_MASTER VALUES(20,'JAVA');
INSERT INTO XX_COURSE_MASTER VALUES(30,'C');
INSERT INTO XX_COURSE_MASTER VALUES(40,'C++');
/
INSERT INTO XX_STU_CHILD VALUES(100,'MOHAN',10);
INSERT INTO XX_STU_CHILD VALUES(101,'MADAN',20);
INSERT INTO XX_STU_CHILD VALUES(102,'VINOD',30);
INSERT INTO XX_STU_CHILD VALUES(103,'RAJA',40);
INSERT INTO XX_STU_CHILD VALUES(104,'RANI',50);
INSERT INTO XX_STU_CHILD VALUES(104,'RANI',NULL);
/
on delete cascade:
******************
-->By using on delete cascade, We can delete the rows from the parent table and the corresponding child table rows deleted automatically.
EX:
****
STEP1:
******
CREATE TABLE XX_DEPT2
(DEPTNO NUMBER PRIMARY KEY,
DNAME VARCHAR2(10));
STEP2:
******
INSERT INTO XX_DEPT2 VALUES(10,'ORACLE');
INSERT INTO XX_DEPT2 VALUES(20,'JAVA');
STEP3:
******
CREATE TABLE XX_EMP2
(
EMPNO NUMBER PRIMARY KEY,
ENAME VARCHAR2(10),
DEPTNO NUMBER REFERENCES XX_DEPT2(DEPTNO) ON DELETE CASCADE
);
STEP4:
******
INSERT INTO XX_EMP2 VALUES(1021,'SAI',10);
INSERT INTO XX_EMP2 VALUES(1022,'JONES',10);
INSERT INTO XX_EMP2 VALUES(1023,'MILLER',20);
TESTING:
*********
DELETE FROM XX_DEPT2 WHERE DEPTNO=20;----ALLOWED
/
EX:
****
/
CREATE TABLE DEPT_PARENT1
(
DEPTNO NUMBER PRIMARY KEY,
DNAME VARCHAR2(20),
LOC VARCHAR2(20)
);
/
CREATE TABLE DEPT_EMP1
(
EMPNO NUMBER PRIMARY KEY,
ENAME VARCHAR2(20),
SAL NUMBER,
DEPTNO NUMBER(2) REFERENCES DEPT_PARENT1(DEPTNO) ON DELETE CASCADE
);
/
INSERT INTO DEPT_PARENT1 VALUES (10,'SALES','HR');
/
INSERT INTO DEPT_PARENT1 VALUES (20,'HR','CHN');
/
INSERT INTO DEPT_PARENT1 VALUES (30,'RESEARCH','BNG');
/
INSERT INTO DEPT_PARENT1 VALUES (40,'OPERATIONS','MUM');
/
COMMIT;
/
SELECT * FROM DEPT_PARENT1;
/
INSERT INTO DEPT_EMP1 VALUES(1,'A',1000,10);
/
INSERT INTO DEPT_EMP1 VALUES(2,'B',2000,10);
/
INSERT INTO DEPT_EMP1 VALUES(3,'C',3000,20);
/
INSERT INTO DEPT_EMP1 VALUES(4,'D',4000,20);
/
INSERT INTO DEPT_EMP1 VALUES(5,'E',5000,30);
/
INSERT INTO DEPT_EMP1 VALUES(6,'F',4000,30);
/
INSERT INTO DEPT_EMP1 VALUES(7,'G',1000,40);
/
INSERT INTO DEPT_EMP1 VALUES(8,'H',1000,50); -- IT WILL THROW THE ERROR
/
COMMIT;
/
SELECT * FROM DEPT_EMP1;
/
SELECT * FROM DEPT_PARENT1 WHERE DEPTNO=30;
/
SELECT * FROM DEPT_EMP1 WHERE DEPTNO=30;
/
DELETE FROM DEPT_PARENT1 WHERE DEPTNO=30;
/
CREATE TABLE DEPT_101
(
DEPTNO NUMBER PRIMARY KEY,
DNAME VARCHAR2(20),
LOC VARCHAR2(20)
);
/
CREATE TABLE EMP_101
(
EMPNO NUMBER PRIMARY KEY,
ENAME VARCHAR2(20),
JOB VARCHAR2(20),
SAL NUMBER,
DEPTNO NUMBER REFERENCES DEPT_101(DEPTNO) ON DELETE CASCADE
);
/
INSERT INTO DEPT_101 VALUES(10,'HR','HYD');
/
INSERT INTO DEPT_101 VALUES(20,'SALES','CHN');
/
INSERT INTO DEPT_101 VALUES(30,'RESEARCH','BNG');
/
INSERT INTO DEPT_101 VALUES(40,'ACCOUNTING','MUN');
/
COMMIT;
/
SELECT * FROM DEPT_101;
/
INSERT INTO EMP_101 VALUES (1,'A','HR',1000,10);
/
INSERT INTO EMP_101 VALUES (2,'B','HR1',1000,10);
/
INSERT INTO EMP_101 VALUES (3,'C','HR2',2000,10);
/
INSERT INTO EMP_101 VALUES (4,'D','HR3',1000,20);
/
INSERT INTO EMP_101 VALUES (5,'E','HR4',4000,20);
/
INSERT INTO EMP_101 VALUES (6,'F','HR5',3000,30);
/
INSERT INTO EMP_101 VALUES (7,'G','HR6',3000,30);
/
COMMIT;
/
SELECT * FROM DEPT_101;
/
SELECT * FROM EMP_101;
/
DELETE FROM DEPT_101 WHERE DEPTNO=10;
/
SELECT *
FROM DEPT_101
WHERE DEPTNO=10;
/
SELECT *
FROM EMP_101
WHERE DEPTNO=10;
/
--ROLLBACK;
/
EX:
***
CREATE TABLE XX_CONTINENT
(
CON_ID NUMBER PRIMARY KEY,
CON_NAME VARCHAR2(20)
);
/
CREATE TABLE XX_COUNTRY
(
COUNTRY_ID NUMBER PRIMARY KEY,
COUNTRY_NAME VARCHAR2(20),
CONT_ID NUMBER, CONSTRAINT COUNTRY_FK FOREIGN KEY(CONT_ID) REFERENCES CONTINENT(CON_ID)
);
/
CREATE TABLE XX_STATE
(
STATE_ID NUMBER PRIMARY KEY,
STATE_NAME VARCHAR2(20),
COUNTRY_ID NUMBER,
CONSTRAINT STATE_FK FOREIGN KEY(COUNTRY_ID) REFERENCES COUNTRY(COUNTRY_ID)
);
/
INSERT INTO XX_CONTINENT VALUES(5000,'ASIA');
/
COMMIT;
/
INSERT INTO XX_COUNTRY VALUES(1000,'INDIA',5000);
/
INSERT INTO XX_COUNTRY VALUES(1001,'CHINA',5000);
/
INSERT INTO XX_COUNTRY VALUES(1002,'SRILANKA',5000);
/
INSERT INTO XX_COUNTRY VALUES(1003,'BANGLADESH',5000);
/
COMMIT;
/
INSERT INTO XX_STATE VALUES(100,'HYDERABAD',1000);
/
INSERT INTO XX_STATE VALUES(101,'BEIJING',1001);
/
INSERT INTO XX_STATE VALUES(102,'COLOMBO',1002);
/
INSERT INTO XX_STATE VALUES(103,'DHAKA',1003);
/
INSERT INTO XX_STATE VALUES(104,'AMARAVATHI','');
/
INSERT INTO XX_STATE VALUES(105,'KARNATAKA','');
/
COMMIT;
/
SELECT * FROM XX_CONTINENT;
/
SELECT * FROM XX_COUNTRY;
/
SELECT * FROM XX_STATE;
/
DELETE FROM XX_CONTINENT;
/
DELETE FROM XX_COUNTRY;
/
TRUNCATE TABLE XX_CONTINENT;
/
TRUNCATE TABLE XX_COUNTRY;
/
DROP TABLE XX_STATE;
/
DROP TABLE XX_COUNTRY;
/
DROP TABLE XX_CONTINENT;
/
CREATE TABLE XX_CONTINENT
(
CON_ID NUMBER PRIMARY KEY,
CON_NAME VARCHAR2(20)
);
/
CREATE TABLE XX_COUNTRY
(
COUNTRY_ID NUMBER PRIMARY KEY,
COUNTRY_NAME VARCHAR2(20),
CONT_ID NUMBER, CONSTRAINT COUNTRY_FK FOREIGN KEY(CONT_ID) REFERENCES CONTINENT(CON_ID) ON DELETE CASCADE
);
/
CREATE TABLE XX_STATE
(
STATE_ID NUMBER PRIMARY KEY,
STATE_NAME VARCHAR2(20),
COUNTRY_ID NUMBER,
CONSTRAINT STATE_FK FOREIGN KEY(COUNTRY_ID) REFERENCES COUNTRY(COUNTRY_ID) ON DELETE CASCADE
);
/
INSERT INTO XX_CONTINENT VALUES(5000,'ASIA');
/
COMMIT;
/
INSERT INTO XX_COUNTRY VALUES(1000,'INDIA',5000);
/
INSERT INTO XX_COUNTRY VALUES(1001,'CHINA',5000);
/
INSERT INTO XX_COUNTRY VALUES(1002,'SRILANKA',5000);
/
INSERT INTO XX_COUNTRY VALUES(1003,'BANGLADESH',5000);
/
COMMIT;
/
INSERT INTO XX_STATE VALUES(100,'HYDERABAD',1000);
/
INSERT INTO XX_STATE VALUES(101,'BEIJING',1001);
/
INSERT INTO XX_STATE VALUES(102,'COLOMBO',1002);
/
INSERT INTO XX_STATE VALUES(103,'DHAKA',1003);
/
INSERT INTO XX_STATE VALUES(104,'AMARAVATHI','');
/
INSERT INTO XX_STATE VALUES(105,'KARNATAKA','');
/
COMMIT;
/
SELECT * FROM XX_CONTINENT;
/
SELECT * FROM XX_COUNTRY;
/
SELECT * FROM XX_STATE;
/
DELETE FROM XX_COUNTRY;
--CHECK THE DATA IN COUNTRY AND STATE TABLE(Once the data is deleted n parent table corresponding child records will be deleted automatically)
ROLLBACK;
/
DELETE FROM XX_CONTINENT;
--CONTINENT TABLE IS DELETED CORRESPONDING COUNTRY AND STATE DATA WILL BE DELETED automatically
ROLLBACK;
/
TRUNCATE TABLE XX_CONTINENT CASCADE;
--CONTINENT TABLE IS TRUNCATED CORRESPONDING COUNTRY AND STATE DATA WILL BE DELETED ALONG WITH NULLS ALSO.
/
Create the parent table:
CREATE TABLE XX_COURSE_PARENT
(
CNO NUMBER PRIMARY KEY,
CNAME VARCHAR2(30)
);
/
CREATE XX_TABLE STU_CHLD
(
SNO NUMBER PRIMARY KEY,
SNAME VARCHAR2(20),CNO NUMBER REFERENCES COURSE_PARENT(CNO) ON DELETE CASCADE);
/
INSERT INTO XX_COURSE_PARENT (1,'CSE');
INSERT INTO XX_COURSE_PARENT (2,'EEE');
INSERT INTO XX_COURSE_PARENT (3,'ECE');
/
INSERT INTO XX_STU_CHLD (100,'MOHAN',1);
INSERT INTO XX_STU_CHLD (101,'MADAN',2);
INSERT INTO XX_STU_CHLD (102,'VINOD',3);
INSERT INTO XX_STU_CHLD (103,'RAJA',1);
INSERT INTO XX_STU_CHLD (104,'RANI',2);
/
DELETE FROM XX_COURSE_PARENT WHERE CNO=1;
/
SELECT * FROM XX_COURSE_PARENT;
/
SELECT * FROM XX_STU_CHLD;
/
CREATE TABLE XX_SCHOOL1
(
SNO NUMBER PRIMARY KEY,
SNAME VARCHAR2(20),
MARKS);
/
Insert Row parent table:
Insert into XX_school1 values(101,'Arun',90);
Insert into XX_school1 values(102,'Kiran',92);
Insert into XX_school1 values(103,'Amit',45);
Creating the child table:
Create table XX_library1(sno number(3), CONSTRAINT LIBRARY1_SNO FOREIGN KEY(sno) REFERENCES school1(sno)on delete cascade,Book_name varchar2(10));
Insert Rows child table:
Insert into XX_library1 values(102,'java');
Insert into XX_library1 values(103,'c++');
Insert into XX_library1 values(103,'oracle');
Insert into XX_library1 values(108,'dotnet'); //error
Insert into XX_library1 values(Null,'DBA'); //valid
Delete from XX_school1 where sno = 101; //valid
1 row deleted
Delete from XX_school1 where sno = 102; //valid
1 row deleted
One row will be deleting from parent table.
One row will be deleting from child table automatically.
/
2)ON DELETE SET NULL:
*************************
-->WHENEVER WE ARE DELETING A RECORD FROM PARENT TABLE THEN THAT ASSOCIATED CHILD RECORDS ARE SET TO NULL IN CHILD TABLE AUTOMATICALLY.
EX:
****
STEP1:
******
CREATE TABLE XX_DEPT3
(
DEPTNO NUMBER PRIMARY KEY,
DNAME VARCHAR2(10)
);
STEP2:
*******
INSERT INTO XX_DEPT3 VALUES(10,'ORACLE');
INSERT INTO XX_DEPT3 VALUES(20,'JAVA');
STEP3:
******
CREATE TABLE XX_EMP3
(
EID NUMBER PRIMARY KEY,
ENAME VARCHAR2(10),
DEPTNO INT REFERENCES XX_DEPT3(DEPTNO) ON DELETE SET NULL
);
STEP4:
*******
INSERT INTO XX_EMP3 VALUES(1021,'SAI',10);
INSERT INTO XX_EMP3 VALUES(1022,'JONES',10);
INSERT INTO XX_EMP3 VALUES(1023,'MILLER',20);
TESTING:
********
DELETE FROM XX_DEPT3 WHERE DEPTNO=10;----ALLOWED
SYNTAX FOR TABLE LEVEL:
========================
CREATE TABLE <TN>(<COL1><DT>[SIZE],<COL2><DT>[SIZE],.............................,
FOREIGN KEY(<COL1>,<COL2>,........) REFERENCES <PARENT TABLE NAME>(<COL1>,<COL2>,...................);
/
HOW TO CREATE A USER DEFINE CONSTRAINT KEY NAME TO CONSTRAINTS:
==============================================================
PRE-DEFINE CONSTRAINT NAME:
============================
- WHENEVER WE ARE APPLYING CONSTRAINT ON A PARTICULAR COLUMN THEN DB SERVER(SYSTEM) INTERNALLY GENERATE AN UNIQUE ID NUMBER (OR) AN UNIQUE CONSTRAINT KEY NAME AUTOMATICALLY FOR IDENTIFYING A CONSTRANT.
EX:
SQL> CREATE TABLE TEST8(SNO NUMBER PRIMARY KEY,NAME VARCHAR2(10));
TESTING:
---------------
SQL> INSERT INTO TEST8 VALUES(1,'A');---ALLOWED
SQL> INSERT INTO TEST8 VALUES(1,'B');---NOT ALLOWED
ERROR:
ORA-00001: unique constraint (SCOTT.SYS_C005475) violated
USER DEFINE CONSTRAINT NAME:
=============================
- IN PLACE OF PRE DEFINE CONSTRAINT NAME WE CAN ALSO CREATE A USER DEFINED CONSTRAINT KEY NAME (OR) CONSTRAINT ID FOR IDENTIFYING A CONSTRAINT.
SYNTAX:
--------------
<COLUMN NAME> <DT>[SIZE] CONSTRAINT <USER DEFINED CONSTRAINT NAME> <CONSTRAINT TYPE>
EX:
SQL> CREATE TABLE TEST10(SNO NUMBER CONSTRAINT PK_SNO PRIMARY KEY,NAME VARCHAR2(10) CONSTRAINT UQ_NAME UNIQUE);
TESTING:
SQL> INSERT INTO TEST10 VALUES(1,'A');
SQL> INSERT INTO TEST10 VALUES(1,'B');
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.PK_SNO) violated
SQL> INSERT INTO TEST10 VALUES(2,'A');
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.UQ_NAME) violated
/
DATA DICTIONARIES (OR) READ ONLY TABLES:
=======================================
- WHENEVER WE ARE INSTALLING ORACLE S/W INTERNALLY ORACLE SERVER IS CREATING SOME PRE-DEFINE TABLES ARE CALLED AS "DATA DICTIONARIES".THESE
DATA DICTIONARIES ARE USED TO STORE THE INFORMATION ABOUT DB OBJECTS SUCH AS TABLES,INDEXES,VIEWS,SYNONYMS,..............etc.
- THESE DATA DICTIONARIES ARE SUPPORTING "SELECT" AND "DESC" COMMANDS ONLY.SO THAT DATA DICTIONARIES ARE ALSO CALLED AS "READ ONLY TABLES" IN ORACLE DB.
- IF WE WANT TO VIEW ALL DATA DICTIONARIES IN ORACLE DB THEN WE FOLLOW THE FOLLOWING SYNTAX IS,
SYNTAX:
--------------
SQL> SELECT * FROM DICT;
NOTE1:
======
IF WE WANT TO VIEW ALL CONSTRAINTS INFROMATION OF A PARTICULAR TABLE THEN WE USE "USER_CONSTRAINTS" DATA DICTIONARY.
EX:
SQL> DESC USER_CONSTRAINTS;
SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE FROM USER_CONSTRAINTS
WHERE TABLE_NAME='TEST10';
CONSTRAINT_NAME CONSTRAINT_TYPE
---------------------------------- -------------------------------
PK_SNO P
UQ_NAME U
NOTE2:
======
- IF WE WANT TO VIEW CONSTRAINT NAME ALONG WITH COLUMN NAME OF A PARTICULAR TABLE THEN WE USE " USER_CONS_COLUMNS " DATADICTIONARY.
EX:
SQL> DESC USER_CONS_COLUMNS;
SQL> SELECT CONSTRAINT_NAME,COLUMN_NAME FROM USER_CONS_COLUMNS
WHERE TABLE_NAME='TEST10';
CONSTRAINT_NAME COLUMN_NAME
---------------------------------- ----------------------------
UQ_NAME NAME
PK_SNO SNO
NOTE3:
======
TO VIEW A LOGICAL CONDITION OF CHECK CONSTRAINT THEN WE NEED TO CALL "SEARCH_CONDITION" COLUMN FROM "USER_CONSTRAINTS" DATA DICTIONARY.
EX:
SQL> CREATE TABLE TEST11(SNO INT,SAL NUMBER(10) CHECK(SAL>5000));
EX:
SQL> DESC USER_CONSTRAINTS;
SQL> SELECT SEARCH_CONDITION FROM USER_CONSTRAINTS
WHERE TABLE_NAME='TEST11';
SEARCH_CONDITION
------------------------------------
SAL>5000
NOTE4:
======
TO VIEW ALL COLUMNS INFORMATION OF A PARTICULAR TABLE THEN WE USE "USER_TAB_COLUMNS" DATADICTIONARY.
EX:
SQL> DESC USER_TAB_COLUMNS;
SQL> SELECT COLUMN_NAME FROM USER_TAB_COLUMNS
WHERE TABLE_NAME='EMP';
HOW TO FIND NO.OF ROWS IN A TABLE:
=================================
SQL> SELECT COUNT(*) FROM EMP;
COUNT(*)
----------------
14
HOW TO FIND NO.OF COLUMNS IN A TABLE:
====================================
SQL> SELECT COUNT(*) FROM USER_TAB_COLUMNS WHERE TABLE_NAME='EMP';
COUNT(*)
------------------
8
/
SELECT COUNT(*) FROM ALL_TAB_COLUMNS
WHERE TABLE_NAME LIKE 'AP_INVOICES_ALL';
/
COUNT(*)
------------------
324
HOW TO ADD CONSTRAINTS TO AN EXISTING TABLE:
============================================
SYNTAX:
=======
ALTER TABLE <TN> ADD CONSTRAINT <CONSTRAINT KEY NAME> <CONSTRAINT TYPE>
(<COLUMN NAME>);
EX:
***
CREATE TABLE TEST12(EID NUMBER,ENAME VARCHAR2(10),SAL NUMBER(10));
i) ADDING PRIMARY KEY:
=====================
ALTER TABLE TEST12 ADD CONSTRAINT PK_EID PRIMARY KEY(EID);
ii) ADDING UNIQUE,CHECK CONSTRAINT:
===================================
ALTER TABLE TEST12 ADD CONSTRAINT UQ_ENAME UNIQUE(ENAME);
ALTER TABLE TEST12 ADD CONSTRAINT CHK_SAL CHECK(SAL=10000);
iii) ADDING "NOT NULL" CONSTRAINT:
************************************
SYNTAX:
*******
ALTER TABLE <TN> MODIFY <COLUMN NAME> CONSTRAINT <CONSTRAINT KEY NAME>
NOT NULL;
EX:
****
ALTER TABLE TEST12 MODIFY ENAME CONSTRAINT NN_ENAME NOT NULL;
iv) ADDING FOREIGN KEY CONSTRAINT:
***********************************
SYNTAX:
*******
ALTER TABLE <TN> ADD CONSTRAINT <CONSTRAINT KEY NAME>
FOREIGN KEY(<COMMON COLUMN OF CHILD TABLE>) REFERENCES
<PARENT TABLE>(<COMMON COLUMN OF PARENT TABLE>) ON DELETE CASCADE /
ON DELETE SET NULL;
EX:
*****
CREATE TABLE TEST13(DNAME VARCHAR2(10),EID VARCHAR2(20));
Table created.
EX:
*****
ALTER TABLE TEST13 ADD CONSTRAINT FK_EID FOREIGN KEY(EID)
REFERENCES TEST12(EID) ON DELETE CASCADE;
HOW TO DROP CONSTRAINT FROM AN EXISTING TABLE:
**********************************************
SYNTAX:
********
ALTER TABLE <TN> DROP CONSTRAINT <CONSTRAINT KEY NAME>;
i) DROPPING PRIMARY KEY:
************************
METHOD1:
********
ALTER TABLE TEST13 DROP CONSTRAINT FK_EID;-------FIRST
ALTER TABLE TEST12 DROP CONSTRAINT PK_EID;--------LATER
METHOD2:
********
-->WHEN WE DROP PRIMARY KEY ALONG WITH FOREIGN KEY CONSTRAINT FROM PARENT AND CHILD TBALES THEN WE USE "CASCADE" STATEMENT.
EX:
****
ALTER TABLE TEST12 DROP CONSTRAINT PK_EID CASCADE;
ii) DROPPING UNIQUE,CHECK,NOT NULL CONSTRANT:
=============================================
ALTER TABLE TEST12 DROP CONSTRAINT UQ_ENAME;
ALTER TABLE TEST12 DROP CONSTRAINT CHK_SAL;
ALTER TABLE TEST12 DROP CONSTRAINT NN_ENAME;
HOW TO RENAME CONSTRAINT NAME:
******************************
SYNTAX:
*********
ALTER TABLE <TN> RENAME CONSTRAINT < OLD CONSTRAINT NAME> TO <NEW CONSTRAINT NAME>;
EX:
****
CREATE TABLE TEST14(SNO INT PRIMARY KEY);
SELECT CONSTRAINT_NAME FROM USER_CONS_COLUMNS
WHERE TABLE_NAME='TEST14';
CONSTRAINT_NAME
------------------------------
SYS_C005489
ALTER TABLE TEST14 RENAME CONSTRAINT SYS_C005489 TO SNO_PK;
SELECT CONSTRAINT_NAME FROM USER_CONS_COLUMNS
WHERE TABLE_NAME='TEST14';
CONSTRAINT_NAME
------------------------------
SNO_PK
HOW TO DISABLE / ENABLE CONSTRAINT:
*********************************
--> BY DEFAULT CONSTRAINTS ARE ENABLE MODE.IF WE WANT TO DISABLE CONSTRAINT TEMP. THEN WE USE "DISABLE" KEYWORD.IT MEANS THAT CONSTRAINT IS EXISTING IN DB BUT NOT WORK TILL IT MAKE AS "ENABLE".
--> WHENEVER WE WANT TO COPY HUGE AMOUNT OF DATA FROM ONE TABLE TO ANOTHER TABLE THERE WE USE "DISABLE" KEYWORD.
SYNTAX:
*******
ALTER TABLE <TN> DISABLE / ENABLE CONSTRAINT <CONSTRAINT KEY NAME>;
EX:
***
CREATE TABLE TEST15(ENAME VARCHAR2(10),SAL NUMBER(10)
CHECK(SAL=5000));
INSERT INTO TEST15 VALUES('SAI',5000);----ALLOWED
INSERT INTO TEST15 VALUES('JONES',3000);-----NOT ALLOWED
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C005492) violated
ALTER TABLE TEST15 DISABLE CONSTRAINT SYS_C005492;
INSERT INTO TEST15 VALUES('JONES',3000);----ALLOWED
EX:
***
ALTER TABLE TEST15 ENABLE CONSTRAINT SYS_C005492;
ERROR at line 1:
ORA-02293: cannot validate (SCOTT.SYS_C005492) - check constraint violated
--> TO OVERCOME THE ABOVE PROBLEM THEN WE USE "NOVALIDATE" KEYWORD AT THE TIME OF ENABLE CONSTRAINT.ONCE WE USE "NOVALIDATE" KEYWORDTHEN CONSTRAINT IS ENABLE WITH "NOVALIDATE" AND ORACLE SERVER WILL NOT CHECK EXISTING DATA IN TABLE BUT CHECKING NEW DATA WHILE INSERTING TIME.
EX:
****
ALTER TABLE TEST15 ENABLE NOVALIDATE CONSTRAINT SYS_C005492;
Table altered.
TESTING:
********
INSERT INTO TEST15 VALUES('SCOTT',6000);---NOT ALLOWED
ERROR at line 1:
ORA-02290: check constraint (SCOTT.SYS_C005492) violated
INSERT INTO TEST15 VALUES('SCOTT',5000);------ALLOWED
How to view tables reference by a given table?
SELECT table_name
FROM ALL_CONSTRAINTS
WHERE constraint_type = 'R'
AND r_constraint_name IN
(SELECT constraint_name from all_constraints
WHERE table_name = 'DEPT1'
AND constraint_type IN ('U','P'));
No comments:
Post a Comment