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
- State the history and sub-languages of SQL (DDL, DML, DCL, TCL).
- Choose appropriate data types for numeric, string, date and boolean data.
- Write
CREATE DATABASEandCREATE TABLEwith all constraint clauses. - Distinguish column-level from table-level constraints and know when each is required.
- 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 EXISTSprevents an error if it is already there — useful in scripts run repeatedly.CHARACTER SET utf8mb4supports the full range of Unicode, including Indic scripts and emoji. Prefer it always.COLLATEfixes the sorting and comparison rules;_cimeans 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 CASCADEonRollnoandON DELETE RESTRICTonSID— 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:
ISBNisCHAR(13)because an ISBN-13 is exactly 13 characters — a genuinely fixed length.MemberIDis a surrogate key withAUTO_INCREMENT, because no stable natural key exists;Emailis declaredUNIQUEas the natural alternate key.PriceusesDECIMAL(8,2), neverFLOAT.ISSUE's primary key includesIssueDate, because the same member may borrow the same book again later — without the date, the second loan would violate the key.ON DELETE RESTRICTon 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:
- 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.
- No natural key is always available at insertion time. A patient arriving unconscious has no recorded identity document, yet must be admitted immediately.
- The only natural key is long or composite. A four-column composite key is copied into every child table, bloating indexes and slowing joins.
- 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
- What does SQL stand for, and what was it originally called?
- Name the four sub-languages of SQL with two statements each.
- Why should money never be stored in
FLOAT? - Distinguish
CHAR(10)fromVARCHAR(10). - Name two constraints that cannot be written at table level.
- Why must
STUDENTbe created beforeADMISSION? - What is the advantage of naming a constraint?
B. Write the SQL
- Create a database
hospitalwith UTF-8 support and select it. - Create
WARD(WardNo, Name, Capacity)where capacity is between 1 and 100. - Create
PATIENT(RegNo, Name, Dateofbirth, BloodGroup)where blood group is one of the eight valid values andRegNois auto-generated. - Create
DOCTOR(EmpCode, Name, Specialisation, Salary)with salary positive and stored exactly. - 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. - Create the complete college schema (all five tables) from scratch, in the correct order.
- Create
EMPLOYEE(Ssn, Name, Salary, SuperSsn, Dno)whereSuperSsnreferences the same table.
C. Analytical
- 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.
- Explain what happens, and why, for each:
a.
CREATE TABLE ADMISSIONbeforeSTUDENTexists. b. TwoPRIMARY KEYclauses on the same table. c. AFOREIGN KEYwhose column isVARCHAR(20)referencing aVARCHAR(8)primary key. d.CHECK (TotalSeats > 0)when a row withTotalSeats = 0already exists. - A designer writes
Coursetype VARCHAR(10)with noCHECK. List three specific ways the data can become corrupt, and give two different SQL mechanisms to prevent it. - Compare
ENUM('Fulltime','Parttime')withVARCHAR(10) CHECK (Coursetype IN (...)). Give one advantage of each. - 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
TRUNCATEis a DML statement.- A table may have several
UNIQUEconstraints. - A
CHECKconstraint may reference a column of another table. PRIMARY KEYimpliesNOT NULL.VARCHAR(40)always occupies 40 bytes.- A foreign key must have the same data type as the key it references.
Answers to Section D
- False. It is DDL — it deallocates the table's storage rather than deleting rows individually, and generally cannot be rolled back.
- True. Only the primary key is limited to one; any number of alternate keys may be declared
UNIQUE. - False. A
CHECKmay reference only columns of its own row. Cross-table rules need a trigger or an assertion. - True. By the entity integrity constraint, a primary key may never be NULL, so
NOT NULLis implied. - False. It occupies the actual length plus 1–2 length bytes.
CHAR(40)occupies 40. - 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:
DECIMALfor money (neverFLOAT),VARCHARunless the length is genuinely fixed, realDATEtypes for dates. CREATE TABLEcarries 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 NULLandDEFAULTmust be column-level. Name your constraints. - Create parent tables before child tables.
- Use
AUTO_INCREMENTsurrogate keys when no stable natural key exists — and always addUNIQUEon the natural key alongside.