Assignment 7 (SQL / DDL / Constraints)

SQL DDL Commands & Constraints: CREATE, ALTER, DROP With Real Examples

Topic: Understanding DDL commands (CREATE, ALTER, DROP, RENAME, TRUNCATE) and SQL Constraints (PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK) — with a full step-by-step assignment walkthrough.

Before you can write SELECT queries or JOIN tables, you need to actually build those tables. That's what DDL (Data Definition Language) is for. And once your tables exist, constraints are what keep the data inside them clean, consistent, and reliable.

This guide covers both concepts clearly, then walks through the complete assignment — 11 queries that take you from creating a raw table all the way to adding primary keys, foreign keys, and NOT NULL constraints.

What Are DDL Commands?

DDL stands for Data Definition Language — the set of SQL commands used to define and modify the structure of database objects like tables, views, and indexes.

💡 DDL commands are auto-committed in Oracle — there's no ROLLBACK after a DROP or TRUNCATE. Always double-check before running them.

What Are Constraints in SQL?

Constraints are rules applied to table columns that restrict what data can be stored. They protect data integrity at the database level — so bad data can't slip in even if the application has a bug.

Constraints can be added at table creation time (CREATE TABLE) or added later with ALTER TABLE — both approaches are shown in the assignment below.

For the full Oracle specification, see the Oracle documentation on Constraints.

Viewing Constraints — USER_CONSTRAINTS

Oracle stores all constraint metadata in a data dictionary view called USER_CONSTRAINTS. You can query it anytime to confirm what constraints exist on your tables:

SELECT constraint_name, constraint_type, table_name
FROM user_constraints;

The constraint_type column uses single-letter codes: P = Primary Key, R = Foreign Key (Referential), U = Unique, C = Check / NOT NULL.

Assignment 7 — Table Structure

Two tables are used throughout this assignment:

emp_new (created first, later renamed to employee)

dept

Assignment 7 — All 11 Queries With Explanation

Query 1: Create the emp_new Table

CREATE TABLE emp_new (
    id         NUMBER(7),
    last_name  VARCHAR2(25),
    first_name VARCHAR2(25),
    dept_id    NUMBER(7)
);

No constraints yet — this is a bare-bones table. Constraints will be added separately using ALTER TABLE later in the assignment.

Query 2: Create the dept Table

CREATE TABLE dept (
    dept_id NUMBER(10),
    dname   VARCHAR2(20)
);

Query 3: Increase last_name Column Size to 75

ALTER TABLE emp_new
MODIFY last_name VARCHAR2(75);

MODIFY changes a column's data type or size. You can increase a VARCHAR2 size freely — but reducing it will fail if existing data is longer than the new size.

Query 4: Drop the first_name Column

ALTER TABLE emp_new
DROP COLUMN first_name;

Verify the change with:

DESC emp_new;

DESC shows the current column list and data types. After this step, first_name should no longer appear.

Query 5: Add a New salary Column

ALTER TABLE emp_new
ADD salary NUMBER(10);

New columns are added with ADD. The column will initially contain NULL for all existing rows since no default value is specified.

Query 6: Rename Column dept_id to deptno

ALTER TABLE emp_new
RENAME COLUMN dept_id TO deptno;

Column renaming uses RENAME COLUMN ... TO ... inside an ALTER TABLE statement — note this is different from renaming a whole table.

Query 7: Rename the Table emp_new to employee

RENAME emp_new TO employee;

In Oracle, the standalone RENAME command renames a table. From this point forward, all queries use the name employee.

Query 8: Add Primary Key to dept Table

Constraint name: my_dept_id_pk

ALTER TABLE dept
ADD CONSTRAINT my_dept_id_pk PRIMARY KEY (dept_id);

Naming your constraints explicitly (like my_dept_id_pk) is good practice — it makes error messages readable and makes dropping specific constraints easy later.

Query 9: Add Foreign Key in employee Table

Constraint name: my_emp_dept_id_fk

ALTER TABLE employee
ADD CONSTRAINT my_emp_dept_id_fk
FOREIGN KEY (deptno)
REFERENCES dept(dept_id);

This links employee.deptno to dept.dept_id. After this, any deptno value inserted into employee must already exist in the dept table — Oracle will reject it otherwise.

💡 The referenced column (dept.dept_id) must already have a PRIMARY KEY or UNIQUE constraint before you can reference it with a FOREIGN KEY. That's why Query 8 came first.

Query 10: Make last_name NOT NULL

Since first_name was dropped in Query 4, only last_name is updated here.

ALTER TABLE employee
MODIFY last_name VARCHAR2(75) NOT NULL;

NOT NULL is applied via MODIFY. The size is re-specified to avoid accidentally resetting it — it's safe to repeat the current value.

Query 11: Confirm All Constraints on Both Tables

SELECT constraint_name, constraint_type, table_name
FROM user_constraints
WHERE table_name IN ('EMPLOYEE', 'DEPT');

Expected constraint types in the output:

Note: Table names in USER_CONSTRAINTS are stored in uppercase — always use uppercase in the WHERE clause or the query returns nothing.

Common Mistakes to Avoid

• Adding a FOREIGN KEY before the referenced PRIMARY KEY exists — Oracle will throw an error. Always create the parent constraint first.

• Using = with column names in USER_CONSTRAINTS — table names are stored uppercase, so WHERE table_name = 'employee' returns nothing; use 'EMPLOYEE'.

• Confusing DROP TABLE vs TRUNCATE — DROP removes the table entirely; TRUNCATE keeps the structure but removes all rows.

• Reducing column size when data exists — MODIFY VARCHAR2(10) on a column with 20-character values will fail.

For a broader reference on DDL best practices, GeeksforGeeks has a solid overview of DDL commands in SQL worth bookmarking.

Also Explore These Topics

• SQL Quick Recap

• SQL Assignment 5 — Aggregate Functions (GROUP BY, HAVING)

• SQL Assignment 6 — Joins & Subqueries

• SQL Views & Set Operations — Assignment 8

• SQL Assignment 9 — PL/SQL

• DBMS Overview — Foundations Before SQL

• Data Science Basics — Where SQL Meets Analysis

Written by Divya Sachan · Published on Notehub