PL/SQL -> SQL -> SQL Queries

SQL Queries for Practice

Go Back

SQL Advanced Tables

Constraints:

SQL> cl scr 
 
SQL> CREATE TABLE SampleNN01 
  2  ( 
  3   SampID NUMBER(2) 
  4   CONSTRAINT SampleNN01_SampID_NN NOT NULL, 
  5   SampName VARCHAR2(10) 
  6   CONSTRAINT SampleNN01_SampName_NN NOT NULL, 
  7   SampDate DATE 
  8  ); 
 
Table created. 
 
SQL> DESC SampleNN01 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 SAMPID                                    NOT NULL NUMBER(2) 
 SAMPNAME                                  NOT NULL VARCHAR2(10) 
 SAMPDATE                                           DATE 
 
SQL> INSERT INTO SampleNN01 
  2  VALUES(1, 'SAMPLE01', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SampleNN01 
  2  VALUES(NULL, 'SAMPLE01', SYSDATE); 
VALUES(NULL, 'SAMPLE01', SYSDATE) 
       * 
ERROR at line 2: 
ORA-01400: cannot insert NULL into ("SCOTT"."SAMPLENN01"."SAMPID")  
 
 
SQL> INSERT INTO SampleNN01 
  2  VALUES(2, NULL, SYSDATE); 
VALUES(2, NULL, SYSDATE) 
          * 
ERROR at line 2: 
ORA-01400: cannot insert NULL into ("SCOTT"."SAMPLENN01"."SAMPNAME")  
 
 
SQL> INSERT INTO SampleNN01 
  2  VALUES(2, 'SAMPLE02', NULL); 
 
1 row created. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE SampleNN02 
  2  ( 
  3   SampID NUMBER(2), 
  4   SampName VARCHAR2(10) 
  5   CONSTRAINT SampleNN02_SampName_NN NOT NULL, 
  6   SampDate DATE, 
 
 7   CONSTRAINT SampleNN02_SampID_NN NOT NULL(SampID) 
  8* ) 
SQL> / 
 CONSTRAINT SampleNN02_SampID_NN NOT NULL(SampID) 
                                 * 
ERROR at line 7: 
ORA-00904: : invalid identifier  
 
 
SQL> cl scr 
 
SQL> CREATE TABLE SampUNQ01 
  2  ( 
  3   SampID NUMBER(2) 
  4   CONSTRAINT SampUNQ01_SampID_UNQ UNIQUE, 
  5   SampName VARCHAR2(10), 
  6   SampDate DATE 
  7  ); 
 
Table created. 
 
SQL> INSERT INTO SampUNQ01 
  2  VALUES(1, 'SAMPLE01', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SampUNQ01 
  2  VALUES(1, 'SAMPLE02', SYSDATE); 
INSERT INTO SampUNQ01 
* 
ERROR at line 1: 
ORA-00001: unique constraint (SCOTT.SAMPUNQ01_SAMPID_UNQ) violated  
 
 
SQL> INSERT INTO SampUNQ01 
  2  VALUES(NULL, 'SAMPLE02', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SampUNQ01 
  2  VALUES(NULL, 'SAMPLE03', SYSDATE); 
 
1 row created. 
 
SQL> cl scr 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE SampUNQ02 
  2  ( 
  3   SampID NUMBER(2), 
  4   SampName VARCHAR2(10), 
  5   SampDate DATE, 
  6   CONSTRAINT SampUNQ02_SampID_UNQ UNIQUE(SampID) 
 
 7* ) 
SQL> / 
 
Table created. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE SampUNQ03 
  2  ( 
  3   SampID NUMBER(2) 
  4   CONSTRAINT SampUNQ03_SampID_UNQ UNIQUE, 
  5   SampName VARCHAR2(10) 
  6   CONSTRAINT SampUNQ03_SampName_UNQ UNIQUE, 
  7   SampDate DATE 
  8* ) 
SQL> / 
 
Table created. 
 
SQL> INSERT INTO SampUNQ03 
  2  VALUES(1, 'SAMPLE01', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SampUNQ03 
  2  VALUES(2, 'SAMPLE01', SYSDATE); 
INSERT INTO SampUNQ03 
* 
ERROR at line 1: 
ORA-00001: unique constraint (SCOTT.SAMPUNQ03_SAMPNAME_UNQ) violated  
 
 
SQL> INSERT INTO SampUNQ03 
  2  VALUES(1, 'SAMPLE02', SYSDATE); 
INSERT INTO SampUNQ03 
* 
ERROR at line 1: 
ORA-00001: unique constraint (SCOTT.SAMPUNQ03_SAMPID_UNQ) violated  
 
 
SQL> INSERT INTO SampUNQ03 
  2  VALUES(2, 'SAMPLE02', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SampUNQ03 
  2  VALUES(NULL, 'SAMPLE03', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SampUNQ03 
  2  VALUES(NULL, 'SAMPLE04', SYSDATE); 
 
1 row created. 
 

SQL> INSERT INTO SampUNQ03 
  2  VALUES(3, NULL, SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SampUNQ03 
  2  VALUES(4, NULL, SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SampUNQ03 
  2  VALUES(NULL, NULL, SYSDATE); 
 
1 row created. 
 
SQL> cl scr 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE SampUNQ04 
  2  ( 
  3   SampID NUMBER(2), 
  4   SampName VARCHAR2(10), 
  5   SampDate DATE, 
  6   CONSTRAINT SampUNQ04_SampID_UNQ UNIQUE(SampID), 
  7   CONSTRAINT SampUNQ04_SampName_UNQ UNIQUE(SampName) 
  8* ) 
SQL> / 
 
Table created. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE SampUNQ05 
  2  ( 
  3   SampID NUMBER(2), 
  4   SampName VARCHAR2(10), 
  5   SampDate DATE, 
  6   CONSTRAINT SampUNQ05_SampIDName_UNQ  
  7   UNIQUE(SampID, SampName) 
  8* ) 
SQL> / 
 
Table created. 
 
SQL> INSERT INTO SampUNQ05 
  2  VALUES(1, 'SAMPLE01', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SampUNQ05 
  2  VALUES(1, 'SAMPLE02', SYSDATE); 
 

1 row created. 
 
SQL> INSERT INTO SampUNQ05 
  2  VALUES(2, 'SAMPLE02', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SampUNQ05 
  2  VALUES(NULL, 'SAMPLE03', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SampUNQ05 
  2  VALUES(NULL, 'SAMPLE04', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SampUNQ05 
  2  VALUES(NULL, 'SAMPLE04', SYSDATE); 
INSERT INTO SampUNQ05 
* 
ERROR at line 1: 
ORA-00001: unique constraint (SCOTT.SAMPUNQ05_SAMPIDNAME_UNQ) violated  
 
 
SQL> INSERT INTO SampUNQ05 
  2  VALUES(NULL, NULL, SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SampUNQ05 
  2  VALUES(NULL, NULL, SYSDATE); 
 
1 row created. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE SampUNQ06 
  2  ( 
  3   SampID NUMBER(2) 
  4   CONSTRAINT SampUNQ06_SampID_UNQ UNIQUE, 
  5   SampName VARCHAR2(10), 
  6   SampDate DATE, 
  7   CONSTRAINT SampUNQ06_SampIDName_UNQ 
  8   UNIQUE(SampID, SampName) 
  9* ) 
SQL> / 
 
Table created. 
 
SQL> SPOOL OFF 
SQL> cl scr 
 
 

SQL> CREATE TABLE SamplePK01 
  2  ( 
  3   SampID NUMBER(2) 
  4   CONSTRAINT SamplePK01_SampID_PK PRIMARY KEY, 
  5   SampName VARCHAR2(10), 
  6   SampDate DATE 
  7  ); 
 
Table created. 
 
SQL> DESC SamplePK01 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 SAMPID                                    NOT NULL NUMBER(2) 
 SAMPNAME                                           VARCHAR2(10) 
 SAMPDATE                                           DATE 
 
SQL> INSERT INTO SamplePK01 
  2  VALUES(1, 'SAMPLE01', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SamplePK01 
  2  VALUES(1, 'SAMPLE02', SYSDATE); 
INSERT INTO SamplePK01 
* 
ERROR at line 1: 
ORA-00001: unique constraint (SCOTT.SAMPLEPK01_SAMPID_PK) violated  
 
 
SQL> INSERT INTO SamplePK01 
  2  VALUES(NULL, 'SAMPLE02', SYSDATE); 
VALUES(NULL, 'SAMPLE02', SYSDATE) 
       * 
ERROR at line 2: 
ORA-01400: cannot insert NULL into ("SCOTT"."SAMPLEPK01"."SAMPID")  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE SamplePK02 
  2  ( 
  3   SampID NUMBER(2), 
  4   SampName VARCHAR2(10), 
  5   SampDate DATE, 
  6   CONSTRAINT SamplePK02_SampID_PK  
  7   PRIMARY KEY(SampID) 
  8* ) 
SQL> / 
 
Table created. 
 
SQL> ED 
Wrote file afiedt.buf 
 

  1  CREATE TABLE SamplePK03 
  2  ( 
  3   SampID NUMBER(2), 
  4   SampName VARCHAR2(10) 
  5   CONSTRAINT SamplePK03_SampName_PK PRIMARY KEY, 
  6   SampDate DATE, 
  7   CONSTRAINT SamplePK03_SampID_PK  
  8   PRIMARY KEY(SampID) 
  9* ) 
SQL> / 
 PRIMARY KEY(SampID) 
 * 
ERROR at line 8: 
ORA-02260: table can have only one primary key  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE SamplePK03 
  2  ( 
  3   SampID NUMBER(2), 
  4   SampName VARCHAR2(10) 
  5   CONSTRAINT SamplePK03_SampName_NN NOT NULL 
  6   CONSTRAINT SamplePK03_SampName_UNQ UNIQUE, 
  7   SampDate DATE, 
  8   CONSTRAINT SamplePK03_SampID_PK  
  9   PRIMARY KEY(SampID) 
 10* ) 
SQL> / 
 
Table created. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE SamplePK04 
  2  ( 
  3   SampID NUMBER(2), 
  4   SampName VARCHAR2(10) 
  5   CONSTRAINT SamplePK04_SampName_NN NOT NULL, 
  6   SampDate DATE, 
  7   CONSTRAINT SamplePK04_SampID_PK  
  8   PRIMARY KEY(SampID), 
  9   CONSTRAINT SamplePK04_SampName_UNQ  
 10   UNIQUE(SampName) 
 11* ) 
SQL> / 
 
Table created. 
 
SQL> CREATE TABLE SamplePK05 
  2  ( 
  3   SampID NUMBER(2), 
 
 4   SampName VARCHAR2(10), 
  5   SampDate DATE, 
  6   CONSTRAINT SamplePK05_SampIDName_PK 
  7   PRIMARY KEY(SampID, SammName) 
  8  ); 
 PRIMARY KEY(SampID, SammName) 
                     * 
ERROR at line 7: 
ORA-00904: "SAMMNAME": invalid identifier  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE SamplePK05 
  2  ( 
  3   SampID NUMBER(2), 
  4   SampName VARCHAR2(10), 
  5   SampDate DATE, 
  6   CONSTRAINT SamplePK05_SampIDName_PK 
  7   PRIMARY KEY(SampID, SampName) 
  8* ) 
SQL> / 
 
Table created. 
 
SQL> INSERT INTo SamplePK05 
  2  VALUES(1, 'SAMPLE01', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTo SamplePK05 
  2  VALUES(1, 'SAMPLE02', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTo SamplePK05 
  2  VALUES(2, 'SAMPLE02', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTo SamplePK05 
  2  VALUES(NULL, 'SAMPLE03', SYSDATE); 
VALUES(NULL, 'SAMPLE03', SYSDATE) 
       * 
ERROR at line 2: 
ORA-01400: cannot insert NULL into ("SCOTT"."SAMPLEPK05"."SAMPID")  
 
 
SQL> INSERT INTo SamplePK05 
  2  VALUES(3, NULL, SYSDATE); 
VALUES(3, NULL, SYSDATE) 
          * 
ERROR at line 2: 
ORA-01400: cannot insert NULL into ("SCOTT"."SAMPLEPK05"."SAMPNAME")  
 

 
SQL> cl scr 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE SamplePK06 
  2  ( 
  3   SampID NUMBER(2) 
  4   CONSTRAINT SamplePK06_SampID_UNQ UNIQUE, 
  5   SampName VARCHAR2(10), 
  6   SampDate DATE, 
  7   CONSTRAINT SamplePK06_SampID_PK 
  8   PRIMARY KEY(SampID) 
  9* ) 
SQL> / 
 CONSTRAINT SamplePK06_SampID_UNQ UNIQUE, 
 * 
ERROR at line 4: 
ORA-02261: such unique or primary key already exists in the table  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE SamplePK06 
  2  ( 
  3   SampID NUMBER(2) 
  4   CONSTRAINT SamplePK06_SampID_UNQ UNIQUE, 
  5   SampName VARCHAR2(10), 
  6   SampDate DATE, 
  7   CONSTRAINT SamplePK06_SampIDName_PK 
  8   PRIMARY KEY(SampID, SampName) 
  9* ) 
SQL> / 
 
Table created. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE SamplePK07 
  2  ( 
  3   SampID NUMBER(2) 
  4   CONSTRAINT SamplePK07_SampID_UNQ UNIQUE, 
  5   SampName VARCHAR2(10) 
  6   CONSTRAINT SamplePK07_SampName_UNQ UNIQUE, 
  7   SampDate DATE, 
  8   CONSTRAINT SamplePK07_SampIDName_PK 
  9   PRIMARY KEY(SampID, SampName) 
 10* ) 
SQL> / 
 
Table created. 
 

SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE SamplePK08 
  2  ( 
  3   SampID NUMBER(2), 
  4   SampName VARCHAR2(10), 
  5   SampDate DATE, 
  6   CONSTRAINT SamplePK08_SampIDName_PK 
  7   PRIMARY KEY(SampID, SampName), 
  8   CONSTRAINT SamplePK08_SampIDName_UNQ  
  9   UNIQUE(SampID, SampName) 
 10* ) 
SQL> / 
 UNIQUE(SampID, SampName) 
 * 
ERROR at line 9: 
ORA-02261: such unique or primary key already exists in the table  
 
 
SQL> SPOOL OFF 
SQL> SELECT * FROM TAB; 
 
TNAME                          TABTYPE  CLUSTERID                                
------------------------------ ------- ----------                                
SAMPLEPK01                     TABLE                                             
SAMPLEPK02                     TABLE                                             
SAMPLEPK03                     TABLE                                             
SAMPLEPK04                     TABLE                                             
SAMPLEPK05                     TABLE                                             
SAMPLEPK06                     TABLE                                             
SAMPLEPK07                     TABLE                                             
EMPLOYEES                      VIEW                                              
EMPINFO                        VIEW                                              
EMPGRADES                      VIEW                                              
EMPMANAGERS                    VIEW                                              
 
TNAME                          TABTYPE  CLUSTERID                                
------------------------------ ------- ----------                                
EMPACCOUNTS                    VIEW                                              
CUMSUM                         VIEW                                              
ORGDESIGNATIONS                VIEW                                              
DEPT                           TABLE                                             
EMP                            TABLE                                             
BONUS                          TABLE                                             
SALGRADE                       TABLE                                             
DUMMY                          TABLE                                             
CUSTOMER                       TABLE                                             
ORD                            TABLE                                             
ITEM                           TABLE                                             
 
TNAME                          TABTYPE  CLUSTERID                                
------------------------------ ------- ----------                                
PRODUCT                        TABLE                                             
 

PRICE                          TABLE                                             
SALES                          VIEW                                              
 
25 rows selected. 
 
SQL> SPOOL OFF 
SQL> cl scr 
 
SQL> CREATE TABLE MyDept 
  2  ( 
  3   Deptno NUMBER(2) 
  4   CONSTRAINT MyDept_Deptno_PK PRIMARY KEY, 
  5   Dname VARCHAR2(16) 
  6   CONSTRAINT MyDept_Dname_NN NOT NULL, 
  7   Loc VARCHAR2(16) 
  8   CONSTRAINT MyDept_Loc_NN NOT NULL 
  9  ); 
 
Table created. 
 
SQL> CREATE TABLE MyEmp 
  2  ( 
  3   Empno NUMBER(4) 
  4   CONSTRAINT MyEmp_Empno_PK PRIMARY KEY, 
  5   Ename VARCHAR2(10) 
  6   CONSTRAINT MyEmp_Ename_NN NOT NULL, 
  7   Job VARCHAR2(14) 
  8   CONSTRAINT MyEmp_Job_NN NOT NULL, 
  9   HireDate DATE 
 10   CONSTRAINT MyEmp_HireDate_NN NOT NULL, 
 11   MGR NUMBER(4) 
 12   CONSTRAINT MyEmp_MGR_FK 
 13   REFERENCES MyEmp(Empno) 
 14   ON DELETE SET NULL, 
 15   Sal NUMBER(7, 2) 
 16   CONSTRAINT MyEmp_Sal_NN NOT NULL, 
 17   Comm NUMBER(7, 2), 
 18   DeptID NUMBER(2) 
 19   CONSTRAINT MyEmp_DeptID_NN NOT NULL 
 20   CONSTRAINT MyEmp_DeptID_FK 
 21   REFERENCES MyDept(Deptno) 
 22   ON DELETE CASCADE 
 23  ); 
 
Table created. 
 
SQL> DESC Dept 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 DEPTNO                                    NOT NULL NUMBER(2) 
 DNAME                                              VARCHAR2(14) 
 LOC                                                VARCHAR2(13) 
 
SQL> DESC MyDept 
 Name                                      Null?    Type 
 
----------------------------------------- -------- ---------------------------- 
 DEPTNO                                    NOT NULL NUMBER(2) 
 DNAME                                     NOT NULL VARCHAR2(16) 
 LOC                                       NOT NULL VARCHAR2(16) 
 
SQL> DESC Emp 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 EMPNO                                     NOT NULL NUMBER(4) 
 ENAME                                              VARCHAR2(10) 
 JOB                                                VARCHAR2(9) 
 MGR                                                NUMBER(4) 
 HIREDATE                                           DATE 
 SAL                                                NUMBER(7,2) 
 COMM                                               NUMBER(7,2) 
 DEPTNO                                    NOT NULL NUMBER(2) 
 
SQL> DESC MyEmp 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 EMPNO                                     NOT NULL NUMBER(4) 
 ENAME                                     NOT NULL VARCHAR2(10) 
 JOB                                       NOT NULL VARCHAR2(14) 
 HIREDATE                                  NOT NULL DATE 
 MGR                                                NUMBER(4) 
 SAL                                       NOT NULL NUMBER(7,2) 
 COMM                                               NUMBER(7,2) 
 DEPTID                                    NOT NULL NUMBER(2) 
 
SQL> INSERT INTO MyEmp 
  2  VALUES(1230, 'SAMPLE01', 'MANAGER', SYSDATE, 
  3   
SQL> cl scr 
 
SQL> CREATE TABLE SamplePK01 
  2  ( 
  3   SampID NUMBER(2) 
  4   CONSTRAINT SamplePK01_SampID_PK PRIMARY KEY, 
  5   SampName VARCHAR2(10), 
  6   SampDate DATE 
  7  ); 
 
Table created. 
 
SQL> CREATE TABLE SampleFK01 
  2  ( 
  3   SampID NUMBER(2) 
  4   CONSTRAINT SampleFK01_SampID_PK PRIMARY KEY, 
  5   SampName VARCHAR2(10), 
  6   SampDate DATE, 
  7   SampIDFK NUMBER(2) 
  8   CONSTRAINT SampleFK01_SampIDFK_FK 
  9   REFERENCES SamplePK01(SampID) 
 10  ); 
 
 

Table created. 
 
SQL> INSERT INTO SampleFK01 
  2  VALUES(20, 'SAMPLE20', SYSDATE, NULL); 
 
1 row created. 
 
SQL> INSERT INTO SamplePK01 
  2  VALUES(10, 'SAMPLE10', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SamplePK01 
  2  VALUES(11, 'SAMPLE11', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SamplePK01 
  2  VALUES(12, 'SAMPLE12', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SamplePK01 
  2  VALUES(13, 'SAMPLE13', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SampleFK01 
  2  VALUES(21, 'SAMPLE21', SYSDATE, 10); 
 
1 row created. 
 
SQL> INSERT INTO SampleFK01 
  2  VALUES(22, 'SAMPLE22', SYSDATE, 15); 
INSERT INTO SampleFK01 
* 
ERROR at line 1: 
ORA-02291: integrity constraint (SCOTT.SAMPLEFK01_SAMPIDFK_FK) violated -  
parent key not found  
 
 
SQL> INSERT INTO SampleFK01 
  2  VALUES(22, 'SAMPLE22', SYSDATE, 10); 
 
1 row created. 
 
SQL> INSERT INTO SampleFK01 
  2  VALUES(23, 'SAMPLE23', SYSDATE, 12); 
 
1 row created. 
 
SQL> SELECT * FROM SamplePK01; 
 
    SAMPID SAMPNAME   SAMPDATE                                                   
---------- ---------- ---------                                                  
 
       10 SAMPLE10   23-JUN-10                                                  
        11 SAMPLE11   23-JUN-10                                                  
        12 SAMPLE12   23-JUN-10                                                  
        13 SAMPLE13   23-JUN-10                                                  
 
SQL> SELECT * FROM SampleFK01; 
 
    SAMPID SAMPNAME   SAMPDATE    SAMPIDFK                                       
---------- ---------- --------- ----------                                       
        20 SAMPLE20   23-JUN-10                                                  
        21 SAMPLE21   23-JUN-10         10                                       
        22 SAMPLE22   23-JUN-10         10                                       
        23 SAMPLE23   23-JUN-10         12                                       
 
SQL> DELETE FROM SamplePK01 
  2  WHERE SampID = 13; 
 
1 row deleted. 
 
SQL> DELETE FROM SamplePK01 
  2  WHERE SampID = 12; 
DELETE FROM SamplePK01 
* 
ERROR at line 1: 
ORA-02292: integrity constraint (SCOTT.SAMPLEFK01_SAMPIDFK_FK) violated - child  
record found  
 
 
SQL> DELETE FROM SampleFK01 
  2  WHERE SampID = 23; 
 
1 row deleted. 
 
SQL> cl scr 
 
SQL> CREATE TABLE SamplePK02 
  2  ( 
  3   SampID NUMBER(2) 
  4   CONSTRAINT SamplePK02_SampID_PK PRIMARY KEY, 
  5   SampName VARCHAR2(10), 
  6   SampDate DATE 
  7  ); 
 
Table created. 
 
SQL> CREATE TABLE SampleFK02 
  2  ( 
  3   SampID NUMBER(2) 
  4   CONSTRAINT SampleFK02_SampID_PK PRIMARY KEY, 
  5   SampName VARCHAR2(10), 
  6   SampDate DATE, 
  7   SampIDFK NUMBER(2) 
  8   CONSTRAINT SampleFK02_SampIDFK_FK 
  9   REFERENCES SamplePK02(SampID) 
 10   ON DELETE SET NULL 
 
11  ); 
 
Table created. 
 
SQL> INSERT INTO SamplePK02 
  2  VALUES(1, 'SAMPLE01', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SamplePK02 
  2  VALUES(2, 'SAMPLE02', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SamplePK02 
  2  VALUES(3, 'SAMPLE03', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SampleFK02 
  2  VALUES(10, 'SAMPLE10', SYSDATE, 1); 
 
1 row created. 
 
SQL> INSERT INTO SampleFK02 
  2  VALUES(11, 'SAMPLE11', SYSDATE, 2); 
 
1 row created. 
 
SQL> INSERT INTO SampleFK02 
  2  VALUES(12, 'SAMPLE12', SYSDATE, 2); 
 
1 row created. 
 
SQL> INSERT INTO SampleFK02 
  2  VALUES(13, 'SAMPLE13', SYSDATE, 2); 
 
1 row created. 
 
SQL> SELECT * FROM SamplePK02; 
 
    SAMPID SAMPNAME   SAMPDATE                                                   
---------- ---------- ---------                                                  
         1 SAMPLE01   23-JUN-10                                                  
         2 SAMPLE02   23-JUN-10                                                  
         3 SAMPLE03   23-JUN-10                                                  
 
SQL> SELECT * FROM SampleFK02; 
 
    SAMPID SAMPNAME   SAMPDATE    SAMPIDFK                                       
---------- ---------- --------- ----------                                       
        10 SAMPLE10   23-JUN-10          1                                       
        11 SAMPLE11   23-JUN-10          2                                       
        12 SAMPLE12   23-JUN-10          2                                       
        13 SAMPLE13   23-JUN-10          2                                       
 

SQL> DELETE FROM SamplePK02 
  2  WHERE SampID = 2; 
 
1 row deleted. 
 
SQL> SELECT * FROM SampleFK02; 
 
    SAMPID SAMPNAME   SAMPDATE    SAMPIDFK                                       
---------- ---------- --------- ----------                                       
        10 SAMPLE10   23-JUN-10          1                                       
        11 SAMPLE11   23-JUN-10                                                  
        12 SAMPLE12   23-JUN-10                                                  
        13 SAMPLE13   23-JUN-10                                                  
 
SQL> cl scr 
 
SQL> CREATE TABLE SamplePK03 
  2  ( 
  3   SampID NUMBER(2) 
  4   CONSTRAINT SamplePK03_SampID_PK PRIMARY KEY, 
  5   SampName VARCHAR2(10), 
  6   SampDate DATE 
  7  ); 
 
Table created. 
 
SQL> CREATE TABLE SampleFK03_1 
  2  ( 
  3   SampID NUMBER(2) 
  4   CONSTRAINT SampleFK03_1_SampID_PK PRIMARY KEY, 
  5   SampName VARCHAR2(10), 
  6   SampDate DATE, 
  7   SampIDFK NUMBER(2) 
  8   CONSTRAINT SampleFK03_1_SampIDFK_FK 
  9   REFERENCES SamplePK03(SampID) 
 10   ON DELETE CASCADE 
 11  ); 
 
Table created. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE SampleFK03_2 
  2  ( 
  3   SampID NUMBER(2) 
  4   CONSTRAINT SampleFK03_2_SampID_PK PRIMARY KEY, 
  5   SampName VARCHAR2(10), 
  6   SampDate DATE, 
  7   SampIDFK NUMBER(2) 
  8   CONSTRAINT SampleFK03_2_SampIDFK_FK 
  9   REFERENCES SamplePK03(SampID) 
 10   ON DELETE SET NULL 
 11* ) 
 

SQL> / 
 
Table created. 
 
SQL> INSERT INTO SamplePK03 
  2  VALUES(10, 'SAMPLE10', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SamplePK03 
  2  VALUES(11, 'SAMPLE11', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SamplePK03 
  2  VALUES(12, 'SAMPLE12', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SamplePK03 
  2  VALUES(13, 'SAMPLE13', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SamplePK03 
  2  VALUES(14, 'SAMPLE14', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SamplePK03 
  2  VALUES(15, 'SAMPLE15', SYSDATE); 
 
1 row created. 
 
SQL> INSERT INTO SamplePK03 
  2  VALUES(16, 'SAMPLE16', SYSDATE); 
 
1 row created. 
 
SQL> COMMIT; 
 
Commit complete. 
 
SQL> INSERT INTO SampleFK03_1 
  2  VALUES(20, 'SAMPLE20', SYSDATE, 10); 
 
1 row created. 
 
SQL> INSERT INTO SampleFK03_1 
  2  VALUES(21, 'SAMPLE21', SYSDATE, 10); 
 
1 row created. 
 
SQL> INSERT INTO SampleFK03_1 
  2  VALUES(22, 'SAMPLE22', SYSDATE, 10); 
 

1 row created. 
 
SQL> INSERT INTO SampleFK03_1 
  2  VALUES(23, 'SAMPLE23', SYSDATE, 12); 
 
1 row created. 
 
SQL> INSERT INTO SampleFK03_1 
  2  VALUES(24, 'SAMPLE24', SYSDATE, 12); 
 
1 row created. 
 
SQL> INSERT INTO SampleFK03_1 
  2  VALUES(25, 'SAMPLE25', SYSDATE, 13); 
 
1 row created. 
 
SQL> INSERT INTO SampleFK03_1 
  2  VALUES(26, 'SAMPLE26', SYSDATE, 13); 
 
1 row created. 
 
SQL> INSERT INTO SampleFK03_1 
  2  VALUES(27, 'SAMPLE27', SYSDATE, 13); 
 
1 row created. 
 
SQL> INSERT INTO SampleFK03_1 
  2  VALUES(28, 'SAMPLE28', SYSDATE, 15); 
 
1 row created. 
 
SQL> INSERT INTO SampleFK03_2 
  2  VALUES(30, 'SAMPLE30', SYSDATE, 10); 
 
1 row created. 
 
SQL> INSERT INTO SampleFK03_2 
  2  VALUES(31, 'SAMPLE31', SYSDATE, 10); 
 
1 row created. 
 
SQL> INSERT INTO SampleFK03_2 
  2  VALUES(32, 'SAMPLE32', SYSDATE, 11); 
 
1 row created. 
 
SQL> INSERT INTO SampleFK03_2 
  2  VALUES(33, 'SAMPLE33', SYSDATE, 11); 
 
1 row created. 
 
SQL> INSERT INTO SampleFK03_2 
  2  VALUES(34, 'SAMPLE34', SYSDATE, 14); 
 

1 row created. 
 
SQL> INSERT INTO SampleFK03_2 
  2  VALUES(35, 'SAMPLE35', SYSDATE, 14); 
 
1 row created. 
 
SQL> INSERT INTO SampleFK03_2 
  2  VALUES(36, 'SAMPLE36', SYSDATE, 14); 
 
1 row created. 
 
SQL> INSERT INTO SampleFK03_2 
  2  VALUES(37, 'SAMPLE37', SYSDATE, 15); 
 
1 row created. 
 
SQL> COMMIT; 
 
Commit complete. 
 
SQL> cl scr 
 
SQL> SELECT * FROM SamplePK03; 
 
    SAMPID SAMPNAME   SAMPDATE                                                   
---------- ---------- ---------                                                  
        10 SAMPLE10   23-JUN-10                                                  
        11 SAMPLE11   23-JUN-10                                                  
        12 SAMPLE12   23-JUN-10                                                  
        13 SAMPLE13   23-JUN-10                                                  
        14 SAMPLE14   23-JUN-10                                                  
        15 SAMPLE15   23-JUN-10                                                  
        16 SAMPLE16   23-JUN-10                                                  
 
7 rows selected. 
 
SQL> SELECT * FROM SampleFK03_1; 
 
    SAMPID SAMPNAME   SAMPDATE    SAMPIDFK                                       
---------- ---------- --------- ----------                                       
        20 SAMPLE20   23-JUN-10         10                                       
        21 SAMPLE21   23-JUN-10         10                                       
        22 SAMPLE22   23-JUN-10         10                                       
        23 SAMPLE23   23-JUN-10         12                                       
        24 SAMPLE24   23-JUN-10         12                                       
        25 SAMPLE25   23-JUN-10         13                                       
        26 SAMPLE26   23-JUN-10         13                                       
        27 SAMPLE27   23-JUN-10         13                                       
        28 SAMPLE28   23-JUN-10         15                                       
 
9 rows selected. 
 
SQL> SELECT * FROM SampleFK03_2; 
 

    SAMPID SAMPNAME   SAMPDATE    SAMPIDFK                                       
---------- ---------- --------- ----------                                       
        30 SAMPLE30   23-JUN-10         10                                       
        31 SAMPLE31   23-JUN-10         10                                       
        32 SAMPLE32   23-JUN-10         11                                       
        33 SAMPLE33   23-JUN-10         11                                       
        34 SAMPLE34   23-JUN-10         14                                       
        35 SAMPLE35   23-JUN-10         14                                       
        36 SAMPLE36   23-JUN-10         14                                       
        37 SAMPLE37   23-JUN-10         15                                       
 
8 rows selected. 
 
SQL> DELETE FROM SamplePK03 
  2  WHERE SampID = 16; 
 
1 row deleted. 
 
SQL> DELETE FROM SamplePK03 
  2  WHERE SampID = 12; 
 
1 row deleted. 
 
SQL> SELECT * FROM SamplePK03; 
 
    SAMPID SAMPNAME   SAMPDATE                                                   
---------- ---------- ---------                                                  
        10 SAMPLE10   23-JUN-10                                                  
        11 SAMPLE11   23-JUN-10                                                  
        13 SAMPLE13   23-JUN-10                                                  
        14 SAMPLE14   23-JUN-10                                                  
        15 SAMPLE15   23-JUN-10                                                  
 
SQL> SELECT * FROM SampleFK03_1; 
 
    SAMPID SAMPNAME   SAMPDATE    SAMPIDFK                                       
---------- ---------- --------- ----------                                       
        20 SAMPLE20   23-JUN-10         10                                       
        21 SAMPLE21   23-JUN-10         10                                       
        22 SAMPLE22   23-JUN-10         10                                       
        25 SAMPLE25   23-JUN-10         13                                       
        26 SAMPLE26   23-JUN-10         13                                       
        27 SAMPLE27   23-JUN-10         13                                       
        28 SAMPLE28   23-JUN-10         15                                       
 
7 rows selected. 
 
SQL> DELETE FROM SamplePK03 
  2  WHERE SampID = 14; 
 
1 row deleted. 
 
SQL> SELECT * FROM SampleFK03_2; 
 
 
   SAMPID SAMPNAME   SAMPDATE    SAMPIDFK                                       
---------- ---------- --------- ----------                                       
        30 SAMPLE30   23-JUN-10         10                                       
        31 SAMPLE31   23-JUN-10         10                                       
        32 SAMPLE32   23-JUN-10         11                                       
        33 SAMPLE33   23-JUN-10         11                                       
        34 SAMPLE34   23-JUN-10                                                  
        35 SAMPLE35   23-JUN-10                                                  
        36 SAMPLE36   23-JUN-10                                                  
        37 SAMPLE37   23-JUN-10         15                                       
 
8 rows selected. 
 
SQL> DELETE FROM SamplePK03 
  2  WHERE SampID = 10; 
 
1 row deleted. 
 
SQL> SELECT * FROM SampleFK03_1; 
 
    SAMPID SAMPNAME   SAMPDATE    SAMPIDFK                                       
---------- ---------- --------- ----------                                       
        25 SAMPLE25   23-JUN-10         13                                       
        26 SAMPLE26   23-JUN-10         13                                       
        27 SAMPLE27   23-JUN-10         13                                       
        28 SAMPLE28   23-JUN-10         15                                       
 
SQL> SELECT * FROM SampleFK03_2; 
 
    SAMPID SAMPNAME   SAMPDATE    SAMPIDFK                                       
---------- ---------- --------- ----------                                       
        30 SAMPLE30   23-JUN-10                                                  
        31 SAMPLE31   23-JUN-10                                                  
        32 SAMPLE32   23-JUN-10         11                                       
        33 SAMPLE33   23-JUN-10         11                                       
        34 SAMPLE34   23-JUN-10                                                  
        35 SAMPLE35   23-JUN-10                                                  
        36 SAMPLE36   23-JUN-10                                                  
        37 SAMPLE37   23-JUN-10         15                                       
 
8 rows selected. 
 
SQL> cl scr 
 
SQL> CREATE TABLE SamplePK04Self 
  2  ( 
  3   SampID NUMBER(2) 
  4   CONSTRAINT SamplePK04Self_SampIDPK PRIMARY KEY, 
  5   SampName VARCHAR2(10), 
  6   SampDate DATE, 
  7   SampIDFK NUMBER(2) 
  8   CONSTRAINT SamplePK04Self_SampIDFK 
  9   REFERENCES SamplePK04Self(SampID) 
 10   ON DELETE SET NULL 
 11  ); 
 

Table created. 
 
SQL> INSERT INTO SamplePK04Self 
  2  VALUES(1, 'SAMPLE01', SYSDATE, NULL); 
 
1 row created. 
 
SQL> INSERT INTO SamplePK04Self 
  2  VALUES(2, 'SAMPLE02', SYSDATE, 1); 
 
1 row created. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE SamplePK04Self1 
  2  ( 
  3   SampID NUMBER(2) 
  4   CONSTRAINT SamplePK04Self1_SampIDPK PRIMARY KEY, 
  5   SampName VARCHAR2(10), 
  6   SampDate DATE, 
  7   SampIDFK NUMBER(2) 
  8   CONSTRAINT SamplePK04Self1_SampIDNN NOT NULL 
  9   CONSTRAINT SamplePK04Self1_SampIDFK 
 10   REFERENCES SamplePK04Self1(SampID) 
 11   ON DELETE CASCADE 
 12* ) 
SQL> / 
 
Table created. 
 
SQL> INSERT INTO SamplePK04Self1 
  2  VALUES(1, 'SAMPLE01', SYSDATE, NULL); 
VALUES(1, 'SAMPLE01', SYSDATE, NULL) 
                               * 
ERROR at line 2: 
ORA-01400: cannot insert NULL into ("SCOTT"."SAMPLEPK04SELF1"."SAMPIDFK")  
 
 
SQL> INSERT INTO SamplePK04Self1 
  2  VALUES(1, 'SAMPLE01', SYSDATE, 1); 
 
1 row created. 
 
SQL> INSERT INTO SamplePK04Self1 
  2  VALUES(2, 'SAMPLE02', SYSDATE, 1); 
 
1 row created. 
 
SQL> cl scr 
 
SQL> SELECT * FROM TAB; 
 
TNAME                          TABTYPE  CLUSTERID                                
 

------------------------------ ------- ----------                                
DEPT                           TABLE                                             
EMP                            TABLE                                             
BONUS                          TABLE                                             
SALGRADE                       TABLE                                             
DUMMY                          TABLE                                             
CUSTOMER                       TABLE                                             
ORD                            TABLE                                             
ITEM                           TABLE                                             
PRODUCT                        TABLE                                             
PRICE                          TABLE                                             
EMPLOYEES                      VIEW                                              
 
TNAME                          TABTYPE  CLUSTERID                                
------------------------------ ------- ----------                                
EMPINFO                        VIEW                                              
EMPGRADES                      VIEW                                              
EMPMANAGERS                    VIEW                                              
EMPACCOUNTS                    VIEW                                              
CUMSUM                         VIEW                                              
ORGDESIGNATIONS                VIEW                                              
SALES                          VIEW                                              
MYDEPT                         TABLE                                             
MYEMP                          TABLE                                             
SAMPLEPK01                     TABLE                                             
SAMPLEFK01                     TABLE                                             
 
TNAME                          TABTYPE  CLUSTERID                                
------------------------------ ------- ----------                                
SAMPLEPK02                     TABLE                                             
SAMPLEFK02                     TABLE                                             
SAMPLEPK03                     TABLE                                             
SAMPLEFK03_1                   TABLE                                             
SAMPLEFK03_2                   TABLE                                             
SAMPLEPK04SELF                 TABLE                                             
SAMPLEPK04SELF1                TABLE                                             
 
29 rows selected. 
 
SQL> cl scr 
 
SQL> CREATE TABLE SamplePK04 
  2  ( 
  3   SampID NUMBER(2) 
  4   CONSTRAINT SamplePK04_SampID_PK PRIMARY KEY, 
  5   SampName VARCHAR2(10), 
  6   SampDate DATE 
  7  ); 
 
Table created. 
 
SQL> CREATE TABLE SampleFK04 
  2  ( 
  3   SampID NUMBER(2) 
  4   CONSTRAINT SampleFK04_SampID_FK 
 
 5   REFERENCES SamplePK04(SampID), 
  6   SampName VARCHAR2(10), 
  7   SampDate DATE, 
  8   CONSTRAINT SampleFK04_SampID_PK 
  9   PRIMARY KEY(SampID) 
 10  ); 
 
Table created. 
 
SQL> CREATE TABLE SampleFK04_1 
  2  ( 
  3   SampID NUMBER(2) 
  4   CONSTRAINT SampleFK04_1_SampID_FK 
  5   REFERENCES SampleFK04(SampID), 
  6   SampName VARCHAR2(10) 
  7  ); 
 
Table created. 
 
SQL> cl scr 
 
SQL> CREATE TABLE SampleCKPK01 
  2  ( 
  3   SampID NUMBER(2) 
  4  ,SampName VARCHAR2(10) 
  5  ,SampDate DATE 
  6  ,CONSTRAINT SampleCKPK01_SampIDName_CKPK 
  7   PRIMARY KEY(SampID, SampName) 
  8  ); 
 
Table created. 
 
SQL> CREATE TABLE SampleCKFK01 
  2  ( 
  3   SampID NUMBER(2), 
  4   SampName VARCHAR2(10), 
  5   SampDate DATE, 
  6   CONSTRAINT SampleCKFK01_SampIDNameCKFK 
  7   FOREIGN KEY(SampID, SampName) 
  8   REFERENCES SampleCKPK01(SampID, SampName) 
  9  ); 
 
Table created. 
 
SQL> cl scr 
 
SQL> CREATE TABLE SamplePKUNQ 
  2  ( 
  3   SampIDPK NUMBER(2) 
  4   CONSTRAINT SamplePKUNQ_SampIDPK_PK PRIMARY KEY, 
  5   SampIDUNQ1 NUMBER(2) 
  6   CONSTRAINT SamplePKUNQ_SampIDUNQ_UNQ UNIQUE, 
  7   SampDate DATE 
  8  ); 
 
 

Table created. 
 
SQL> CREATE TABLE SampleFK 
  2  ( 
  3   SampIDFKPK NUMBER(2) 
  4   CONSTRAINT SampleFK_SampIDFKPK_FK 
  5   REFERENCES SamplePKUNQ(SampIDPK), 
  6   SampIDFKUNQ NUMBER(2) 
  7   CONSTRAINT SampleFK_SampIDFKUNQ_FK 
  8   REFERENCES SamplePKUNQ(SampIDUNQ1) 
  9  ); 
 
Table created. 
 
SQL> SPOOL OFF 
SQL> cl scr 
 
SQL> CREATE TABLE MyDept 
  2  ( 
  3   Deptno NUMBER(2) 
  4   CONSTRAINT MyDept_Deptno_PK PRIMARY KEY 
  5   CONSTRAINT MyDept_Deptno_CHK01 
  6   CHECK(Deptno IN(10, 20, 30, 40, 50, 60, 70, 80, 90)), 
  7   DName VARCHAR2(16) 
  8   CONSTRAINT MyDept_DName_NN  NOT NULL 
  9   DEFAULT 'NOT GIVEN' 
 10   CONSTRAINT MyDept_DName_UNQ UNIQUE 
 11   CONSTRAINT MyDept_DName_CHK01 
 12   CHECK(DName = UPPER(DName)), 
 13   Loc VARCHAR2(14) 
 14   CONSTRAINT MyDept_Loc_NN NOT NULL 
 15   DEFAULT 'NOT GIVEN' 
 16   CONSTRAINT MyDept_Loc_CHK01 
 17   CHECK(Loc IN('NEW YORK', 'BOSTON', 'CHICAGO', 'DALLAS')) 
 18  ); 
 DEFAULT 'NOT GIVEN' 
 * 
ERROR at line 9: 
ORA-00907: missing right parenthesis  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE MyDept 
  2  ( 
  3   Deptno NUMBER(2) 
  4   CONSTRAINT MyDept_Deptno_PK PRIMARY KEY 
  5   CONSTRAINT MyDept_Deptno_CHK01 
  6   CHECK(Deptno IN(10, 20, 30, 40, 50, 60, 70, 80, 90)), 
  7   DName VARCHAR2(16) 
  8   CONSTRAINT MyDept_DName_NN  NOT NULL 
  9   DEFAULT 'NOT GIVEN') 
 10   CONSTRAINT MyDept_DName_UNQ UNIQUE 
 11   CONSTRAINT MyDept_DName_CHK01 
 
12   CHECK(DName = UPPER(DName)), 
 13   Loc VARCHAR2(14) 
 14   CONSTRAINT MyDept_Loc_NN NOT NULL 
 15   DEFAULT 'NOT GIVEN' 
 16   CONSTRAINT MyDept_Loc_CHK01 
 17   CHECK(Loc IN('NEW YORK', 'BOSTON', 'CHICAGO', 'DALLAS')) 
 18* ) 
SQL> / 
 DEFAULT 'NOT GIVEN') 
 * 
ERROR at line 9: 
ORA-00907: missing right parenthesis  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE MyDept 
  2  ( 
  3   Deptno NUMBER(2) 
  4   CONSTRAINT MyDept_Deptno_PK PRIMARY KEY 
  5   CONSTRAINT MyDept_Deptno_CHK01 
  6   CHECK(Deptno IN(10, 20, 30, 40, 50, 60, 70, 80, 90)), 
  7   DName VARCHAR2(16) 
  8   CONSTRAINT MyDept_DName_NN  NOT NULL 
  9   (DEFAULT 'NOT GIVEN') 
 10   CONSTRAINT MyDept_DName_UNQ UNIQUE 
 11   CONSTRAINT MyDept_DName_CHK01 
 12   CHECK(DName = UPPER(DName)), 
 13   Loc VARCHAR2(14) 
 14   CONSTRAINT MyDept_Loc_NN NOT NULL 
 15   DEFAULT 'NOT GIVEN' 
 16   CONSTRAINT MyDept_Loc_CHK01 
 17   CHECK(Loc IN('NEW YORK', 'BOSTON', 'CHICAGO', 'DALLAS')) 
 18* ) 
SQL> / 
 (DEFAULT 'NOT GIVEN') 
 * 
ERROR at line 9: 
ORA-00907: missing right parenthesis  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE MyDept 
  2  ( 
  3   Deptno NUMBER(2) 
  4   CONSTRAINT MyDept_Deptno_PK PRIMARY KEY 
  5   CONSTRAINT MyDept_Deptno_CHK01 
  6   CHECK(Deptno IN(10, 20, 30, 40, 50, 60, 70, 80, 90)), 
  7   DName VARCHAR2(16) 
  8   DEFAULT 'NOT GIVEN' 
  9   CONSTRAINT MyDept_DName_NN  NOT NULL 
 10   CONSTRAINT MyDept_DName_UNQ UNIQUE 
 
11   CONSTRAINT MyDept_DName_CHK01 
 12   CHECK(DName = UPPER(DName)), 
 13   Loc VARCHAR2(14) 
 14   CONSTRAINT MyDept_Loc_NN NOT NULL 
 15   DEFAULT 'NOT GIVEN' 
 16   CONSTRAINT MyDept_Loc_CHK01 
 17   CHECK(Loc IN('NEW YORK', 'BOSTON', 'CHICAGO', 'DALLAS')) 
 18* ) 
SQL> / 
 DEFAULT 'NOT GIVEN' 
 * 
ERROR at line 15: 
ORA-00907: missing right parenthesis  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE MyDept 
  2  ( 
  3   Deptno NUMBER(2) 
  4   CONSTRAINT MyDept_Deptno_PK PRIMARY KEY 
  5   CONSTRAINT MyDept_Deptno_CHK01 
  6   CHECK(Deptno IN(10, 20, 30, 40, 50, 60, 70, 80, 90)), 
  7   DName VARCHAR2(16) 
  8   DEFAULT 'Not Given' 
  9   CONSTRAINT MyDept_DName_NN  NOT NULL 
 10   CONSTRAINT MyDept_DName_UNQ UNIQUE 
 11   CONSTRAINT MyDept_DName_CHK01 
 12   CHECK(DName = UPPER(DName)), 
 13   Loc VARCHAR2(14) 
 14   DEFAULT 'NOT GIVEN' 
 15   CONSTRAINT MyDept_Loc_NN NOT NULL 
 16   CONSTRAINT MyDept_Loc_CHK01 
 17   CHECK(Loc IN('NEW YORK', 'BOSTON', 'CHICAGO', 'DALLAS')) 
 18* ) 
SQL> / 
 
Table created. 
 
SQL> INSERT INTO MyDept 
  2  VALUES(1, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO MyDept 
* 
ERROR at line 1: 
ORA-02290: check constraint (SCOTT.MYDEPT_DEPTNO_CHK01) violated  
 
 
SQL> INSERT INTO MyDept 
  2  VALUES(10, 'ACCOUNTING', 'NEW YORK'); 
 
1 row created. 
 
SQL> INSERT INTO MyDept 
  2  VALUES(11, 'ACCOUNTING', 'NEW YORK'); 
 

INSERT INTO MyDept 
* 
ERROR at line 1: 
ORA-02290: check constraint (SCOTT.MYDEPT_DEPTNO_CHK01) violated  
 
 
SQL> INSERT INTO MyDept 
  2  VALUES(20, 'ACCOUNTING', 'NEW YORK'); 
INSERT INTO MyDept 
* 
ERROR at line 1: 
ORA-00001: unique constraint (SCOTT.MYDEPT_DNAME_UNQ) violated  
 
 
SQL> INSERT INTO MyDept 
  2  VALUES(20, 'RESEARCH', 'BOSTON'); 
 
1 row created. 
 
SQL> INSERT INTO MyDept 
  2  VALUES(30, 'SALES', 'Chicago'); 
INSERT INTO MyDept 
* 
ERROR at line 1: 
ORA-02290: check constraint (SCOTT.MYDEPT_LOC_CHK01) violated  
 
 
SQL> INSERT INTO MyDept 
  2  VALUES(30, 'SALES', 'CHICAGO'); 
 
1 row created. 
 
SQL> INSERT INTO MyDept 
  2  VALUES(40, 'OPERATIONS'); 
INSERT INTO MyDept 
            * 
ERROR at line 1: 
ORA-00947: not enough values  
 
 
SQL> INSERT INTO MyDept(Deptno, Loc) 
  2  VALUES(50, 'BOSTON'); 
INSERT INTO MyDept(Deptno, Loc) 
* 
ERROR at line 1: 
ORA-02290: check constraint (SCOTT.MYDEPT_DNAME_CHK01) violated  
 
 
SQL> INSERT INTO MyDept(Deptno, DName) 
  2  VALUES(40, 'OPEARTIONS'); 
INSERT INTO MyDept(Deptno, DName) 
* 
ERROR at line 1: 
ORA-02290: check constraint (SCOTT.MYDEPT_LOC_CHK01) violated  
 
 

SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO MyDept(Deptno, DName) 
  2* VALUES(40, 'OPEARTIONS') 
SQL> DROP TABLE MyDept; 
 
Table dropped. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE MyDept 
  2  ( 
  3   Deptno NUMBER(2) 
  4   CONSTRAINT MyDept_Deptno_PK PRIMARY KEY 
  5   CONSTRAINT MyDept_Deptno_CHK01 
  6   CHECK(Deptno IN(10, 20, 30, 40, 50, 60, 70, 80, 90)), 
  7   DName VARCHAR2(16) 
  8   DEFAULT 'NOT GIVEN' 
  9   CONSTRAINT MyDept_DName_NN  NOT NULL 
 10   CONSTRAINT MyDept_DName_UNQ UNIQUE 
 11   CONSTRAINT MyDept_DName_CHK01 
 12   CHECK(DName = UPPER(DName)), 
 13   Loc VARCHAR2(14) 
 14   DEFAULT 'NOT GIVEN' 
 15   CONSTRAINT MyDept_Loc_NN NOT NULL 
 16   CONSTRAINT MyDept_Loc_CHK01 
 17   CHECK(Loc IN('NEW YORK', 'BOSTON', 'CHICAGO', 'DALLAS', 'NOT GIVEN')) 
 18* ) 
SQL> / 
 
Table created. 
 
SQL> INSERT INTO MyDept(Deptno, DName) 
  2  VALUES(10, 'OPERATIONS'); 
 
1 row created. 
 
SQL> INSERT INTO MyDept(Deptno, Loc) 
  2  VALUES(20, 'BOSTON'); 
 
1 row created. 
 
SQL> SELECT * FROM MyDept; 
 
    DEPTNO DNAME            LOC                                                  
---------- ---------------- --------------                                       
        10 OPERATIONS       NOT GIVEN                                            
        20 NOT GIVEN        BOSTON                                               
 
SQL> INSERT INTO MyDept(Deptno, DName) 
  2  VALUES(30, 'ACCOUNTING'); 
 
 

1 row created. 
 
SQL> SELECT * FROM MyDept; 
 
    DEPTNO DNAME            LOC                                                  
---------- ---------------- --------------                                       
        10 OPERATIONS       NOT GIVEN                                            
        20 NOT GIVEN        BOSTON                                               
        30 ACCOUNTING       NOT GIVEN                                            
 
SQL> INSERT INTO MyDept(Deptno, DName) 
  2   
SQL> INSERT INTO MyDept(Deptno, Loc) 
  2  VALUES(40, 'CHICAGO'); 
INSERT INTO MyDept(Deptno, Loc) 
* 
ERROR at line 1: 
ORA-00001: unique constraint (SCOTT.MYDEPT_DNAME_UNQ) violated  
 
 
SQL> cl scr 
 
SQL> CREATE TABLE MyEmp 
  2  ( 
  3   Empno NUMBER(4) 
  4   CONSTRAINT MyEmp_Empno_PK PRIMARY KEY 
  5   CONSTRAINT MyEmp_Empno_CHK01 
  6   CHECK(Empno >= 1000 AND Empno <= 4000), 
  7   Ename VARCHAR2(10) 
  8   DEFAULT 'NOT GIVEN' 
  9   CONSTRAINT MyEmp_Ename_NN NOT NULL 
 10   CONSTRAINT MyEmp_Ename_CHK01 
 11   CHECK(EName = UPPER(EName)), 
 12   Job VARCHAR2(14) 
 13   DEFAULT 'NOT GIVEN' 
 14   CONSTRAINT MyEmp_Job_NN NOT NULL 
 15   CONSTRAINT MyEmp_Job_CHK01 
 16   CHECK(Job IN('PRESIDENT', 'MANAGER', 'ANALYST', 'SALESMAN', 'CLERK', 'NOT 
GIVEN')), 
 17   MGR NUMBER(4) 
 18   CONSTRAINT MyEmp_MGR_CHK01 
 19   CHECK(MGR >= 1000 AND MGR <= 4000), 
 20   Sal NUMBER(7, 2) 
 21   CONSTRAINT MyEmp_Sal_NN NOT NULL 
 22   CONSTRAINT MyEmp_Sal_CHK01 
 23   CHECK(Sal BETWEEN 1000 AND 90000), 
 24   Comm NUMBER(7, 2), 
 25   HireDate DATE 
 26   DEFAULT SYSDATE 
 27   CONSTRAINT MyEmp_HireDate_NN NOT NULL, 
 28   Deptno NUMBER(2) 
 29   CONSTRAINT MyEmp_Deptno_NN NOT NULL 
 30   CONSTRAINT MyEmp_Deptno_CHK 
 31   CHECK(Deptno IN(10, 20, 30, 40, 50, 60, 70, 80, 90)) 
 32   CONSTRAINT MyEmp_Deptno_FK 
 
33   RFERENCES MyDept(Deptno), 
 34   CONSTRAINT MyEmpSalAndComm_CHK 
 35   CHECK(Sal + NVL(Comm, 0) <= 90000) 
 36  ); 
 CONSTRAINT MyEmp_Deptno_FK 
 * 
ERROR at line 32: 
ORA-02253: constraint specification not allowed here  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE MyEmp 
  2  ( 
  3   Empno NUMBER(4) 
  4   CONSTRAINT MyEmp_Empno_PK PRIMARY KEY 
  5   CONSTRAINT MyEmp_Empno_CHK01 
  6   CHECK(Empno >= 1000 AND Empno <= 4000), 
  7   Ename VARCHAR2(10) 
  8   DEFAULT 'NOT GIVEN' 
  9   CONSTRAINT MyEmp_Ename_NN NOT NULL 
 10   CONSTRAINT MyEmp_Ename_CHK01 
 11   CHECK(EName = UPPER(EName)), 
 12   Job VARCHAR2(14) 
 13   DEFAULT 'NOT GIVEN' 
 14   CONSTRAINT MyEmp_Job_NN NOT NULL 
 15   CONSTRAINT MyEmp_Job_CHK01 
 16   CHECK(Job IN('PRESIDENT', 'MANAGER', 'ANALYST', 'SALESMAN', 'CLERK', 'NOT 
GIVEN')), 
 17   MGR NUMBER(4) 
 18   CONSTRAINT MyEmp_MGR_CHK01 
 19   CHECK(MGR >= 1000 AND MGR <= 4000), 
 20   Sal NUMBER(7, 2) 
 21   CONSTRAINT MyEmp_Sal_NN NOT NULL 
 22   CONSTRAINT MyEmp_Sal_CHK01 
 23   CHECK(Sal BETWEEN 1000 AND 90000), 
 24   Comm NUMBER(7, 2), 
 25   HireDate DATE 
 26   DEFAULT SYSDATE 
 27   CONSTRAINT MyEmp_HireDate_NN NOT NULL, 
 28   Deptno NUMBER(2) 
 29   CONSTRAINT MyEmp_Deptno_NN NOT NULL 
 30   CONSTRAINT MyEmp_Deptno_FK 
 31   RFERENCES MyDept(Deptno) 
 32   CONSTRAINT MyEmp_Deptno_CHK 
 33   CHECK(Deptno IN(10, 20, 30, 40, 50, 60, 70, 80, 90)), 
 34   CONSTRAINT MyEmpSalAndComm_CHK 
 35   CHECK(Sal + NVL(Comm, 0) <= 90000) 
 36* ) 
SQL> / 
 CONSTRAINT MyEmp_Deptno_FK 
 * 
ERROR at line 30: 
ORA-02253: constraint specification not allowed here  
 

 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE MyEmp 
  2  ( 
  3   Empno NUMBER(4) 
  4   CONSTRAINT MyEmp_Empno_PK PRIMARY KEY 
  5   CONSTRAINT MyEmp_Empno_CHK01 
  6   CHECK(Empno >= 1000 AND Empno <= 4000), 
  7   Ename VARCHAR2(10) 
  8   DEFAULT 'NOT GIVEN' 
  9   CONSTRAINT MyEmp_Ename_NN NOT NULL 
 10   CONSTRAINT MyEmp_Ename_CHK01 
 11   CHECK(EName = UPPER(EName)), 
 12   Job VARCHAR2(14) 
 13   DEFAULT 'NOT GIVEN' 
 14   CONSTRAINT MyEmp_Job_NN NOT NULL 
 15   CONSTRAINT MyEmp_Job_CHK01 
 16   CHECK(Job IN('PRESIDENT', 'MANAGER', 'ANALYST', 'SALESMAN', 'CLERK', 'NOT 
GIVEN')), 
 17   MGR NUMBER(4) 
 18   CONSTRAINT MyEmp_MGR_CHK01 
 19   CHECK(MGR >= 1000 AND MGR <= 4000), 
 20   Sal NUMBER(7, 2) 
 21   CONSTRAINT MyEmp_Sal_NN NOT NULL 
 22   CONSTRAINT MyEmp_Sal_CHK01 
 23   CHECK(Sal BETWEEN 1000 AND 90000), 
 24   Comm NUMBER(7, 2), 
 25   HireDate DATE 
 26   DEFAULT SYSDATE 
 27   CONSTRAINT MyEmp_HireDate_NN NOT NULL, 
 28   Deptno NUMBER(2) 
 29   CONSTRAINT MyEmp_Deptno_NN NOT NULL 
 30   CONSTRAINT MyEmp_Deptno_FK 
 31   RFERENCES MyDept(Deptno), 
 32   CONSTRAINT MyEmpSalAndComm_CHK 
 33   CHECK(Sal + NVL(Comm, 0) <= 90000) 
 34* ) 
SQL> / 
 CONSTRAINT MyEmp_Deptno_FK 
 * 
ERROR at line 30: 
ORA-02253: constraint specification not allowed here  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE TABLE MyEmp 
  2  ( 
  3   Empno NUMBER(4) 
  4   CONSTRAINT MyEmp_Empno_PK PRIMARY KEY 
  5   CONSTRAINT MyEmp_Empno_CHK01 
 
 6   CHECK(Empno >= 1000 AND Empno <= 4000), 
  7   Ename VARCHAR2(10) 
  8   DEFAULT 'NOT GIVEN' 
  9   CONSTRAINT MyEmp_Ename_NN NOT NULL 
 10   CONSTRAINT MyEmp_Ename_CHK01 
 11   CHECK(EName = UPPER(EName)), 
 12   Job VARCHAR2(14) 
 13   DEFAULT 'NOT GIVEN' 
 14   CONSTRAINT MyEmp_Job_NN NOT NULL 
 15   CONSTRAINT MyEmp_Job_CHK01 
 16   CHECK(Job IN('PRESIDENT', 'MANAGER', 'ANALYST', 'SALESMAN', 'CLERK', 'NOT 
GIVEN')), 
 17   MGR NUMBER(4) 
 18   CONSTRAINT MyEmp_MGR_CHK01 
 19   CHECK(MGR >= 1000 AND MGR <= 4000), 
 20   Sal NUMBER(7, 2) 
 21   CONSTRAINT MyEmp_Sal_NN NOT NULL 
 22   CONSTRAINT MyEmp_Sal_CHK01 
 23   CHECK(Sal BETWEEN 1000 AND 90000), 
 24   Comm NUMBER(7, 2), 
 25   HireDate DATE 
 26   DEFAULT SYSDATE 
 27   CONSTRAINT MyEmp_HireDate_NN NOT NULL, 
 28   Deptno NUMBER(2) 
 29   CONSTRAINT MyEmp_Deptno_NN NOT NULL 
 30   CONSTRAINT MyEmp_Deptno_FK 
 31   REFERENCES MyDept(Deptno) 
 32   CONSTRAINT MyEmp_Deptno_CHK 
 33   CHECK(Deptno IN(10, 20, 30, 40, 50, 60, 70, 80, 90)), 
 34   CONSTRAINT MyEmpSalAndComm_CHK 
 35   CHECK(Sal + NVL(Comm, 0) <= 90000) 
 36* ) 
SQL> / 
 
Table created. 
 
SQL> ALTER TABLE MyEmp 
  2  ADD 
  3  ( 
  4   CONSTRAINT MyEmp_MGR_FK 
  5   FOREIGN KEY(MGR) 
  6   REFERENCES MyEmp(Empno) 
  7   ON DELETE SET NULL 
  8  ); 
 
Table altered. 
 
SQL> cl scr 
 
SQL> CREATE TABLE SampleALT 
  2  ( 
  3   SampID NUMBER(2), 
  4   SampName VARCHAR2(10), 
  5   SampDate DATE 
  6  ); 
 

Table created. 
 
SQL> ALTER TABLE SampleALT 
  2  ADD( 
  3   
SQL> ALTER TABLE SampleALT 
  2  ADD 
  3  ( 
  4   CONSTRAINT SampleALT_SampID_PK 
  5   PRIMARY KEY(SampID), 
  6   CONSTRAINT SampleALT_SampID_CHK 
  7   CHECK(SampID >= 10), 
  8   CONSTRAINT SampleALT_SampName_CHK 
  9   CHECK(SampName = UPPER(SampName)) 
 10  ); 
 
Table altered. 
 
SQL> cl scr 
 
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> COLUMN CONSTRAINT_NAME FORMAT A20 
SQL> COLUMN TABLE_NAME FORMAT A15 
SQL> COLUMN SEARCH_CONDITION FORMAT A25 
SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION 
  2  FROM USER_CONSTRAINTS 
  3  WHERE TABLE_NAME = 'SAMPLEALT'; 
 
CONSTRAINT_NAME      C TABLE_NAME      SEARCH_CONDITION                          
-------------------- - --------------- -------------------------                 
SAMPLEALT_SAMPID_PK  P SAMPLEALT                                                 
SAMPLEALT_SAMPID_CHK C SAMPLEALT       SampID >= 10                              
SAMPLEALT_SAMPNAME_C C SAMPLEALT       SampName = UPPER(SampName                 
HK                                     )                                         
                                                                                 
 
SQL> DESC USER_CONS_COLUMS 
ERROR: 
ORA-04043: object USER_CONS_COLUMS does not exist  
 
 
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 
 
SQL> DESC MYEmp 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 EMPNO                                     NOT NULL NUMBER(4) 
 ENAME                                     NOT NULL VARCHAR2(10) 
 JOB                                       NOT NULL VARCHAR2(14) 
 MGR                                                NUMBER(4) 
 SAL                                       NOT NULL NUMBER(7,2) 
 COMM                                               NUMBER(7,2) 
 HIREDATE                                  NOT NULL DATE 
 DEPTNO                                    NOT NULL NUMBER(2) 
 
SQL> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION 
  2  FROM USER_CONSTRAINTS 
  3  WHERE TABLE_NAME = 'MYEMP'; 
 
CONSTRAINT_NAME      C TABLE_NAME      SEARCH_CONDITION                          
-------------------- - --------------- -------------------------                 
MYEMP_ENAME_NN       C MYEMP           "ENAME" IS NOT NULL                       
MYEMP_JOB_NN         C MYEMP           "JOB" IS NOT NULL                         
MYEMP_SAL_NN         C MYEMP           "SAL" IS NOT NULL                         
MYEMP_HIREDATE_NN    C MYEMP           "HIREDATE" IS NOT NULL                    
MYEMP_DEPTNO_NN      C MYEMP           "DEPTNO" IS NOT NULL                      
MYEMP_EMPNO_CHK01    C MYEMP           Empno >= 1000 AND Empno <                 
                                       = 4000                                    
                                                                                 
MYEMP_ENAME_CHK01    C MYEMP           EName = UPPER(EName)                      
MYEMP_JOB_CHK01      C MYEMP           Job IN('PRESIDENT', 'MANA                 
                                       GER', 'ANALYST', 'SALESMA                 
 
CONSTRAINT_NAME      C TABLE_NAME      SEARCH_CONDITION                          
-------------------- - --------------- -------------------------                 
                                       N', 'CLERK', 'NOT GIVEN')                 
                                                                                 
MYEMP_MGR_CHK01      C MYEMP           MGR >= 1000 AND MGR <= 40                 
                                       00                                        
 
                                                                                
MYEMP_SAL_CHK01      C MYEMP           Sal BETWEEN 1000 AND 9000                 
                                       0                                         
                                                                                 
MYEMP_DEPTNO_CHK     C MYEMP           Deptno IN(10, 20, 30, 40,                 
                                        50, 60, 70, 80, 90)                      
                                                                                 
 
CONSTRAINT_NAME      C TABLE_NAME      SEARCH_CONDITION                          
-------------------- - --------------- -------------------------                 
MYEMPSALANDCOMM_CHK  C MYEMP           Sal + NVL(Comm, 0) <= 900                 
                                       00                                        
                                                                                 
MYEMP_EMPNO_PK       P MYEMP                                                     
MYEMP_DEPTNO_FK      R MYEMP                                                     
MYEMP_MGR_FK         R MYEMP                                                     
 
15 rows selected. 
 
SQL> cl scr 
 
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 
 
SQL> SPOOL OFF   

Go Back