Using DDL to Create and Manage Tables and Constraints
Using DDL to Create and Manage Tables and Constraints
This domain tests creating and altering tables and enforcing rules with constraints. You need to choose appropriate data types and understand constraint states, foreign keys, and DDL side effects.
Core rules
- Common data types include VARCHAR2, CHAR, NUMBER, DATE, TIMESTAMP, CLOB, and RAW. VARCHAR2 stores variable-length character data; CHAR pads to fixed length.
- Constraints include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, and CHECK. A primary key is unique and not null. A foreign key must match a parent key or be null unless other rules apply.
- NOT NULL is a column constraint in Oracle; it cannot be written as a table-level constraint. CHECK cannot contain a subquery or reference another row.
- ALTER TABLE can add, modify, rename, or drop columns and constraints. Adding NOT NULL to a column containing nulls fails unless nulls are handled first.
- DROP TABLE removes the table and its data; TRUNCATE TABLE removes all rows quickly, resets the high-water mark, and is DDL. TRUNCATE does not fire DML triggers and cannot be rolled back.
- CREATE TABLE AS SELECT creates a table from a query. It copies column definitions and data but not constraints such as primary keys unless added separately.
Worked example
CREATE TABLE orders ( order_id NUMBER CONSTRAINT orders_pk PRIMARY KEY, customer_id NUMBER CONSTRAINT orders_cust_fk REFERENCES customers(customer_id), order_date DATE DEFAULT SYSDATE NOT NULL, amount NUMBER(10,2) CONSTRAINT orders_amt_ck CHECK (amount >= 0) );
The primary key enforces unique, non-null order IDs. The foreign key requires each customer to exist in customers or be null. The default supplies the current date when order_date is omitted. The check constraint rejects negative amounts.
Traps
A table-level NOT NULL definition fails. A foreign key can contain nulls unless the column is also NOT NULL. Dropping a parent table can fail while enabled foreign keys reference it. CTAS does not copy primary-key or foreign-key constraints. TRUNCATE cannot be rolled back and is not a substitute for a conditional DELETE.