Day 5 - Data Definition Language (DDL) and Data Control Language (DCL)

📌 Key Takeaways

• DDL handles table structure. It includes CREATE, ALTER, DROP, RENAME, and TRUNCATE, and in Oracle it is committed immediately when run.
• There are five constraints: PRIMARY KEY (NOT NULL + no duplicates), UNIQUE (no duplicates, multiple NULLs allowed), NOT NULL, CHECK, and FOREIGN KEY (only values that exist in the parent).
• A FOREIGN KEY's ON DELETE CASCADE deletes the children when the parent is deleted, and ON DELETE SET NULL changes the children's foreign key to NULL. With no option, a parent that has children cannot be deleted.
• CREATE TABLE … AS SELECT (CTAS) copies the structure and data, but of the constraints only NOT NULL comes along. DEFAULT is not copied either.
• DCL's GRANT and REVOKE give and take away privileges. For object privileges, WITH GRANT OPTION makes revocation cascade; for system privileges, WITH ADMIN OPTION does not cascade.

CREATE TABLE — Data Types and DEFAULT

CREATE TABLE table (
  column datatype [DEFAULT value] [CONSTRAINT name] [constraint],
  …,
  [CONSTRAINT name] constraint (column, …)   -- table-level constraint
);

• LENGTH(CAST('AB' AS CHAR(5))) was 5, and LENGTH(CAST('AB' AS VARCHAR2(5))) was 2. CHAR pads the leftover space with spaces.
• SQL Server's variable-length character type is VARCHAR. VARCHAR2 is Oracle's name.

Constraints

We create the constraints with names and try inserting values that violate each one.

CREATE TABLE T_DEPT (DEPTNO NUMBER(2) CONSTRAINT T_DEPT_PK PRIMARY KEY, DNAME VARCHAR2(14) CONSTRAINT T_DEPT_NN NOT NULL CONSTRAINT T_DEPT_UK UNIQUE, LOC VARCHAR2(13) DEFAULT 'SEOUL');
CREATE TABLE T_EMP (EMPNO NUMBER(4) CONSTRAINT T_EMP_PK PRIMARY KEY, ENAME VARCHAR2(10), SAL NUMBER(7,2) CONSTRAINT T_EMP_CK CHECK (SAL > 0), DEPTNO NUMBER(2) CONSTRAINT T_EMP_FK REFERENCES T_DEPT (DEPTNO));
INSERT INTO T_DEPT (DEPTNO, DNAME) VALUES (10, 'ACCOUNTING');
INSERT INTO T_DEPT (DEPTNO, DNAME) VALUES (10, 'SALES');
INSERT INTO T_DEPT (DEPTNO, DNAME) VALUES (20, 'ACCOUNTING');
INSERT INTO T_DEPT (DEPTNO) VALUES (30);
INSERT INTO T_EMP VALUES (1, 'KIM', 0, 10);
INSERT INTO T_EMP VALUES (1, 'KIM', 100, 99);
INSERT INTO T_EMP VALUES (1, 'KIM', 100, 10);
DELETE FROM T_DEPT WHERE DEPTNO = 10;
DROP TABLE T_EMP PURGE;
DROP TABLE T_DEPT PURGE;

Table created.
Table created.
1 row created.
INSERT INTO T_DEPT (DEPTNO, DNAME) VALUES (10, 'SALES')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.T_DEPT_PK) violated on table SCOTT.T_DEPT columns (DEPTNO)
ORA-03301: (ORA-00001 details) row with column values (DEPTNO:10) already exists
INSERT INTO T_DEPT (DEPTNO, DNAME) VALUES (20, 'ACCOUNTING')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.T_DEPT_UK) violated on table SCOTT.T_DEPT columns (DNAME)
ORA-03301: (ORA-00001 details) row with column values (DNAME:'ACCOUNTING') already exists
INSERT INTO T_DEPT (DEPTNO) VALUES (30)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."T_DEPT"."DNAME")
INSERT INTO T_EMP VALUES (1, 'KIM', 0, 10)
*
ERROR at line 1:
ORA-02290: check constraint (SCOTT.T_EMP_CK) violated
INSERT INTO T_EMP VALUES (1, 'KIM', 100, 99)
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.T_EMP_FK) violated - parent key not found
1 row created.
DELETE FROM T_DEPT WHERE DEPTNO = 10
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.T_EMP_FK) violated - child record found
Table dropped.
Table dropped.

• 23ai appends the column name and an ORA-03301 detail line to ORA-00001. Earlier versions give the single line ORA-00001: unique constraint (SCOTT.T_DEPT_PK) violated.
• We dropped the child table T_EMP first. If you reverse the order and DROP the parent first, you get ORA-02449. To drop the parent first, use DROP TABLE T_DEPT CASCADE CONSTRAINTS to remove the children's foreign keys along with it.
• A constraint you did not name gets a system name such as SYS_C008727. It is hard to tell from the error message which constraint it is.

We checked the relationship between NULL and constraints separately.

• Even when we inserted NULL into a UNIQUE column twice, both went in. NULLs are not regarded as equal to each other.
• Inserting NULL into a CHECK (A > 0) column also went in. CHECK blocks only when the condition is FALSE, and lets the UNKNOWN of a NULL comparison pass.
• Inserting NULL into a foreign key column goes in without looking for a parent.

Delete Options for a FOREIGN KEY

We create three child tables that reference the same parent. Only the option differs.

CREATE TABLE T_DEPT AS SELECT DEPTNO, DNAME FROM DEPT;
ALTER TABLE T_DEPT ADD CONSTRAINT T_DEPT_PK PRIMARY KEY (DEPTNO);
CREATE TABLE T_EMP1 (EMPNO NUMBER(4), DEPTNO NUMBER(2) CONSTRAINT T_EMP1_FK REFERENCES T_DEPT (DEPTNO) ON DELETE CASCADE);
CREATE TABLE T_EMP2 (EMPNO NUMBER(4), DEPTNO NUMBER(2) CONSTRAINT T_EMP2_FK REFERENCES T_DEPT (DEPTNO) ON DELETE SET NULL);
CREATE TABLE T_EMP3 (EMPNO NUMBER(4), DEPTNO NUMBER(2) CONSTRAINT T_EMP3_FK REFERENCES T_DEPT (DEPTNO));
INSERT INTO T_EMP1 SELECT EMPNO, DEPTNO FROM EMP;
INSERT INTO T_EMP2 SELECT EMPNO, DEPTNO FROM EMP;
INSERT INTO T_EMP3 SELECT EMPNO, DEPTNO FROM EMP WHERE DEPTNO = 20;
DELETE FROM T_DEPT WHERE DEPTNO = 20;
DELETE FROM T_DEPT WHERE DEPTNO = 30;
SELECT (SELECT COUNT(*) FROM T_EMP1) AS E1_ROWS, (SELECT COUNT(*) FROM T_EMP2) AS E2_ROWS, (SELECT COUNT(*) FROM T_EMP2 WHERE DEPTNO IS NULL) AS E2_NULL FROM DUAL;
DROP TABLE T_DEPT CASCADE CONSTRAINTS PURGE;
DROP TABLE T_EMP1 PURGE;
DROP TABLE T_EMP2 PURGE;
DROP TABLE T_EMP3 PURGE;

Table created.
Table altered.
Table created.
Table created.
Table created.
14 rows created.
14 rows created.
5 rows created.
DELETE FROM T_DEPT WHERE DEPTNO = 20
*
ERROR at line 1:
ORA-02292: integrity constraint (SCOTT.T_EMP3_FK) violated - child record found
1 row deleted.
   E1_ROWS    E2_ROWS    E2_NULL
---------- ---------- ----------
         8         14          6
1 row selected.
Table dropped.
Table dropped.
Table dropped.
Table dropped.

• Department 20 is referenced by T_EMP3, which has no option, so the deletion was rejected. The whole statement failed, so the department 20 rows in T_EMP1 and T_EMP2 are also unchanged.
• When department 30 was deleted, the 6 rows of department 30 employees in T_EMP1, which is CASCADE, were deleted along with it (14 → 8).
• T_EMP2, which is SET NULL, kept all 14 rows, and the DEPTNO of 6 of them became NULL.

• Oracle has no ON UPDATE CASCADE. SQL Server also supports ON UPDATE CASCADE and SET DEFAULT.

CTAS — Copying a Table

CREATE TABLE new_table AS SELECT … creates a new table with the structure and rows of the SELECT result as is. Adding WHERE 1 = 2 copies only the structure.

CREATE TABLE T_DEPT (DEPTNO NUMBER(2) CONSTRAINT T_DEPT_PK PRIMARY KEY, DNAME VARCHAR2(14) CONSTRAINT T_DEPT_NN NOT NULL, LOC VARCHAR2(13) DEFAULT 'SEOUL' CONSTRAINT T_DEPT_CK CHECK (LOC <> 'X'));
INSERT INTO T_DEPT (DEPTNO, DNAME) VALUES (10, 'ACCOUNTING');
CREATE TABLE T_DEPT2 AS SELECT * FROM T_DEPT;
SELECT SUBSTR(TABLE_NAME, 1, 8) AS TNAME, CONSTRAINT_TYPE AS T, SUBSTR(SEARCH_CONDITION_VC, 1, 20) AS COND FROM USER_CONSTRAINTS WHERE TABLE_NAME IN ('T_DEPT', 'T_DEPT2') ORDER BY TNAME, T, COND;
INSERT INTO T_DEPT2 (DEPTNO, DNAME) VALUES (10, 'SALES');
INSERT INTO T_DEPT2 VALUES (20, 'IT', 'X');
INSERT INTO T_DEPT2 (DEPTNO) VALUES (30);
SELECT * FROM T_DEPT2 ORDER BY DEPTNO, DNAME;
DROP TABLE T_DEPT2 PURGE;
DROP TABLE T_DEPT PURGE;

Table created.
1 row created.
Table created.
TNAME                            T COND
-------------------------------- - --------------------------------------------------------------------------------
T_DEPT                           C "DNAME" IS NOT NULL
T_DEPT                           C LOC <> 'X'
T_DEPT                           P
T_DEPT2                          C "DNAME" IS NOT NULL
4 rows selected.
1 row created.
1 row created.
INSERT INTO T_DEPT2 (DEPTNO) VALUES (30)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."T_DEPT2"."DNAME")
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     SEOUL
        10 SALES
        20 IT             X
3 rows selected.
Table dropped.
Table dropped.

• The original T_DEPT has NOT NULL and CHECK (type C) and PRIMARY KEY (P). The copy T_DEPT2 kept only a single NOT NULL.
• With no PK, DEPTNO 10 went in twice. With no CHECK, LOC 'X' went in too.
• The LOC of 10 SALES is empty. The DEFAULT 'SEOUL' was not copied either.
• NOT NULL stayed, so an INSERT that omits DNAME gives ORA-01400.
• In SQL Server, SELECT … INTO new_table FROM … does the same job.

ALTER · RENAME · TRUNCATE · DROP

CREATE TABLE EMP_T AS SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 10;
ALTER TABLE EMP_T ADD (BONUS NUMBER(7,2) DEFAULT 0);
ALTER TABLE EMP_T ADD (MEMO VARCHAR2(20));
ALTER TABLE EMP_T MODIFY (ENAME VARCHAR2(20));
ALTER TABLE EMP_T MODIFY (ENAME VARCHAR2(3));
ALTER TABLE EMP_T RENAME COLUMN SAL TO SALARY;
ALTER TABLE EMP_T DROP COLUMN MEMO;
ALTER TABLE EMP_T ADD CONSTRAINT EMP_T_PK PRIMARY KEY (EMPNO);
ALTER TABLE EMP_T ADD (GRADE NUMBER NOT NULL);
ALTER TABLE EMP_T MODIFY (ENAME NOT NULL);
RENAME EMP_T TO T_STAFF;
SELECT * FROM T_STAFF ORDER BY EMPNO;
TRUNCATE TABLE T_STAFF;
SELECT COUNT(*) FROM T_STAFF;
DROP TABLE T_STAFF PURGE;

Table created.
Table altered.
Table altered.
Table altered.
ALTER TABLE EMP_T MODIFY (ENAME VARCHAR2(3))
                          *
ERROR at line 1:
ORA-01441: cannot decrease column length because some value is too big
Table altered.
Table altered.
Table altered.
ALTER TABLE EMP_T ADD (GRADE NUMBER NOT NULL)
            *
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
Table altered.
Table renamed.
     EMPNO ENAME                    SALARY      BONUS
---------- -------------------- ---------- ----------
      7782 CLARK                      2450          0
      7839 KING                       5000          0
      7934 MILLER                     1300          0
3 rows selected.
Table truncated.
  COUNT(*)
----------
         0
1 row selected.
Table dropped.

• When you ADD a column with a DEFAULT, existing rows are filled with that value too. BONUS is 0 everywhere.
• A MODIFY that increases the length always works. Shrinking it below the values already stored gives ORA-01441.
• You cannot add a NOT NULL column without a DEFAULT to a table that has rows (ORA-01758), because the new column would be NULL for the existing rows.
• Without PURGE, a dropped table goes to the Recycle Bin and can be restored with FLASHBACK TABLE. PURGE deletes it immediately without passing through the Recycle Bin.
• SQL Server uses ALTER TABLE t ALTER COLUMN … to change a column and sp_rename to rename.

DCL — Granting and Revoking Privileges

DCL creates users and manages privileges. The practice lab account has no privileges, so we look at syntax only, without result tables.

CREATE USER user IDENTIFIED BY password;
GRANT system_privilege [, …] TO user [WITH ADMIN OPTION];
GRANT object_privilege [, …] ON object TO user [WITH GRANT OPTION];
REVOKE privilege [ON object] FROM user;

• Whether revocation cascades was checked in the practice container by creating three users (A, B, C).
• CREATE USER alone does not let the user connect. The CREATE SESSION privilege is needed to log in.

ROLE

Instead of granting privileges to each user one by one, you create a bundle of privileges (a ROLE) and grant it all at once.

CREATE ROLE R_DEV;
GRANT CREATE SESSION, CREATE TABLE TO R_DEV;
GRANT R_DEV TO KIM;
REVOKE R_DEV FROM KIM;

• In the practice container, RESOURCE also contained CREATE VIEW and CREATE SYNONYM. Textbooks for earlier versions say RESOURCE has no CREATE VIEW. For the exam, it is enough to know "CONNECT is for connecting, RESOURCE is for creating objects."

Next week is the final review before the exam, and we start by revisiting the NULL and COUNT questions that are easy to mix up.

📖 Glossary

• DDL : Statements that create, change, and remove the structure of objects such as tables. In Oracle they are committed immediately when run.
• Constraint : A rule for the values a column may hold. It blocks any INSERT, UPDATE, or DELETE that violates it.
• Foreign key (FOREIGN KEY) : A constraint that allows only primary key values of another table (the parent). It preserves referential integrity.
• CTAS : CREATE TABLE … AS SELECT, a statement that makes a query result into a new table as is.
• Recycle Bin : Where a table dropped without PURGE stays for a while. It can be restored with FLASHBACK TABLE.
• DCL : Statements that grant and revoke privileges. They are GRANT and REVOKE.
• ROLE : A named bundle of several privileges. Used to grant privileges to a user all at once.
• WITH GRANT OPTION / WITH ADMIN OPTION : Options that let the recipient pass on the object privilege / system privilege they received.