Skip to content

Lesson 13: SQL: Data Definition and Constraints

Creating databases and tables, data types, and table constraints

About this lesson

Course: Database Management Systems

Unit 4: Structured Query Language · 12 Lecture Hours

Lesson duration: 2 lectures

Reading: Elmasri & Navathe, Ch. 6.1, 6.2 · Murach's MySQL, Ch. 10, 11

Learning Outcomes

  1. State the history and sub-languages of SQL (DDL, DML, DCL, TCL).
  2. Choose appropriate data types for numeric, string, date and boolean data.
  3. Write CREATE DATABASE and CREATE TABLE with all constraint clauses.
  4. Distinguish column-level from table-level constraints and know when each is required.
  5. Build the complete college schema used throughout the practical component.

13.1 SQL — Origins and Structure

SQL

Structured Query Language — the standard language of relational databases. Developed at IBM's San Jose Research Laboratory in the early 1970s as SEQUEL (Structured English Query Language) for the System R prototype, based on Codd's relational model. Standardised by ANSI in 1986 and by ISO in 1987; major revisions followed as SQL-89, SQL-92, SQL:1999, SQL:2003, and onward.

SQL is declarative: you state what you want, and the optimiser decides how (Unit 1, Lesson 4). It is also relationally complete — everything expressible in relational algebra is expressible in SQL.

The sub-languages

Sub-language Purpose Statements
DDL — Data Definition Define and change the schema CREATE, ALTER, DROP, TRUNCATE, RENAME
DML — Data Manipulation Retrieve and change data SELECT, INSERT, UPDATE, DELETE
DCL — Data Control Control access GRANT, REVOKE
TCL — Transaction Control Delimit transactions COMMIT, ROLLBACK, SAVEPOINT

This lesson covers DDL. DML is Lessons 2 to 5; DCL and TCL are Lesson 6.

Terminology: SQL vs the relational model

Relational model SQL
Relation Table
Tuple Row
Attribute Column
Relation is a set Table is a multiset (bag) — duplicates possible

The bag semantics is the single most important difference, and it is why DISTINCT exists.

13.2 Data Types

Choosing the right type is a design decision, not a formality: it is the domain constraint of Unit 3 in practice.

Numeric

Type Range / meaning Use for
TINYINT −128 to 127 (or 0–255 unsigned) Small counters, flags
SMALLINT ±32,767 Seat counts, ages
INT / INTEGER ±2.1 billion Most integer data
BIGINT ±9.2 × 1018 Large IDs, counters
DECIMAL(p,s) / NUMERIC(p,s) Exact decimal: p total digits, s after the point Money, marks, any exact fraction
FLOAT, DOUBLE Approximate binary floating point Scientific measurements

Never use FLOAT for money

FLOAT and DOUBLE are binary approximations. The decimal value 0.1 has no exact binary representation, so 0.1 + 0.2 may not equal 0.3, and a sum of a thousand payments can drift by rupees.

Use DECIMAL(10,2) for currency. It stores the digits exactly and arithmetic is exact.

String

Type Meaning Notes
CHAR(n) Fixed length, space-padded to n Use when every value is the same length: CHAR(10) for a mobile number, CHAR(12) for Aadhaar
VARCHAR(n) Variable length, up to n The default choice for names, addresses
TEXT Long text, no length limit given Descriptions, comments; cannot be fully indexed
ENUM('a','b') One of a listed set (MySQL) Alternative to a CHECK constraint

CHAR vs VARCHAR

CHAR(40) storing 'Ankit' occupies 40 characters (padded with spaces); VARCHAR(40) occupies about 6 (5 + a length byte).

Use CHAR only when the length really is fixed — the padding then costs nothing and lookups are marginally faster because rows are fixed-width. Use VARCHAR for everything else.

Date and time

Type Format Example
DATE YYYY-MM-DD 2019-07-15
TIME HH:MM:SS 14:30:00
DATETIME YYYY-MM-DD HH:MM:SS 2019-07-15 14:30:00
TIMESTAMP As DATETIME, but timezone-aware and auto-updating Audit columns
YEAR YYYY 2019

Always store dates in a date type, never as a string. A VARCHAR date cannot be compared or sorted chronologically, cannot use date functions, and permits '31-02-2019'.

Boolean and other

BOOLEAN (in MySQL a synonym for TINYINT(1), where 0 is false and non-zero is true); BLOB for binary data; JSON in modern versions.

13.3 CREATE DATABASE

CREATE DATABASE IF NOT EXISTS college
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE college;
  • IF NOT EXISTS prevents an error if it is already there — useful in scripts run repeatedly.
  • CHARACTER SET utf8mb4 supports the full range of Unicode, including Indic scripts and emoji. Prefer it always.
  • COLLATE fixes the sorting and comparison rules; _ci means case-insensitive.

Related statements: SHOW DATABASES;, DROP DATABASE college;, SHOW TABLES;, DESCRIBE STUDENT;.

13.4 CREATE TABLE

General form

CREATE TABLE table_name (
    column_name  data_type  [column constraints],
    ...
    [table constraints]
);

The five constraint types

Constraint Meaning Level
NOT NULL The column may not hold NULL Column only
UNIQUE No two rows may share this value Either
PRIMARY KEY UNIQUE and NOT NULL; the identifier Either
FOREIGN KEY Must match a primary key elsewhere, or be NULL Table (mostly)
CHECK An arbitrary Boolean condition Either
DEFAULT Value used when none is supplied Column only

Column-level vs table-level

A column-level constraint is written immediately after the column definition and applies to that one column:

Rollno VARCHAR(8) PRIMARY KEY

A table-level constraint is written as a separate item after all columns, and must be used when the constraint involves more than one column:

PRIMARY KEY (Rollno, SID)

Table-level constraints may also be named, which makes them far easier to drop or diagnose later:

CONSTRAINT chk_seats CHECK (TotalSeats > 0)

Building the college schema, step by step

Table 1 — STUDENT (no dependencies, so create it first).

CREATE TABLE STUDENT (
    Rollno      VARCHAR(8)   NOT NULL,
    Name        VARCHAR(40)  NOT NULL,
    Dateofbirth DATE,
    CONSTRAINT pk_student PRIMARY KEY (Rollno)
);

Table 2 — COURSE.

CREATE TABLE COURSE (
    SID             VARCHAR(6)   NOT NULL,
    Cname           VARCHAR(40)  NOT NULL,
    TotalSeats      INT          NOT NULL,
    Duration        INT,
    Coursetype      VARCHAR(10)  NOT NULL,
    TeacherInCharge VARCHAR(40),
    CONSTRAINT pk_course      PRIMARY KEY (SID),
    CONSTRAINT uq_cname       UNIQUE (Cname),
    CONSTRAINT chk_seats      CHECK (TotalSeats > 0),
    CONSTRAINT chk_duration   CHECK (Duration BETWEEN 1 AND 6),
    CONSTRAINT chk_ctype      CHECK (Coursetype IN ('Fulltime','Parttime'))
);

Note each constraint encodes a business rule stated in the requirements — this is where the rules that the ER diagram could not draw (Unit 2, Lesson 4) finally get enforced.

Table 3 — ADMISSION (depends on both, so create it last).

CREATE TABLE ADMISSION (
    Rollno          VARCHAR(8)  NOT NULL,
    SID             VARCHAR(6)  NOT NULL,
    Dateofadmission DATE        NOT NULL,
    CONSTRAINT pk_admission PRIMARY KEY (Rollno, SID),
    CONSTRAINT fk_adm_student FOREIGN KEY (Rollno)
        REFERENCES STUDENT(Rollno) ON DELETE CASCADE  ON UPDATE CASCADE,
    CONSTRAINT fk_adm_course  FOREIGN KEY (SID)
        REFERENCES COURSE(SID)     ON DELETE RESTRICT ON UPDATE CASCADE
);

Three points:

  • The composite primary key (Rollno, SID) must be table-level, and it enforces the business rule "a student may be admitted to a given course only once".
  • ON DELETE CASCADE on Rollno and ON DELETE RESTRICT on SID — the reasoning is in Unit 3, Lesson 2.
  • Order matters: a foreign key cannot reference a table that does not yet exist.

Tables 4 and 5 — the society schema.

CREATE TABLE SOCIETY (
    SocID      VARCHAR(6)   NOT NULL,
    Socname    VARCHAR(40)  NOT NULL,
    Mentor     VARCHAR(40),
    TotalSeats INT          NOT NULL,
    CONSTRAINT pk_society  PRIMARY KEY (SocID),
    CONSTRAINT uq_socname  UNIQUE (Socname),
    CONSTRAINT chk_socseats CHECK (TotalSeats > 0)
);

CREATE TABLE ENROLLMENT (
    Rollno           VARCHAR(8) NOT NULL,
    SocID            VARCHAR(6) NOT NULL,
    Dateofenrollment DATE       NOT NULL,
    CONSTRAINT pk_enrollment PRIMARY KEY (Rollno, SocID),
    CONSTRAINT fk_enr_student FOREIGN KEY (Rollno)
        REFERENCES STUDENT(Rollno) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_enr_society FOREIGN KEY (SocID)
        REFERENCES SOCIETY(SocID)  ON DELETE CASCADE ON UPDATE CASCADE
);
   THE COLLEGE SCHEMA

   +-------------------+                    +-------------------------+
   |      STUDENT      |                    |         COURSE          |
   +-------------------+                    +-------------------------+
   | PK Rollno         |                    | PK SID                  |
   |    Name           |                    |    Cname       (UNIQUE) |
   |    Dateofbirth    |                    |    TotalSeats  (>0)     |
   +---------+---------+                    |    Duration             |
             |                              |    Coursetype  (F/P)    |
             |                              |    TeacherInCharge      |
             |                              +------------+------------+
             |                                           |
             |     +-------------------------+           |
             +-----+       ADMISSION         +-----------+
             |     +-------------------------+
             |     | PK,FK Rollno            |
             |     | PK,FK SID               |
             |     |       Dateofadmission   |
             |     +-------------------------+
             |
             |     +-------------------------+           +----------------+
             +-----+       ENROLLMENT        +-----------+    SOCIETY     |
                   +-------------------------+           +----------------+
                   | PK,FK Rollno            |           | PK SocID       |
                   | PK,FK SocID             |           |    Socname     |
                   |       Dateofenrollment  |           |    Mentor      |
                   +-------------------------+           |    TotalSeats  |
                                                         +----------------+

Figure 1.1 — The college schema used throughout the practical component.

13.5 AUTO_INCREMENT and Surrogate Keys

CREATE TABLE FEE_RECEIPT (
    ReceiptNo INT AUTO_INCREMENT PRIMARY KEY,
    Rollno    VARCHAR(8) NOT NULL,
    Amount    DECIMAL(10,2) NOT NULL CHECK (Amount > 0),
    PaidOn    DATE NOT NULL DEFAULT (CURRENT_DATE),
    FOREIGN KEY (Rollno) REFERENCES STUDENT(Rollno)
);

AUTO_INCREMENT generates the next integer automatically — a surrogate key (Unit 2, Lesson 2). Use it when no good natural key exists, and declare the natural key UNIQUE alongside.

13.6 Solved Questions

Solved Question 1

Q. Classify SQL statements into its sub-languages, giving two examples of each and stating who uses them.

A.

Sub-language Purpose Examples Typical user What it changes
DDL Define/alter the schema CREATE TABLE, ALTER TABLE, DROP, TRUNCATE DBA, designer The schema (catalog)
DML Retrieve/change data SELECT, INSERT, UPDATE, DELETE End users, programmers The instance
DCL Control access GRANT, REVOKE DBA Privileges in the catalog
TCL Delimit transactions COMMIT, ROLLBACK, SAVEPOINT Programmers Transaction boundaries

Two notes worth adding. TRUNCATE is classified as DDL even though it removes rows, because it deallocates the table's storage rather than deleting row by row, and (in most systems) cannot be rolled back. And some textbooks fold DCL into a broad definition of DDL; state the four-way classification and mention the variation.

Solved Question 2

Q. Distinguish CHAR from VARCHAR, and DECIMAL from FLOAT. When would you use each?

A.

CHAR(n) vs VARCHAR(n)

CHAR(n) VARCHAR(n)
Storage Always exactly n characters, space-padded Actual length plus 1–2 length bytes
'Ankit' in a size-40 column 40 characters ~6 bytes
Rows Fixed width — marginally faster to scan Variable width
Use for Genuinely fixed-length codes: CHAR(10) mobile, CHAR(12) Aadhaar, CHAR(1) sex Everything variable: names, addresses, titles

DECIMAL(p,s) vs FLOAT/DOUBLE

DECIMAL(p,s) FLOAT / DOUBLE
Representation Exact decimal Approximate binary
0.1 + 0.2 Exactly 0.3 May be 0.30000000000000004
Use for Money, marks, fees, any exact fraction Scientific measurements, where a tiny relative error is acceptable and range matters more

The decisive argument: a currency column summed over a million rows in FLOAT accumulates rounding error, and an account can fail to balance. DECIMAL(10,2) stores the digits themselves, so arithmetic is exact. Never store money in a floating-point type.

Solved Question 3

Q. Write the CREATE TABLE statement for ADMISSION, explaining every clause.

A.

CREATE TABLE ADMISSION (
    Rollno          VARCHAR(8) NOT NULL,
    SID             VARCHAR(6) NOT NULL,
    Dateofadmission DATE       NOT NULL,
    CONSTRAINT pk_admission PRIMARY KEY (Rollno, SID),
    CONSTRAINT fk_adm_student FOREIGN KEY (Rollno)
        REFERENCES STUDENT(Rollno) ON DELETE CASCADE  ON UPDATE CASCADE,
    CONSTRAINT fk_adm_course  FOREIGN KEY (SID)
        REFERENCES COURSE(SID)     ON DELETE RESTRICT ON UPDATE CASCADE
);
Clause Purpose
VARCHAR(8) / VARCHAR(6) Domains matching the referenced primary keys exactly — a foreign key must have the same domain as the key it references.
NOT NULL on all three The first two are part of the primary key, so entity integrity requires it; the third is a business rule.
PRIMARY KEY (Rollno, SID) A composite key, so it must be table-level. Encodes the rule "a student may be admitted to a given course only once", and enforces entity integrity on both columns.
FOREIGN KEY (Rollno) REFERENCES STUDENT Referential integrity: no admission for a non-existent student.
ON DELETE CASCADE (student) An admission is meaningless without the student, so deleting the student removes their admissions.
ON DELETE RESTRICT (course) Deleting a course with admitted students would destroy their history, so it is forbidden until they are transferred or removed.
ON UPDATE CASCADE (both) A changed key value is a renaming of the same entity, and children should follow.
CONSTRAINT <name> Naming makes constraints easy to drop (ALTER TABLE ... DROP CONSTRAINT pk_admission) and produces readable error messages.

Creation order matters: STUDENT and COURSE must exist first, since a foreign key cannot reference a non-existent table.

Solved Question 4

Q. Distinguish column-level from table-level constraints. Give a constraint that must be table-level and one that cannot be.

A. A column-level constraint is written inline after a column's data type and applies to that single column. A table-level constraint is written as a separate element after all column definitions and may reference several columns.

Must be table-level: any constraint spanning more than one column.

PRIMARY KEY (Rollno, SID)                     -- composite key
CHECK (Dateofadmission >= Dateofbirth)        -- relates two columns
UNIQUE (Cname, Coursetype)                    -- composite uniqueness

A composite primary key cannot be expressed column-by-column: writing PRIMARY KEY after both Rollno and SID would attempt to declare two primary keys, which is an error.

Cannot be table-level: NOT NULL and DEFAULT. Both are properties of a single column's storage rather than relations among columns, and SQL provides no table-level syntax for them.

Name   VARCHAR(40) NOT NULL,
Mobile CHAR(10) DEFAULT '9999999999'

Either level: single-column PRIMARY KEY, UNIQUE, CHECK and FOREIGN KEY. Prefer the table level with a name for anything you may later need to drop or diagnose, since an unnamed constraint receives a system-generated name that differs between systems.

Solved Question 5

Q. Write DDL for a LIBRARY database: BOOK(ISBN, Title, Publisher, Price), MEMBER(MemberID, Name, Email, JoinDate), ISSUE(ISBN, MemberID, IssueDate, DueDate, Returned). Include every sensible constraint.

A.

CREATE DATABASE IF NOT EXISTS library
    CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE library;

CREATE TABLE BOOK (
    ISBN      CHAR(13)      NOT NULL,
    Title     VARCHAR(100)  NOT NULL,
    Publisher VARCHAR(60),
    Price     DECIMAL(8,2)  NOT NULL,
    CONSTRAINT pk_book    PRIMARY KEY (ISBN),
    CONSTRAINT chk_price  CHECK (Price > 0)
);

CREATE TABLE MEMBER (
    MemberID INT AUTO_INCREMENT,
    Name     VARCHAR(40)  NOT NULL,
    Email    VARCHAR(60)  NOT NULL,
    JoinDate DATE         NOT NULL DEFAULT (CURRENT_DATE),
    CONSTRAINT pk_member PRIMARY KEY (MemberID),
    CONSTRAINT uq_email  UNIQUE (Email)
);

CREATE TABLE ISSUE (
    ISBN      CHAR(13)  NOT NULL,
    MemberID  INT       NOT NULL,
    IssueDate DATE      NOT NULL,
    DueDate   DATE      NOT NULL,
    Returned  BOOLEAN   NOT NULL DEFAULT FALSE,
    CONSTRAINT pk_issue PRIMARY KEY (ISBN, MemberID, IssueDate),
    CONSTRAINT fk_iss_book   FOREIGN KEY (ISBN)
        REFERENCES BOOK(ISBN)     ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_iss_member FOREIGN KEY (MemberID)
        REFERENCES MEMBER(MemberID) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT chk_dates CHECK (DueDate > IssueDate)
);

Design decisions worth stating:

  • ISBN is CHAR(13) because an ISBN-13 is exactly 13 characters — a genuinely fixed length.
  • MemberID is a surrogate key with AUTO_INCREMENT, because no stable natural key exists; Email is declared UNIQUE as the natural alternate key.
  • Price uses DECIMAL(8,2), never FLOAT.
  • ISSUE's primary key includes IssueDate, because the same member may borrow the same book again later — without the date, the second loan would violate the key.
  • ON DELETE RESTRICT on both foreign keys: an issue record is an audit trail, and deleting a book or a member must not silently erase the borrowing history.
  • CHECK (DueDate > IssueDate) is a table-level constraint because it relates two columns.

Solved Question 6

Q. What is AUTO_INCREMENT? When should a surrogate key be preferred to a natural key, and what should accompany it?

A. AUTO_INCREMENT (MySQL; SERIAL in PostgreSQL, IDENTITY in SQL Server) instructs the DBMS to generate the next integer automatically whenever a row is inserted without a value for that column. It is the standard way of creating a surrogate key — a system-generated, meaningless identifier.

Prefer a surrogate key when:

  1. No natural candidate key is stable. Email addresses, phone numbers and names all change; a primary key that changes forces cascading updates through every referencing table.
  2. No natural key is always available at insertion time. A patient arriving unconscious has no recorded identity document, yet must be admitted immediately.
  3. The only natural key is long or composite. A four-column composite key is copied into every child table, bloating indexes and slowing joins.
  4. The natural key is privacy-sensitive. Using a national identity number as a primary key propagates it into every referencing table and every index.

Prefer a natural key when one exists that is stable, short, always available and non-sensitive — Rollno for STUDENT, ISBN for a book title. A natural key is meaningful to users and often removes the need for a join.

What must accompany a surrogate key: a UNIQUE constraint on the natural key. A surrogate guarantees that every row is distinct, but not that every real-world entity appears only once — without UNIQUE (Email), the same person can be inserted twice with two different MemberIDs, and the surrogate happily calls them different members.

13.7 Exercises

A. Short answer

  1. What does SQL stand for, and what was it originally called?
  2. Name the four sub-languages of SQL with two statements each.
  3. Why should money never be stored in FLOAT?
  4. Distinguish CHAR(10) from VARCHAR(10).
  5. Name two constraints that cannot be written at table level.
  6. Why must STUDENT be created before ADMISSION?
  7. What is the advantage of naming a constraint?

B. Write the SQL

  1. Create a database hospital with UTF-8 support and select it.
  2. Create WARD(WardNo, Name, Capacity) where capacity is between 1 and 100.
  3. Create PATIENT(RegNo, Name, Dateofbirth, BloodGroup) where blood group is one of the eight valid values and RegNo is auto-generated.
  4. Create DOCTOR(EmpCode, Name, Specialisation, Salary) with salary positive and stored exactly.
  5. Create TREATS(EmpCode, RegNo, FirstConsult, Fee) with the appropriate composite key, both foreign keys, sensible referential actions, and a check that the fee is not negative.
  6. Create the complete college schema (all five tables) from scratch, in the correct order.
  7. Create EMPLOYEE(Ssn, Name, Salary, SuperSsn, Dno) where SuperSsn references the same table.

C. Analytical

  1. For each column below, choose a data type and justify: a. A student's roll number, always one letter plus four digits. b. A course fee in rupees and paise. c. A student's photograph. d. The date and time an exam starts. e. Whether a fee has been paid. f. A 500-word course description. g. A mobile number.
  2. Explain what happens, and why, for each: a. CREATE TABLE ADMISSION before STUDENT exists. b. Two PRIMARY KEY clauses on the same table. c. A FOREIGN KEY whose column is VARCHAR(20) referencing a VARCHAR(8) primary key. d. CHECK (TotalSeats > 0) when a row with TotalSeats = 0 already exists.
  3. A designer writes Coursetype VARCHAR(10) with no CHECK. List three specific ways the data can become corrupt, and give two different SQL mechanisms to prevent it.
  4. Compare ENUM('Fulltime','Parttime') with VARCHAR(10) CHECK (Coursetype IN (...)). Give one advantage of each.
  5. Write the DDL for a schema of your own design for a canteen (items, orders, order lines, payments), with full justification of every key and constraint.

D. Objective — True or False with reason

  1. TRUNCATE is a DML statement.
  2. A table may have several UNIQUE constraints.
  3. A CHECK constraint may reference a column of another table.
  4. PRIMARY KEY implies NOT NULL.
  5. VARCHAR(40) always occupies 40 bytes.
  6. A foreign key must have the same data type as the key it references.

Answers to Section D

  1. False. It is DDL — it deallocates the table's storage rather than deleting rows individually, and generally cannot be rolled back.
  2. True. Only the primary key is limited to one; any number of alternate keys may be declared UNIQUE.
  3. False. A CHECK may reference only columns of its own row. Cross-table rules need a trigger or an assertion.
  4. True. By the entity integrity constraint, a primary key may never be NULL, so NOT NULL is implied.
  5. False. It occupies the actual length plus 1–2 length bytes. CHAR(40) occupies 40.
  6. True in effect — it must have the same domain; most systems reject a mismatched type or length outright.

Lesson Summary

  • SQL comprises DDL, DML, DCL, TCL; this lesson covered DDL.
  • Choose types deliberately: DECIMAL for money (never FLOAT), VARCHAR unless the length is genuinely fixed, real DATE types for dates.
  • CREATE TABLE carries the constraints that enforce the business rules the ER diagram could not draw: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.
  • Constraints spanning several columns must be table-level; NOT NULL and DEFAULT must be column-level. Name your constraints.
  • Create parent tables before child tables.
  • Use AUTO_INCREMENT surrogate keys when no stable natural key exists — and always add UNIQUE on the natural key alongside.