Core CS · DBMS

Your first constraint is the column type

Q: What is the difference between DDL and DML?

DDL defines structure and DML changes rows. CREATE , ALTER , DROP and TRUNCATE are DDL; INSERT , UPDATE and DELETE are DML. The split is worth knowing because it predicts behaviour rather than merely grouping keywords: DML is logged row by row and always rolls back, while DDL is not logged per row and on Oracle and MySQL commits itself as it runs.

Q: Would you declare a six-character roll number as CHAR(6) or VARCHAR(6)?

CHAR(6) , because every roll number really is six characters, but I would not rely on the type to enforce that. CHAR(n) rejects a longer value and blank-pads a shorter one to reach n, so it stores “exactly six” without ever checking it. For a name or a city the answer is VARCHAR : the values genuinely vary in length and the padding would be noise carried in every row.

Q: DATE, TIME or TIMESTAMP for when a student joined the college?

DATE , because joining happens on a day and there is no meaningful time of day to record. Choosing TIMESTAMP forces you to invent one, and every later comparison has to remember it is there: a DATE compared against a timestamp is promoted to midnight, so equality fails for every row entered later in the day. The exception to watch is Oracle, whose DATE already carries a time down to the second.

One CREATE TABLE and three student rows, read column by column. You will choose between CHAR and VARCHAR, learn why money is never FLOAT, change the table with ALTER, and then run DELETE, TRUNCATE and DROP against the same three rows to see what each one actually takes away.

Empty the same table three ways →

A column type is not a storage hint. It is the first constraint on the table, checked before any other rule, on every write, for every program that ever connects.

01 The idea

SQL is four sublanguages wearing one name

People say “I know SQL” and mean SELECT. SQL is a family of statement kinds, split by what the statement acts on, and the split is not academic tidiness. It predicts the two things interviewers push on: whether a statement can be undone, and whether the database writes a log record for every row it touches.

DDL defines structure: CREATE, ALTER, DROP, TRUNCATE. DML changes rows: INSERT, UPDATE, DELETE. DCL hands out permission: GRANT, REVOKE. TCL draws the boundary a group of changes lives inside: COMMIT, ROLLBACK, SAVEPOINT. That is the whole map, and TRUNCATE sitting under DDL rather than DML is the single classification that answers the most asked question in this lesson.

This lesson lives almost entirely in DDL, and specifically in the part of DDL that students skim: the type beside each column name. VARCHAR(60) is a rule that rejects the sixty-first character. NUMERIC(9,2) is a rule that refuses to lose the paise. NOT NULL is a rule that refuses a blank. None of them is advice to the storage engine. Each one is a check that runs before a row is allowed to exist, and it runs for the web app, the nightly import, and the UPDATE somebody types by hand at 2am.

Write the type once, in DDL, and it constrains every writer forever. Write the same rule in application code and it constrains that application. That is the whole reason the type is a design decision and not paperwork.

DDLData Definition Language: the statements that change structure rather than rows. Whether a DDL statement can be rolled back is the one place vendors genuinely split. PostgreSQL and SQL Server run it inside your transaction; Oracle and MySQL commit before and after each DDL statement, so there is nothing left to undo.

Precision and scaleIn NUMERIC(9,2) the 9 is precision, the total count of significant digits, and the 2 is scale, how many of them sit after the decimal point. Seven digits are left in front, so the largest value it holds is 9999999.99, exactly.

NULLA marker meaning no value was recorded, not zero and not an empty string. It is equal to nothing, including another NULL, so city = NULL is never true and city IS NULL is the only test that works.

02 Worked example

One student table, and what happens to one insert

Here is the table the rest of this lesson uses. Read it as nine decisions, not nine lines. Every type was chosen against one question: what values must this column accept, and what must it refuse?

CREATE TABLE student (  sid        INTEGER       GENERATED BY DEFAULT AS IDENTITY,  -- counter lives outside the rows  roll_no    CHAR(6)       NOT NULL,   -- every roll number is 6 characters  name       VARCHAR(60)   NOT NULL,   -- up to 60; stores only what you put in  city       VARCHAR(40),              -- nullable: "not recorded" is a real state  fee_due    NUMERIC(9,2)  NOT NULL DEFAULT 0,   -- money: exact, never FLOAT  joined_on  DATE          NOT NULL,   -- a calendar day, no time of day  left_on    DATE,                    -- still enrolled = no value yet  hosteller  BOOLEAN       NOT NULL DEFAULT FALSE,  photo      BLOB,                    -- large object, kept away from the row  CONSTRAINT student_pk       PRIMARY KEY (sid),  CONSTRAINT student_roll_uq  UNIQUE (roll_no),  CONSTRAINT student_fee_ck   CHECK (fee_due >= 0),  CONSTRAINT student_dates_ck CHECK (left_on IS NULL OR left_on >= joined_on));

Lines 2 to 10 declare the columns, and every rule written beside a column there is a column constraint: it sees that one column and nothing else. Lines 11 to 14 are table constraints: they are written after the columns because they are allowed to mention more than one of them. Line 14 is the reason that distinction exists. “You cannot leave before you joined” needs left_on and joined_on at the same time, so it cannot be written beside either column. Everything else on lines 11 to 13 could have been a column constraint; naming them explicitly is a choice you will be glad of in section 03.

Now follow one insert through. A clerk types a roll number and drops a zero, so '21CS1' arrives where '21CS01' was meant, five characters into a CHAR(6) column.

INSERT INTO student (roll_no, name, city, joined_on, hosteller)VALUES ('21CS1', 'Asha', 'Bengaluru', DATE '2024-07-15', TRUE);

1 · Statement arrivesfive columns named, four left out: sid, fee_due, left_on and photo

→

2 · Types applied'21CS1' is 5 characters into CHAR(6), so it is accepted and blank-padded to '21CS1 '. No warning

→

3 · Column rulessid takes identity value 1, fee_due takes DEFAULT 0, left_on and photo become NULL, and all five NOT NULL columns end up with a value

→

4 · Table constraintsPRIMARY KEY, UNIQUE and both CHECKs run on the finished row, because student_dates_ck needs two columns at once

→

5 · All or nothingone failure rejects the whole statement; there is no half-inserted row to clean up

Node 2 is the one to remember. CHAR(6) did not reject the short value and it did not warn about it. It stored '21CS1 ', six characters, one of them a space nobody typed. Node 4 is the second: a table constraint reads a row, a column constraint reads a value, and no amount of care in the column list can make a two-column rule work as a one-column rule.

CHAR(6) does not mean exactly six characters. It means at most six, stored as six. If “exactly six” is the rule, only a CHECK can say so.

Fix that row and add two more. These three rows are the whole dataset for the rest of the lesson. Note the identity counter: three inserts have been taken, so the next sid handed out is 4.

sid	roll_no	name	city	fee_due	joined_on	left_on
1	`21CS01`	Asha	Bengaluru	`12500.00`	`2024-07-15`	`NULL`
2	`21CS02`	Ravi	Hyderabad	`0.00`	`2024-07-15`	`NULL`
3	`21CS03`	Meera	Pune	`7250.50`	`2024-08-01`	`NULL`

Three facts about this table you can check by hand, and each one is a question somebody has been asked in an interview. SELECT COUNT(*) FROM student is 3. SELECT COUNT(left_on) FROM student is 0, because COUNT of a column counts values and there are none. SELECT SUM(fee_due) FROM student is 19750.50, exactly, because the column is NUMERIC and not FLOAT. Section 03 is those nine type decisions written as a table you can answer from.

03 Mechanics

Nine type families, and the trap in each

One row per family. The third column is the one to learn, because “what it holds” is in every textbook and “when to declare it” is what a schema review actually argues about. The fourth column is what gets you caught.

Type	What it holds	Declare it when	The trap
`CHAR(n)`	Exactly n characters. A shorter value is padded with spaces to reach n.	Every value genuinely has n characters: a 6-character roll number, a 2-letter country code, a single-character flag.	`It pads instead of rejecting. Standard SQL pads the shorter side with spaces before comparing two character strings, so = ignores trailing blanks; LIKE is defined without that padding and does not. PostgreSQL is the exception on the LIKE side, because character(n) is converted to text and loses the padding first; Oracle and SQL Server keep it.`
`VARCHAR(n)`	Up to n characters. Stores what you gave it, plus a small length header.	Almost every string: names, cities, addresses, email. This is the default answer.	`n is a constraint, not a reservation, so being generous costs nothing; but a value over n is rejected, not trimmed. MySQL outside strict mode truncates it with a warning instead, which is the worse of the two failures.`
`SMALLINT INTEGER BIGINT`	Whole numbers in 2, 4 and 8 bytes. Roughly ±32 thousand, ±2.1 billion, ±9.2 quintillion.	Counters, surrogate keys, quantities, anything you will do arithmetic on and never see a fraction of.	`INTEGER stops at 2,147,483,647. A key column on a table that will pass two billion rows needs BIGINT from the start, because widening it later rewrites every row and every index on it.`
`NUMERIC(p,s) DECIMAL(p,s)`	Exact decimal: p significant digits, s of them after the point. Every engine here treats the two spellings as one type; the standard only lets DECIMAL give you more precision than you asked for.	Money, tax, marks, quantities, anything a human will audit or add up and expect to match a printed total.	`Exact arithmetic is slower than integer or float arithmetic. And p and s are two separate limits: more decimals than s are rounded away without a word, while too many digits in front of the point are rejected outright.`
`REAL DOUBLE PRECISION FLOAT`	Binary floating point. Approximate, wide range, fast.	Measurements from an instrument, scientific values, coordinates, anything where the last bit was never meaningful.	`0.1 has no exact binary representation. Add 0.10 to a DOUBLE PRECISION 12500.00 ten times and you get 12501.000000000004. Never money, never anything compared with =.`
`DATE`	A calendar day. Year, month, day, no time of day.	joined_on, date of birth, invoice date: things that happen on a day rather than at an instant.	`Compared against a timestamp it is promoted to midnight, so joined_on = TIMESTAMP '2024-07-15 09:30:00' is false. Oracle's DATE is the exception that catches everybody: it carries a time down to the second.`
`TIME TIMESTAMP TIMESTAMP WITH TIME ZONE`	A time of day; a day plus a time; and the same with a zone offset recorded.	Event times, audit columns, anything you will subtract to get a duration. Use the zoned form for anything a second server or a second country will read.	`Plain TIMESTAMP is a wall-clock reading with no location attached. Two servers in two zones write the same instant as two different values, and nothing in the column says which was which.`
`BOOLEAN`	TRUE, FALSE, or NULL. Three states, not two.	Flags: hosteller, is_active, fee_waived. Add NOT NULL DEFAULT unless “unknown” is a state you mean.	`WHERE NOT hosteller silently skips every row where hosteller is NULL, because NOT unknown is unknown. Oracle has no BOOLEAN column type before 23c, so the convention there is CHAR(1) with CHECK (flag IN ('Y','N')); MySQL's BOOLEAN is an alias for TINYINT(1) and will store 7.`
`BLOB CLOB`	Large binary and large character objects, held away from the row itself.	A scanned marksheet, a PDF, a long free-text abstract. PostgreSQL spells these BYTEA and TEXT; SQL Server uses VARBINARY(MAX) and VARCHAR(MAX).	`You cannot usefully index one or compare two, and SELECT * drags every megabyte across the network. Most teams store a key or path in the table and keep the bytes in object storage.`

Money is never FLOAT. Adding 0.10 to a DOUBLE PRECISION column holding 12500.00 ten times gives 12501.000000000004, and no error is raised at any point. NUMERIC(9,2) gives 12501.00 and gives it every time. You pay for that in speed, and it is the cheapest thing you will ever buy.

NULL is not a value, so it does not behave like one. On our three rows COUNT(*) is 3 and COUNT(left_on) is 0. WHERE city = NULL returns nothing, because the comparison is unknown and a WHERE clause keeps only what is true. And WHERE sid NOT IN (1, NULL) returns no rows at all, not the two rows you expected: sid <> NULL is unknown for every row, so nothing is ever true. Declaring NOT NULL wherever a value is genuinely required is how you never meet any of this.

The other half of DDL is changing a table that already has rows in it. ALTER TABLE adds, retypes and drops both columns and constraints, and every one of those statements is checked against the three rows already there.

ALTER TABLE student ADD COLUMN email VARCHAR(120);ALTER TABLE student ALTER COLUMN email SET NOT NULL;      -- fails: 3 rows hold NULLUPDATE student SET email = roll_no || '@college.edu';      -- backfill all 3ALTER TABLE student ALTER COLUMN email SET NOT NULL;      -- now it passes ALTER TABLE student ALTER COLUMN city SET DATA TYPE VARCHAR(80);  -- widen: nothing to checkALTER TABLE student ALTER COLUMN city SET DATA TYPE VARCHAR(6);   -- fails: 'Bengaluru' is 9 ALTER TABLE student ADD CONSTRAINT student_fee_cap CHECK (fee_due <= 200000);ALTER TABLE student DROP CONSTRAINT student_fee_cap;  -- by the name you gave itALTER TABLE student DROP COLUMN photo;                -- the column and its bytes

Adding a column is cheap. Adding NOT NULL is not. The three existing rows have no email, so line 1 gives all three a NULL and line 2 has nothing to work with. Either give the new column a DEFAULT so the rows get a value, or do it in three statements as above: add, backfill, tighten. Line 2 and line 4 are the same statement with a different table underneath them.

Widening a type is free. Narrowing is a question about your data. Every existing value already satisfies a wider rule, so line 6 has nothing to verify. Line 7 must check all three rows and 'Bengaluru' is nine characters, so the whole statement is refused and the column keeps its old type. Nothing partial happens.

A constraint added later is validated against the rows already there. Otherwise a table would be free to hold data its own schema forbids. That is also why line 9 names the constraint: line 10 needs that name, and an engine-generated one like student_chk_1 is not something you want a migration script to depend on. Vendor spelling differs more here than anywhere else in SQL: MySQL writes MODIFY COLUMN and Oracle writes MODIFY where the standard writes ALTER COLUMN ... SET DATA TYPE.

DROP COLUMN takes the data with it, and line 3 hides a trap. Our three roll numbers are exactly six characters, so the concatenation on line 3 is clean. Had the '21CS1' row from section 02 survived, its email would be '21CS1 @college.edu' in Oracle and SQL Server, with the pad space riding along. One bad character in one column becomes one bad address in another, months later, and nothing failed.

05 Cheat sheet

DELETE, TRUNCATE and DROP on one card

This comparison is asked in almost every DBMS interview, and almost every candidate answers the first two rows and stops. The rows that separate people are the log, the counter and the rollback.

Question	DELETE	TRUNCATE	DROP
Sublanguage	`DML`	`DDL`	`DDL`
What it removes	The rows a WHERE selects, or all of them	Every row, always	`rows, columns, constraints, indexes, triggers and grants`
Takes a WHERE clause	`yes`	`no`	`no`
Logged per row	`yes, one record per row`	`no, page deallocations only`	`no`
Cost on a million rows	`slowest by a wide margin`	`fast, close to constant`	`fast, close to constant`
Row triggers fire	`yes`	`no`	`no`
Identity counter	`untouched`	`back to the seed on MySQL and SQL Server; PostgreSQL only with RESTART IDENTITY, Oracle not at all`	`gone with the table`
ROLLBACK undoes it	`yes, always`	`PostgreSQL and SQL Server yes; Oracle and MySQL no`	`same split, and Oracle keeps a recycle bin`
The table afterwards	Exists, emptier	Exists, empty, fully defined	`does not exist; the name no longer resolves`

The four sublanguagesDDL defines: CREATE, ALTER, DROP, TRUNCATE, RENAME. DML changes rows: INSERT, UPDATE, DELETE, MERGE. DCL grants: GRANT, REVOKE. TCL bounds a transaction: COMMIT, ROLLBACK, SAVEPOINT. ANSI files SELECT under DML; most Indian textbooks split it out as DQL. Either is accepted if you say which convention you are using.

Naming, and why quotes are a trapLowercase with underscores, singular table names, and a constraint name you chose rather than one the engine invented. Unquoted identifiers are folded to a single case: PostgreSQL folds down to student, Oracle folds up to STUDENT. Write "Student" with double quotes once and you have to write it that way in every statement, in every tool, forever.

The data dictionaryThe engine stores your schema as rows in its own tables, so you can query the definition back instead of trusting a document. SELECT column_name, data_type, is_nullable FROM information_schema.columns WHERE table_name = 'student'; works on PostgreSQL, MySQL and SQL Server. Oracle spells it USER_TAB_COLUMNS and USER_CONSTRAINTS. This is also how DROP is so fast: it is mostly an edit to these rows.

06 Where & why

The same nine types, spelled four ways

There is no such thing as writing SQL in general. You write it against an engine, and the four below disagree on exactly the points this lesson is about: what a type is called, whether it is enforced, and whether DDL can be undone. Knowing which one you are on is a real skill, and interviewers test it by asking the same question twice with a different product name.

PostgreSQL

DDL runs inside your transaction

BEGIN; ALTER TABLE ...; ROLLBACK; puts the schema back exactly as it was, which is why migration tools can wrap a whole release in one transaction and abort it cleanly on failure. Its TEXT type has no length limit and costs the same as VARCHAR, so most PostgreSQL schemas use TEXT plus a CHECK when a limit is a genuine rule rather than a habit. SERIAL is the older spelling of an identity column and is still everywhere in existing code.

MySQL · MariaDB

Implicit commit, and a mode you must check

Every DDL statement commits, so a ROLLBACK after TRUNCATE or DROP does nothing at all. An over-length VARCHAR is an error in the default strict mode and a silent truncation with a warning outside it, which is the most damaging default in this lesson because your data is wrong and nothing said so. BOOLEAN is an alias for TINYINT(1), and AUTO_INCREMENT replaces the standard identity syntax.

Oracle

Different words for the same ideas

VARCHAR2 rather than VARCHAR, NUMBER(p,s) rather than NUMERIC(p,s), and no BOOLEAN column type before 23c, so flags are CHAR(1) with a CHECK. Its DATE carries a time down to the second, which makes it closer to another engine's TIMESTAMP(0) and is the most common cross-engine bug in this lesson. A dropped table lands in the recycle bin, so FLASHBACK TABLE student TO BEFORE DROP is a real recovery route no other engine here offers.

SQLite

The types are suggestions

A column declared VARCHAR(6) will hold a 200-character string without complaint, because SQLite uses type affinity rather than enforcement and any column except an INTEGER PRIMARY KEY accepts a value of any type. STRICT tables, from version 3.37, opt back into checking. It is the one engine where testing your length and type limits locally proves nothing whatsoever about production, and it is also the one most students have installed.

The type is the one part of a schema you cannot fix cheaply later. ALTER TABLE will widen a column, add one, and add a constraint. It will not recover the paise a FLOAT column has already rounded away, or the characters a truncating VARCHAR already dropped. Get the types right at CREATE TABLE and everything else in the schema stays editable.

07 Interview questions

What they actually ask

The DELETE, TRUNCATE and DROP question comes up so often that answering it well is worth more than any other single fact in this module. Expect it early, expect a follow-up about rollback, and expect to be asked which type you would pick for a named column.

What is the difference between DDL and DML?

DDL defines structure and DML changes rows. CREATE, ALTER, DROP and TRUNCATE are DDL; INSERT, UPDATE and DELETE are DML. The split is worth knowing because it predicts behaviour rather than merely grouping keywords: DML is logged row by row and always rolls back, while DDL is not logged per row and on Oracle and MySQL commits itself as it runs.

Would you declare a six-character roll number as CHAR(6) or VARCHAR(6)?

CHAR(6), because every roll number really is six characters, but I would not rely on the type to enforce that. CHAR(n) rejects a longer value and blank-pads a shorter one to reach n, so it stores “exactly six” without ever checking it. For a name or a city the answer is VARCHAR: the values genuinely vary in length and the padding would be noise carried in every row.

Why must money never be stored as FLOAT?

Because floating point is binary and a value like 0.10 has no exact binary representation, so what is stored is an approximation and the error accumulates through arithmetic. Nothing raises an error while this happens, which is what makes it dangerous rather than merely wrong. NUMERIC(9,2) stores exact decimal, so a total matches the printed receipt every time, and the price you pay is slower arithmetic.

What is the difference between DELETE, TRUNCATE and DROP?

DELETE is DML: it removes the rows a WHERE selects, writes a log record per row, fires row-level triggers, and always rolls back. TRUNCATE is DDL: it removes every row by releasing their storage instead of visiting them, so it takes no WHERE, fires no row triggers, is far faster on a large table, and on MySQL and SQL Server puts the identity counter back to its seed. DROP is DDL and removes the table itself, so the columns, constraints, indexes, triggers and grants go with the rows and the name stops resolving.

Can you roll back a TRUNCATE?

It depends on the engine, and naming which is the answer rather than a dodge. PostgreSQL and SQL Server run TRUNCATE inside your transaction, so ROLLBACK brings every row back. Oracle and MySQL issue an implicit COMMIT as part of running any DDL statement, so the rows were permanent before you typed ROLLBACK and there is nothing left to undo. The same split applies to DROP.

DELETE FROM student and TRUNCATE TABLE student both leave zero rows. You then insert one row. What sid does it get?

After the DELETE it gets 4, because the identity counter was untouched and had already handed out 1, 2 and 3. After the TRUNCATE it gets 1, because the counter went back to its seed. That is the sharpest way to show the two statements are not the same operation at different speeds. Then name the engines: MySQL and SQL Server reset it as part of the statement, PostgreSQL only if you write TRUNCATE student RESTART IDENTITY, and Oracle does not reset it at all.

What is NULL, and why is city = NULL never true?

NULL marks the absence of a value, not zero and not an empty string, so any comparison against it is unknown rather than true or false. A WHERE clause keeps only the rows where the condition is true, so city = NULL returns nothing and city IS NULL is the only test that works. Two follow-ups usually come with it: COUNT(*) counts rows while COUNT(col) skips NULLs, and NOT IN against a subquery holding a single NULL returns no rows at all.

What is the difference between a column constraint and a table constraint?

A column constraint is written beside one column and can only see that column: NOT NULL, DEFAULT, a single-column CHECK. A table constraint is written after the column list and may mention several columns, which is the only way to express a rule about two of them at once, or a composite PRIMARY KEY or UNIQUE. Anything expressible either way is identical to the engine, so the real reason to write it as a named table constraint is that you choose the name instead of the engine choosing it.

Your ALTER TABLE ... ADD CONSTRAINT failed on a table you had not touched. Why?

Because a constraint added later is validated against every row already in the table, so one row that violates it aborts the whole statement and no constraint is created. That is deliberate: otherwise a table could hold data its own schema forbids. Either fix the offending rows first, or add it as NOT VALID in PostgreSQL or ENABLE NOVALIDATE in Oracle so it applies only to new writes, which is a decision to keep the bad rows rather than a way around the check.

DATE, TIME or TIMESTAMP for when a student joined the college?

DATE, because joining happens on a day and there is no meaningful time of day to record. Choosing TIMESTAMP forces you to invent one, and every later comparison has to remember it is there: a DATE compared against a timestamp is promoted to midnight, so equality fails for every row entered later in the day. The exception to watch is Oracle, whose DATE already carries a time down to the second.

What do the two numbers in NUMERIC(9,2) mean, and what is the largest value it can hold?

Hard

You are on Oracle, where a TRUNCATE cannot be rolled back and the recycle bin does not cover it. Design a scheme, using only statements from this lesson, that lets a team empty a two-million-row table as fast as TRUNCATE does and still change their mind for 24 hours. Give the statements in order, and name two things your scheme costs that a plain TRUNCATE does not.

Follow-up

TRUNCATE is fast precisely because it never records the rows, so any undo has to record them somewhere, and copying two million rows is exactly the cost you were trying to avoid. Pay for the undo once, at the level of whole tables, rather than once per row removed.

Show the hint

You are allowed to create a table and to rename one, and nothing in this lesson says the rows have to leave the table they are already sitting in.