Constraints
The Oracle Server uses constraints to prevent invalid data entry into tables. You can use constraints to do the following:
• Enforce rules on the data in a table whenever a row is inserted, updated, or deleted from that table. The constraint must be satisfied for the operation to succeed. • Prevent the deletion of a table if there are dependencies from other tables • Provide rules for Oracle tools, such as Oracle Developer
Constraints are usually created at the same time as the table. Constraints can be added to a table after its creation and also temporarily disabled.
Constraints can be defined at one of two levels. • Column level constraint
• Table level constraint flevel
The NOT NULL constraint can be specified only at the column level, not at the table level. The following constraint types are valid:
• NOT NULL • UNIQUE • PRIMARY KEY • FOREIGN KEY • CHECK Constraint Guidelines
• Name a constraint or the Oracle server generates a name by using the SYS_Cn format. • Create a constraint either:
– At the same time as the table is created, or – After the table has been created
• Define a constraint at the column or table level. • View a constraint in the data dictionary.
Create DEPARTMENT table
Define constraints at column level
Do not provide constraint name because Oracle will provide constraints name
SQL> CREATE TABLE DEPARTMENT 2 (
3 DEPTNO NUMBER(2) PRIMARY KEY, 4 DNAME VARCHAR2(10) NOT NULL,
5 LOC VARCHAR2(10) DEFAULT 'KARACHI' 6 );
Inserting into DEPARTMENT table
SQL> INSERT INTO DEPARTMENT VALUES (1,'ADMIN','LAHORE'); 1 row created.
SQL> INSERT INTO DEPARTMENT (DEPTNO, DNAME, LOC) VALUES (2,'ACCOUNTS','LAHORE'); 1 row created.
SQL> INSERT INTO DEPARTMENT (DEPTNO, DNAME, LOC) VALUES (3,'IT',DEFAULT); 1 row created.
SQL> INSERT INTO DEPARTMENT (DEPTNO, DNAME) VALUES (4,'AUDIT'); 1 row created.
SQL> DESC DEPARTMENT
Name Null? Type --- --- ---- DEPTNO NOT NULL NUMBER(2) DNAME NOT NULL VARCHAR2(10) LOC VARCHAR2(10) SQL> SELECT * FROM DEPARTMENT;
DEPTNO DNAME LOC
--- --- --- 1 ADMIN LAHORE 2 ACCOUNTS LAHORE 3 IT KARACHI 4 AUDIT KARACHI
Note the errors in the following queries and give the reason of errors
SQL> INSERT INTO DEPARTMENT (DEPTNO, DNAME, LOC) VALUES (4,'PURCHASE','LAHORE'); INSERT INTO DEPARTMENT (DEPTNO, DNAME, LOC) VALUES (4,'PURCHASE','LAHORE')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C0011127) violated
SQL> INSERT INTO DEPARTMENT (DEPTNO, DNAME, LOC) VALUES (NULL,'PURCHASE','LAHORE');
INSERT INTO DEPARTMENT (DEPTNO, DNAME, LOC) VALUES (NULL,'PURCHASE','LAHORE') *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."DEPARTMENT"."DEPTNO")
SQL> INSERT INTO DEPARTMENT (DEPTNO, DNAME, LOC) VALUES (5,NULL,'LAHORE'); INSERT INTO DEPARTMENT (DEPTNO, DNAME, LOC) VALUES (5,NULL,'LAHORE')
* ERROR at line 1:
Use the following data dictionary views for viewing constraints information
SQL> DESC USER_CONSTRAINTS
Name Null? Type --- --- ----
OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) CONSTRAINT_TYPE VARCHAR2(1) TABLE_NAME NOT NULL VARCHAR2(30) SEARCH_CONDITION LONG R_OWNER VARCHAR2(30) R_CONSTRAINT_NAME VARCHAR2(30) DELETE_RULE VARCHAR2(9) STATUS VARCHAR2(8) DEFERRABLE VARCHAR2(14) DEFERRED VARCHAR2(9) VALIDATED VARCHAR2(13) GENERATED VARCHAR2(14) BAD VARCHAR2(3) RELY VARCHAR2(4) LAST_CHANGE DATE INDEX_OWNER VARCHAR2(30) INDEX_NAME VARCHAR2(30) INVALID VARCHAR2(7) VIEW_RELATED VARCHAR2(14) SQL> DESC USER_CONS_COLUMNS
Name Null? Type --- --- ----
OWNER NOT NULL VARCHAR2(30) CONSTRAINT_NAME NOT NULL VARCHAR2(30) TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME VARCHAR2(4000) POSITION NUMBER
View constraints information of DEPARTMENT table by using data dictionary views:
You can view constraints information by using user_constraints but you can not view the column names for which constraints are used.
SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE 2 FROM USER_CONSTRAINTS 3 WHERE TABLE_NAME='DEPARTMENT'; CONSTRAINT_NAME C --- - SYS_C0011126 C SYS_C0011127 P
You have to use user_cons_columns if you want to view column name for which constraints are used but you can not view constraints type by user_cons_columns.
SQL> COL COLUMN_NAME FORMAT A15
SQL> SELECT CONSTRAINT_NAME, COLUMN_NAME, POSITION 2 FROM USER_CONS_COLUMNS
3 WHERE TABLE_NAME='DEPARTMENT';
CONSTRAINT_NAME COLUMN_NAME POSITION --- --- --- SYS_C0011126 DNAME
If you want to view complete constraint information including constraints type and column information then you have to join user_constraints and user_cons_columns.
SQL> SELECT T.CONSTRAINT_NAME, C.COLUMN_NAME, T.CONSTRAINT_TYPE 2 FROM USER_CONSTRAINTS T, USER_CONS_COLUMNS C
3 WHERE C.CONSTRAINT_NAME=T.CONSTRAINT_NAME AND 4 C.TABLE_NAME=T.TABLE_NAME AND 5 T.TABLE_NAME='DEPARTMENT'; CONSTRAINT_NAME COLUMN_NAME C --- --- - SYS_C0011126 DNAME C SYS_C0011127 DEPTNO P
Create EMPLOYEE table
Define constraints at column level
Do not provide constraint name because Oracle will provide constraints name
SQL> CREATE TABLE EMPLOYEE 2 (
3 EMP_ID NUMBER(4) PRIMARY KEY, 4 FIRST_NAME VARCHAR2(10) NOT NULL, 5 LAST_NAME VARCHAR2(10),
6 JOIN_DATE DATE DEFAULT SYSDATE,
7 DESG VARCHAR2(20) DEFAULT 'CLERK',
8 SALARY NUMBER(7,2) CHECK (SALARY BETWEEN 500 AND 99999), 9 NIC VARCHAR2(20) UNIQUE,
10 DEPTNO NUMBER(02) REFERENCES DEPARTMENT (DEPTNO) 11 );
Table created.
SQL> DESC EMPLOYEE
Name Null? Type --- --- ---- EMP_ID NOT NULL NUMBER(4) FIRST_NAME NOT NULL VARCHAR2(10) LAST_NAME VARCHAR2(10) JOIN_DATE DATE DESG VARCHAR2(20) SALARY NUMBER(7,2) NIC VARCHAR2(20) DEPTNO NUMBER(2)
View constraints information of EMPLOYEE table by using data dictionary views: SQL> SELECT T.CONSTRAINT_NAME, C.COLUMN_NAME, T.CONSTRAINT_TYPE
2 FROM USER_CONSTRAINTS T, USER_CONS_COLUMNS C 3 WHERE C.CONSTRAINT_NAME=T.CONSTRAINT_NAME AND 4 C.TABLE_NAME=T.TABLE_NAME AND 5 T.TABLE_NAME='EMPLOYEE'; CONSTRAINT_NAME COLUMN_NAME C --- --- - SYS_C0011128 FIRST_NAME C SYS_C0011129 SALARY C SYS_C0011130 EMP_ID P SYS_C0011131 NIC U
Create EMPLOYEE2 table with user defined constraint name Define constraints at column level
SQL> CREATE TABLE EMPLOYEE2 2 (
3 EMP_ID NUMBER(4) CONSTRAINT EMPLOYEE2_EMP_ID_PK PRIMARY KEY, 4 FIRST_NAME VARCHAR2(10) CONSTRAINT EMPLOYEE2_FIRSTNAME_NN NOT NULL, 5 LAST_NAME VARCHAR2(10),
6 JOIN_DATE DATE DEFAULT SYSDATE,
7 DESG VARCHAR2(20) DEFAULT 'CLERK', 8 SALARY NUMBER(7,2)
9 CONSTRAINT EMPLOYEE2_SALARY_C CHECK (SALARY BETWEEN 500 AND 99999), 10 NIC VARCHAR2(20) CONSTRAINT EMPLOYEE2_NIC_U UNIQUE,
11 DEPTNO NUMBER(02)
12 CONSTRAINT EMPLOYEE2_DEPTNO_FK REFERENCES DEPARTMENT (DEPTNO) 13 );
Table created.
View constraints information of EMPLOYEE2 table by using data dictionary views: SQL> SELECT T.CONSTRAINT_NAME, C.COLUMN_NAME, T.CONSTRAINT_TYPE
2 FROM USER_CONSTRAINTS T, USER_CONS_COLUMNS C 3 WHERE C.CONSTRAINT_NAME=T.CONSTRAINT_NAME AND 4 C.TABLE_NAME=T.TABLE_NAME AND 5 T.TABLE_NAME='EMPLOYEE2'; CONSTRAINT_NAME COLUMN_NAME C --- --- - EMPLOYEE2_FIRSTNAME_NN FIRST_NAME C EMPLOYEE2_SALARY_C SALARY C EMPLOYEE2_EMP_ID_PK EMP_ID P EMPLOYEE2_NIC_U NIC U EMPLOYEE2_DEPTNO_FK DEPTNO R