SQL DDL: CREATE TABLE, data types, constraints, ON DELETE/ON UPDATE

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. Which constraint forbids NULL in a primary key? from Lesson 2.3.1
2. What does ON DELETE CASCADE do? from Lesson 2.3.2
3. Is SQL relationally complete? from Lesson 3.2.3

1 · The Hook

SQL (Structured Query Language) is what real systems speak: Oracle, PostgreSQL, MySQL, SQL Server, SQLite. It is both a DDL and a DML (Lesson 0.1.2). This lesson is the DDL half: how the college's tables, keys and referential actions are actually declared.

2 · Predict First

In SQL, a column declared UNIQUE: can it hold NULL? Can it hold NULL in two different rows?

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

Yes and yes (in standard SQL). UNIQUE forbids two equal non-NULL values; NULLs are not equal to each other (Lesson 2.1.2), so several NULLs are allowed. A PRIMARY KEY column, by contrast, can never hold NULL.

CREATE TABLE

CREATE TABLE DEPT (
  dname     VARCHAR(10) PRIMARY KEY,
  building  VARCHAR(30) NOT NULL
);

CREATE TABLE STUDENT (
  roll   INT PRIMARY KEY,
  name   VARCHAR(40) NOT NULL,
  dept   VARCHAR(10) REFERENCES DEPT(dname) ON DELETE SET NULL,
  cgpa   DECIMAL(3,1) CHECK (cgpa BETWEEN 0 AND 10),
  email  VARCHAR(60) UNIQUE
);

CREATE TABLE ENROLL (
  roll   INT,
  cid    CHAR(2),
  grade  CHAR(1) DEFAULT 'P',
  PRIMARY KEY (roll, cid),
  FOREIGN KEY (roll) REFERENCES STUDENT(roll) ON DELETE CASCADE,
  FOREIGN KEY (cid)  REFERENCES COURSE(cid)
);

Common data types

INT/INTEGER, SMALLINT, DECIMAL(p, s) / NUMERIC(p, s) (p digits, s after the point), REAL/FLOAT, CHAR(n) (fixed length, padded), VARCHAR(n) (variable length, at most n), DATE, TIME, TIMESTAMP, BOOLEAN.

Constraints

Column-level form (written after a column) can only constrain that column; table-level form (separate line) is needed for composite keys.

CHECK subtlety: CHECK rejects a row only if the condition is FALSE. A NULL cgpa makes cgpa BETWEEN 0 AND 10 UNKNOWN, so the row is accepted.

Changing and removing tables

• ALTER TABLE STUDENT ADD COLUMN phone VARCHAR(15); adds a column (existing rows get NULL or the default).
• ALTER TABLE STUDENT DROP COLUMN phone;
• ALTER TABLE … ADD CONSTRAINT … / DROP CONSTRAINT ….
• DROP TABLE ENROLL; removes the table and its schema. (Compare DELETE, next lesson, which removes rows only.) Dropping a table that others reference is rejected unless CASCADE is added.

4 · Visual

PRIMARY KEY  = UNIQUE + NOT NULL       (one per table)
UNIQUE       = no equal non-NULL values (many NULLs allowed)
CHECK(c)     = reject row only if c is FALSE   (UNKNOWN passes)
REFERENCES   = value must exist in parent, or be NULL
DROP TABLE   = rows + schema gone        DELETE FROM = rows gone, schema stays

5 · Worked Examples

Example 1 Easy

Write the table-level constraint for a composite primary key (roll, cid).

Solution

PRIMARY KEY (roll, cid). A column-level PRIMARY KEY cannot cover two columns.

Example 2 Medium

STUDENT has email VARCHAR(60) UNIQUE. Three new students have no email. Can all three be inserted?

Solution

Yes. UNIQUE allows several NULLs; NULL values are not considered equal to each other.

Example 3 GATE

CHECK (cgpa >= 6) on a table. Which inserts succeed: cgpa 5.0, 6.0, NULL?

Solution

5.0 → FALSE → rejected. 6.0 → TRUE → accepted. NULL → UNKNOWN → accepted.

8 · Connect the Dots

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · MCQ · 1 mark(s) — Constraints

A PRIMARY KEY constraint is equivalent to:

1. (A) UNIQUE
2. (B) NOT NULL
3. (C) CHECK
4. (D) UNIQUE and NOT NULL

Q2 · NAT · 1 mark(s) — UNIQUE

A column is declared UNIQUE (not NOT NULL). What is the maximum number of rows that can hold NULL in it, if the table has 10 rows?

Numerical answer: ________

Q3 · MCQ · 1 mark(s) — CHECK

Table T has CHECK (x > 0). Which insert is rejected?

1. (A) x = 1
2. (B) x = NULL
3. (C) x = 5
4. (D) x = 0

Q4 · MCQ · 1 mark(s) — Default action

If a FOREIGN KEY has no ON DELETE clause, deleting a referenced parent row:

1. (A) sets children to the default
2. (B) is rejected
3. (C) sets children to NULL
4. (D) cascades

Q5 · MSQ · 2 mark(s) — DDL

Which statements are true?

1. (A) ALTER TABLE ADD COLUMN fills existing rows with NULL unless a default is given.
2. (B) A table can have several UNIQUE constraints.
3. (C) DROP TABLE removes the table's schema.
4. (D) A composite primary key must be declared at table level.

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

SQLCREATE TABLEdata typesNOT NULLUNIQUEPRIMARY KEYFOREIGN KEY … REFERENCESCHECKDEFAULTALTER TABLEDROP TABLEcolumn-level vs table-level constraint