Friday, August 9, 2019

Constraints in Oracle

SET LINES 100;

DROP TABLE EMPLOYEE;

CREATE TABLE EMPLOYEE
(
   EMPNO    NUMBER,
   ENAME    VARCHAR2 (50),
   GENDER   CHAR (1),
   DOB      DATE,
   DOJ      DATE
);

-- Primary Key
ALTER TABLE EMPLOYEE ADD CONSTRAINT EMP_EMPNO_PK PRIMARY KEY(EMPNO);

INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (NULL,'Rajasekhar','M','10-AUG-1991','16-AUG-2014');
INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1010,'Rajasekhar','M','10-AUG-1991','16-AUG-2014');
INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1010,'Rajasekhar','M','10-AUG-1991','16-AUG-2014');

-- NOT NULL
ALTER TABLE EMPLOYEE MODIFY ( ENAME VARCHAR2 (50) CONSTRAINT EMP_ENAME_NN NOT NULL );

INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1011,'','M','10-SEP-1991','16-MAY-2015');
INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1011,'Siva','M','10-SEP-1991','16-MAY-2015');

-- CHECK
ALTER TABLE EMPLOYEE ADD CONSTRAINT EMP_GENDER_CHK CHECK (GENDER IN ('M','F'));

INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1012,'Jyothi','O','10-APR-1991','16-SEP-2011');
INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1012,'Jyothi','F','10-APR-1991','16-SEP-2011');

-- DEFAULT
ALTER TABLE EMPLOYEE MODIFY DOJ DEFAULT SYSDATE;

INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1013,'Balu','M','11-JUL-1991','');
INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1014,'Sai','M','11-FEB-1991',NULL);
INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB) VALUES (1015,'Hari','M','11-JAN-1991');

COMMIT;

SELECT * FROM EMPLOYEE;

DROP TABLE EMP_ADDRESS;

CREATE TABLE EMP_ADDRESS
(
   EMPNO           NUMBER,
   ADDR_TYPE       VARCHAR2 (10),
   DNO             VARCHAR2 (30),
   ADDRESS_LINE1   VARCHAR2 (100),
   ADDRESS_LINE2   VARCHAR2 (100),
   ADDRESS_LINE3   VARCHAR2 (100),
   CITY            VARCHAR2 (50),
   STATE           VARCHAR2 (50),
   COUNTRY         VARCHAR2 (50),
   PHONE           NUMBER (10),
   ZIP_CODE        NUMBER (6)
);

-- Foregin Key
ALTER TABLE EMP_ADDRESS ADD CONSTRAINT EMPADDR_EMPNO_FK FOREIGN KEY(EMPNO) REFERENCES EMPLOYEE(EMPNO);

INSERT INTO EMP_ADDRESS(EMPNO,ADDR_TYPE,DNO,PHONE) VALUES(1111,'PERMANENT','3-85','1234567891');
INSERT INTO EMP_ADDRESS(EMPNO,ADDR_TYPE,DNO,PHONE) VALUES(NULL,'PERMANENT','3-85','1234567891');
INSERT INTO EMP_ADDRESS(EMPNO,ADDR_TYPE,DNO,PHONE) VALUES('','PERMANENT','3-85','1234567891');
INSERT INTO EMP_ADDRESS(EMPNO,ADDR_TYPE,DNO,PHONE) VALUES(1010,'PERMANENT','3-85','1234567891');
INSERT INTO EMP_ADDRESS(EMPNO,ADDR_TYPE,DNO,PHONE) VALUES(1010,'TEMPORARY','3-85','9999966666');
COMMIT;

COLUMN DNO FORMAT A20;
COLUMN ADDRESS_LINE1 FORMAT A20;
SELECT EMPNO,ADDR_TYPE,DNO,ADDRESS_LINE1,PHONE FROM EMP_ADDRESS;


**************************** logs ************************************
SQL> DROP TABLE EMPLOYEE;

Table dropped.

SQL>
SQL> CREATE TABLE EMPLOYEE
  2  (
  3     EMPNO    NUMBER,
  4     ENAME    VARCHAR2 (50),
  5     GENDER   CHAR (1),
  6     DOB      DATE,
  7     DOJ      DATE
  8  );

Table created.

SQL>
SQL> -- Primary Key
SQL> ALTER TABLE EMPLOYEE ADD CONSTRAINT EMP_EMPNO_PK PRIMARY KEY(EMPNO);

Table altered.

SQL>
SQL> INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (NULL,'Rajasekhar','M','10-AUG-1991','16-AUG-2014');
INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (NULL,'Rajasekhar','M','10-AUG-1991','16-AUG-2014')
                                                          *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYSTEM"."EMPLOYEE"."EMPNO")


SQL> INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1010,'Rajasekhar','M','10-AUG-1991','16-AUG-2014');

1 row created.

SQL> INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1010,'Rajasekhar','M','10-AUG-1991','16-AUG-2014');
INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1010,'Rajasekhar','M','10-AUG-1991','16-AUG-2014')
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.EMP_EMPNO_PK) violated


SQL>
SQL> -- NOT NULL
SQL> ALTER TABLE EMPLOYEE MODIFY ( ENAME VARCHAR2 (50) CONSTRAINT EMP_ENAME_NN NOT NULL );

Table altered.

SQL>
SQL> INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1011,'','M','10-SEP-1991','16-MAY-2015');
INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1011,'','M','10-SEP-1991','16-MAY-2015')
                                                               *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYSTEM"."EMPLOYEE"."ENAME")


SQL> INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1011,'Siva','M','10-SEP-1991','16-MAY-2015');

1 row created.

SQL>
SQL> -- CHECK
SQL> ALTER TABLE EMPLOYEE ADD CONSTRAINT EMP_GENDER_CHK CHECK (GENDER IN ('M','F'));

Table altered.

SQL>
SQL> INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1012,'Jyothi','O','10-APR-1991','16-SEP-2011');
INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1012,'Jyothi','O','10-APR-1991','16-SEP-2011')
*
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.EMP_GENDER_CHK) violated


SQL> INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1012,'Jyothi','F','10-APR-1991','16-SEP-2011');

1 row created.

SQL>
SQL> -- DEFAULT
SQL> ALTER TABLE EMPLOYEE MODIFY DOJ DEFAULT SYSDATE;

Table altered.

SQL>
SQL> INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1013,'Balu','M','11-JUL-1991','');

1 row created.

SQL> INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1014,'Sai','M','11-FEB-1991',NULL);

1 row created.

SQL> INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB) VALUES (1015,'Hari','M','11-JAN-1991');

1 row created.

SQL>
SQL> SELECT * FROM EMPLOYEE;

     EMPNO ENAME                                              G DOB       DOJ
---------- -------------------------------------------------- - --------- ---------
      1010 Rajasekhar                                         M 10-AUG-91 16-AUG-14
      1011 Siva                                               M 10-SEP-91 16-MAY-15
      1012 Jyothi                                             F 10-APR-91 16-SEP-11
      1013 Balu                                               M 11-JUL-91
      1014 Sai                                                M 11-FEB-91
      1015 Hari                                               M 11-JAN-91 09-AUG-19

6 rows selected.

SQL> DROP TABLE EMPLOYEE;

Table dropped.

SQL>
SQL> CREATE TABLE EMPLOYEE
  2  (
  3     EMPNO    NUMBER,
  4     ENAME    VARCHAR2 (50),
  5     GENDER   CHAR (1),
  6     DOB      DATE,
  7     DOJ      DATE
  8  );

Table created.

SQL>
SQL> -- Primary Key
SQL> ALTER TABLE EMPLOYEE ADD CONSTRAINT EMP_EMPNO_PK PRIMARY KEY(EMPNO);

Table altered.

SQL>
SQL> INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (NULL,'Rajasekhar','M','10-AUG-1991','16-AUG-2014');
INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (NULL,'Rajasekhar','M','10-AUG-1991','16-AUG-2014')
                                                          *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYSTEM"."EMPLOYEE"."EMPNO")


SQL> INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1010,'Rajasekhar','M','10-AUG-1991','16-AUG-2014');

1 row created.

SQL> INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1010,'Rajasekhar','M','10-AUG-1991','16-AUG-2014');
INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1010,'Rajasekhar','M','10-AUG-1991','16-AUG-2014')
*
ERROR at line 1:
ORA-00001: unique constraint (SYSTEM.EMP_EMPNO_PK) violated


SQL>
SQL> -- NOT NULL
SQL> ALTER TABLE EMPLOYEE MODIFY ( ENAME VARCHAR2 (50) CONSTRAINT EMP_ENAME_NN NOT NULL );

Table altered.

SQL>
SQL> INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1011,'','M','10-SEP-1991','16-MAY-2015');
INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1011,'','M','10-SEP-1991','16-MAY-2015')
                                                               *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SYSTEM"."EMPLOYEE"."ENAME")


SQL> INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1011,'Siva','M','10-SEP-1991','16-MAY-2015');

1 row created.

SQL>
SQL> -- CHECK
SQL> ALTER TABLE EMPLOYEE ADD CONSTRAINT EMP_GENDER_CHK CHECK (GENDER IN ('M','F'));

Table altered.

SQL>
SQL> INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1012,'Jyothi','O','10-APR-1991','16-SEP-2011');
INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1012,'Jyothi','O','10-APR-1991','16-SEP-2011')
*
ERROR at line 1:
ORA-02290: check constraint (SYSTEM.EMP_GENDER_CHK) violated


SQL> INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1012,'Jyothi','F','10-APR-1991','16-SEP-2011');

1 row created.

SQL>
SQL> -- DEFAULT
SQL> ALTER TABLE EMPLOYEE MODIFY DOJ DEFAULT SYSDATE;

Table altered.

SQL>
SQL> INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1013,'Balu','M','11-JUL-1991','');

1 row created.

SQL> INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB,DOJ) VALUES (1014,'Sai','M','11-FEB-1991',NULL);

1 row created.

SQL> INSERT INTO EMPLOYEE (EMPNO,ENAME,GENDER,DOB) VALUES (1015,'Hari','M','11-JAN-1991');

1 row created.

SQL>
SQL> SELECT * FROM EMPLOYEE;

     EMPNO ENAME                                              G DOB       DOJ
---------- -------------------------------------------------- - --------- ---------
      1010 Rajasekhar                                         M 10-AUG-91 16-AUG-14
      1011 Siva                                               M 10-SEP-91 16-MAY-15
      1012 Jyothi                                             F 10-APR-91 16-SEP-11
      1013 Balu                                               M 11-JUL-91
      1014 Sai                                                M 11-FEB-91
      1015 Hari                                               M 11-JAN-91 09-AUG-19

6 rows selected.

SQL> COMMIT;

Commit complete.

SQL>

SQL> DROP TABLE EMP_ADDRESS;

Table dropped.

SQL>
SQL> CREATE TABLE EMP_ADDRESS
  2  (
  3     EMPNO           NUMBER,
  4     ADDR_TYPE       VARCHAR2 (10),
  5     DNO             VARCHAR2 (30),
  6     ADDRESS_LINE1   VARCHAR2 (100),
  7     ADDRESS_LINE2   VARCHAR2 (100),
  8     ADDRESS_LINE3   VARCHAR2 (100),
  9     CITY            VARCHAR2 (50),
 10     STATE           VARCHAR2 (50),
 11     COUNTRY         VARCHAR2 (50),
 12     PHONE           NUMBER (10),
 13     ZIP_CODE        NUMBER (6)
 14  );

Table created.

SQL>
SQL> -- Foregin Key
SQL> ALTER TABLE EMP_ADDRESS ADD CONSTRAINT EMPADDR_EMPNO_FK FOREIGN KEY(EMPNO) REFERENCES EMPLOYEE(EMPNO);

Table altered.

SQL>
SQL> INSERT INTO EMP_ADDRESS(EMPNO,ADDR_TYPE,DNO,PHONE) VALUES(1111,'PERMANENT','3-85','1234567891');
INSERT INTO EMP_ADDRESS(EMPNO,ADDR_TYPE,DNO,PHONE) VALUES(1111,'PERMANENT','3-85','1234567891')
*
ERROR at line 1:
ORA-02291: integrity constraint (SYSTEM.EMPADDR_EMPNO_FK) violated - parent key not found


SQL> INSERT INTO EMP_ADDRESS(EMPNO,ADDR_TYPE,DNO,PHONE) VALUES(NULL,'PERMANENT','3-85','1234567891');

1 row created.

SQL> INSERT INTO EMP_ADDRESS(EMPNO,ADDR_TYPE,DNO,PHONE) VALUES('','PERMANENT','3-85','1234567891');

1 row created.

SQL> INSERT INTO EMP_ADDRESS(EMPNO,ADDR_TYPE,DNO,PHONE) VALUES(1010,'PERMANENT','3-85','1234567891');

1 row created.

SQL> INSERT INTO EMP_ADDRESS(EMPNO,ADDR_TYPE,DNO,PHONE) VALUES(1010,'TEMPORARY','3-85','9999966666');

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL> COLUMN DNO FORMAT A20;
SQL> COLUMN ADDRESS_LINE1 FORMAT A20;
SQL> SELECT EMPNO,ADDR_TYPE,DNO,ADDRESS_LINE1,PHONE FROM EMP_ADDRESS;

     EMPNO ADDR_TYPE  DNO                  ADDRESS_LINE1             PHONE
---------- ---------- -------------------- -------------------- ----------
           PERMANENT  3-85                                      1234567891
           PERMANENT  3-85                                      1234567891
      1010 PERMANENT  3-85                                      1234567891
      1010 TEMPORARY  3-85                                      9999966666

SQL>
-

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Delete Row Button to all records of Interactive Report in Oracle Apex

 1. add 'Delete' Del column to Report Query 2. Set the Following Properties for the DEL Column Type: Link Heading: Delete Targ...