Oracle DML, DDL and Constraint Statements

DML Statements

Add a new row to the table:

INSERT INTO departments

            (location, dname, department_id)

VALUES

            (‘Beijing’, ‘Public Relations’, 50);

INSERT INTO employees

VALUES

            (9013, USER, 'MANAGER', 7788, ADD_MONTHS (SYSDATE, -36), DEFAULT, '', NULL);

INSERT INTO employees_copy

VALUES

            (SELECT empno, ename, job, mgr, hiredate  --?

             FROM employees);

Modifies existing rows in the table:

UPDATE departments

SET    dname = 'Engineering',

       location = DEFAULT

WHERE  department_id = 50;

UPDATE employees

SET    ename = (SELECT ename FROM emp WHERE empno = 7844),

       mgr = DEFAULT

WHERE  empno = 7944;

Removes existing rows from the table

DELETE FROM departments/ DELETE FROM departments

WHERE  department_id = 50;

Conditionally inserts / updates data in a table

MERGE INTO departments

WHERE  department_id = 50;

DDL Statements

Creates a table

CREATE TABLE departments_copy

            (department_id           NUMBER(2),

             dname                        VARCHAR2(14) DEFAULT ‘Administration’);

Modifies table structures

ALTER TABLE departments_copy

ADD / MODIFY / DROP COLUMN / SET UNUSED ...

...

Adding a Column

ALTER TABLE departments_copy

ADD    (dcategory VARCHAR2(9) DEFAULT ‘General’,

              mgr_head  NUMBER(5)      DEFAULT 12345);

Modifying a Column

ALTER TABLE departments_copy

MODIFY (dcategory VARCHAR2(30) DEFAULT ‘Private’,

              mgr_head      VARCHAR2(10) DEFAULT ‘None’);

Renaming a Column

ALTER TABLE departments_copy

RENAME COLUMN (dcategory TO dept_cat);

Dropping a Column[s]

ALTER TABLE departments_copy

DROP COLUMN     dcategory;/ DROP COLUMN (mgr_head [, dcategory, ...]);

SET UNUSED Option

ALTER TABLE departments_copy

SET UNUSED (dcategory, mgr_head);

ALTER TABLE departments_copy

DROP UNUSED COLUMNS;

Removes the rows and table structure

DROP TABLE departments_copy;

Changes the name of a table

RENAME [TABLE] departments_copy TO detail_dept;

OR

ALTER TABLE departments_copy

RENAME TO detail_dept;

Removes all rows from a table

TRUNCATE TABLE detail_dept;  --Releases the storage space,                             cannot rollback.

OR

DELETE         detail_dept;               --Do not release the storage space,                   can-be rollback.

Explicit Locking for all SQL statements except SELECT

SELECT * FROM detail_dept
FOR UPDATE NOWAIT;

Add Comments to a column/ table/ view (Delete Comments by setting it to empty string i.e. ‘’.)

COMMENT ON TABLE departments_copy          /           COMMENT ON COLUMN departments_copy.dname
IS ‘Copy of Departments table’;                    IS ‘This column has Department Names’;

USER_TAB_COMMENTS                                                            USER_COL_COMMENTS

CONSTRAINTS (using CREATE TABLE)

                                                                         Column-Level only

NOT NULL Constraints

CREATE TABLE employees

            (employee_id  NUMBER(2),

             last_name                   VARCHAR2(25) NOT NULL,          --Oracle Server Named Cons.

             salary              NUMBER(8,2),

             commission_pct         NUMBER(2,2),

             hire_date                    DATE             CONSTRAINT emp_hire_date_nn

                                                                         NOT NULL    --User Named Cons.

            );

                                                                         Column-Level
             Table-Level

UNIQUE Constraints

CREATE TABLE employees

            (employee_id  NUMBER(2)  UNIQUE,        --Oracle Server Named Cons.

             last_name                   VARCHAR2(25) NOT NULL,

             nic_no                        NUMBER(14),

             passport_no    NUMBER(7),

             cell_no                        NUMBER(11)   CONSTRAINT emp_cell_no_uk

                                                                         UNIQUE,        --User Named Cons.

             UNIQUE (nic_no),

             CONSTRAINT emp_passport_no_uk UNIQUE (passport_no)

            );

PRIMARY KEY Constraints

CREATE TABLE employees

            (CONSTRAINT emp_passport_no_pk PRIMARY KEY (passport_no),

             employee_id   NUMBER(2)  PRIMARY KEY, --Oracle Server Named Cons.

             last_name                   VARCHAR2(25) NOT NULL DISABLE,

             nic_no                        NUMBER(14),

             passport_no    NUMBER(7),

             sal                               NUMBER(5)  CHECK (sal>5000) DISABLE,

             cell_no                        NUMBER(11)            CONSTRAINT emp_cell_no_pk

                                                                         PRIMARY KEY, --User Named Cons.

             PRIMARY KEY (nic_no)

            );

FOREIGN KEY Constraints

CREATE TABLE employees

            (manager_id                NUMBER(2)  REFERENCES employees(employee_id),

                                                                                                  --Oracle Server Named Cons.

             employee_id   NUMBER(2)  PRIMARY KEY,

             department_id,

             location_id                             CONSTRAINT emp_location_id_fk

                                                                         REFERENCES location,

                                                                                                  --User Named Cons.

             last_name                   VARCHAR2(25) NOT NULL,

             test_id                        NUMBER(2),

             CONSTRAINT emp_department_id_fk

             FOREIGN KEY (department_id) REFERENCES department
             ON DELETE CASCADE / ON DELETE SET NULL

             FOREIGN KEY (test_id) REFERENCES employees(employee_id),

            );

CHECK Constraints

CREATE TABLE employees

            (employee_id  NUMBER(2), CHECK (employee_id > 5000),

                                                                                                  --Oracle Server Named Cons.

             salary              NUMBER(8,2)  CONSTRAINT emp_salary_min_chk

                                                                         CHECK (salary > 0)

                                                                         DISABLE       --User Named Cons.

             CHECK (employee_id > 5000),

             CONSTRAINT emp_salary_min_chk CHECK (salary > 0)

            );

CONSTRAINTS (using ALTER TABLE)

Adding/ Dropping/ Disabling/ Enabling Constraints (Using Constraint Name)

ALTER TABLE employees

ADD/ DROP/ DISABLE/ ENABLE CONSTRAINT SYS_C003553;

Adding Constraints (Disabled Explicitly) – Table-Level only except NOT NULL

ALTER TABLE employees

MODIFY (manager_id                      NOT NULL
                                                            DISABLE);  --Oracle Server Named Cons.

ALTER TABLE employees

MODIFY (manager_id                      CONSTRAINT emp_manager_id_nn
                                                            NOT NULL

                                                            DISABLE);  --User Named Cons.

ALTER TABLE employees

ADD                                                   UNIQUE(manager_id)

                                                            DISABLE;                   --Oracle Server Named Cons.

ALTER TABLE employees

ADD                                                   CONSTRAINT emp_manager_id_uk

                                                            UNIQUE(manager_id); --User Named Cons.

ALTER TABLE employees

ADD                                                   PRIMARY KEY(manager_id)

                                                            DISABLE;                   --Oracle Server Named Cons.

ALTER TABLE employees

ADD                                                   CONSTRAINT emp_manager_id_pk

                                                            PRIMARY KEY(manager_id)

                                                            DISABLE;                   --User Named Cons.

ALTER TABLE employees

ADD                                                   FOREIGN KEY(manager_id) REFERENCES employees

                                                            DISABLE;                   --Oracle Server Named Cons.

ALTER TABLE employees

ADD                                                   CONSTRAINT emp_manager_id_fk

                                                            FOREIGN KEY(manager_id) REFERENCES employees

                                                            DISABLE;                   --User Named Cons.

ALTER TABLE employees

ADD   (                                               CHECK         (duty_pct < 10));                                                                                                                                                           --Oracle Server Named Cons.

ALTER TABLE employees

ADD   (                                               CONSTRAINT emp_duty_chk

                                                            CHECK         (duty_pct < 10));

                                                                                      --User Named Cons.

ALTER TABLE employees                                                   --Add a new column with Cons.

ADD   (duty_pct        NUMBER(2,2)           CHECK         (duty_pct < 10)

                                                            NOT NULL,                --Oracle Server Named Cons.

             department_id,                       CONSTRAINT emp_department_id_fk

                                                            FOREIGN KEY(department_id) REFERENCES departments

                                                            DISABLE);                  --User Named Cons.

Dropping/ Disabling Constraints

ALTER TABLE employees

DROP    PRIMARY KEY/ UNIQUE (email) [CASCADE];/

DISABLE PRIMARY KEY/ UNIQUE (email) [CASCADE];

Dropping Table/ Column having Constraints defined

DROP TABLE employees/ DROP COLUMN employee_id

CASCADE CONSTRAINTS; --To Drop an object other than Cons., tell Oracle to effect on its Cons. also,

                                          if there any exist, using CONSTRAINTS keyword after CASCADE.

Enabling Constraints

ALTER TABLE employees

ENABLE PRIMARY KEY/ UNIQUE (email);

Renaming Constraints

ALTER TABLE employees

RENAME CONSTRAINT cust_fname_nn TO cust_firstname_nn;

CONSTRAINTS (using CREATE TABLE)

PRIMARY KEY Constraints

CREATE TABLE employee

            (CONSTRAINT emp_employee_id_pk PRIMARY KEY (employee_id),

             NOT NULL (salary)

             CHECK    (salary>=5000)    DISABLE,
             UNIQUE   (email)                             DISABLE,

             employee_id   NUMBER(4),

             manager_id                                        REFERENCES employee

                                                                         ON DELETE CASCADE,

             last_name                   VARCHAR2(25) DEFAULT   ‘Public User’

                                                                         NOT NULL

                                                                         DISABLE,

             email               VARCHAR2(25) DEFAULT   ‘user@kingtest.com.uk’, --???

             department_id,                                   FOREIGN KEY (department_id)

                                                                         REFERENCES departments

                                                                         ON DELETE SET NULL,

             salary              NUMBER(5)

            );

Other Database Objects

INDEXES

Creating an Index

CREATE INDEX emp_sal_01                                                         --Non-Unique Index

ON emp (sal);

CREATE UNIQUE INDEX emp_empno_01                                 --Unique Index

ON emp (empno);

CREATE UNIQUE INDEX emp_empno_ename_indx_01           --Composite Index

ON emp (empno, ename);

CREATE INDEX emp_ename_reverse_indx                                  --Reverse Key Index

ON emp (ename) REVERSE;

CREATE BITMAP INDEX emp_deptno_indx_01                       --Bitmap Index

ON emp (deptno);

CREATE INDEX ixd_emp_01                                                        --Function-Based Index

ON emp (sal * 1.08);

Renaming an Index

ALTER INDEX upper_ix

RENAME TO upper_name_ix;

Dropping an Index

DROP INDEX upper_ix;

SYNONYMS

Creating a Synonym

CREATE SYNONYM my_private_synonym                                 --Private Synonym by Default

FOR emp;

CREATE PUBLIC SYNONYM like_this                          --Public Synonym

FOR scott.emp;

Dropping a Synonym

DROP SYNONYM emp_sal;