SQL CREATE, ALTER and DROP Tables

So far you have used tables that already existed. Now you will build them yourself.

CREATE DATABASE

A database holds a group of related tables. Create one for the institute:

Example

sql

CREATE DATABASE institute;

In MySQL you then choose it with USE institute;. In PostgreSQL you connect to it (in psql: \c institute).

CREATE TABLE

Example

sql

CREATE TABLE table_name (
  column1 datatype constraints,
  column2 datatype constraints
);

Each column needs a name and a data type, which says what kind of value it can hold.

Common Data Types

Type	Holds	Example
`INT`	Whole numbers	`20`
`DECIMAL(10, 2)`	Exact numbers with decimals, good for money	`15000.00`
`VARCHAR(n)`	Text up to n characters	`'Ali Khan'`
`TEXT`	Long text	A course description
`DATE`	A date	`'2026-10-15'`
`BOOLEAN`	true or false	`TRUE`

Names vary a little between databases, but these work in MySQL and PostgreSQL.

Constraints

Constraints are rules that protect your data. The database refuses any insert or update that breaks them.

Constraint	Rule
`NOT NULL`	The column must have a value
`UNIQUE`	No two rows may have the same value
`PRIMARY KEY`	Identifies each row: unique and not null
`FOREIGN KEY`	Must match a value in another table
`CHECK`	The value must pass a condition
`DEFAULT`	The value used when none is given

The Sample Tables

Here is how the two tables of this tutorial are created:

Example

sql

CREATE TABLE courses (
  id INT PRIMARY KEY,
  title VARCHAR(100) NOT NULL UNIQUE,
  duration_months INT NOT NULL CHECK (duration_months > 0)
);

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  city VARCHAR(50) DEFAULT 'Karachi',
  age INT CHECK (age >= 15),
  marks INT,
  course_id INT,
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

courses must be created first, because students refers to it.

Constraints in Action

The CHECK rule stops a course with zero months:

Example

sql

INSERT INTO courses (id, title, duration_months)
VALUES (9, 'Test Course', 0);

Output

ERROR: check constraint violated (duration_months > 0)

The foreign key stops a student joining a course that does not exist:

Example

sql

INSERT INTO students (id, name, course_id)
VALUES (9, 'Imran', 99);

Output

ERROR: foreign key constraint fails: course_id 99 not found in courses

The exact messages depend on your database, but the row is never saved.

A primary key is the unique ID of a row. A foreign key is a column that stores another table's primary key. Together they create the links that joins use.

ALTER TABLE

ALTER TABLE changes a table that already has data.

Add a column:

Example

sql

ALTER TABLE students
ADD phone VARCHAR(20);

Every existing student gets NULL in the new column.

Remove a column:

Example

sql

ALTER TABLE students
DROP COLUMN phone;

Other common changes:

Goal	MySQL	PostgreSQL
Rename a column	`ALTER TABLE students RENAME COLUMN marks TO score;`	Same
Change a type	`ALTER TABLE students MODIFY name VARCHAR(150);`	`ALTER TABLE students ALTER COLUMN name TYPE VARCHAR(150);`
Rename a table	`ALTER TABLE students RENAME TO learners;`	Same

DROP TABLE and DROP DATABASE

DROP TABLE deletes the whole table: its columns, rows and constraints.

Example

sql

DROP TABLE students;
DROP TABLE IF EXISTS old_results;

IF EXISTS avoids an error if the table is not there. DROP DATABASE institute; deletes the whole database with every table in it.

There is no undo for DROP. On a real project, always take a backup first and double-check the name.

Exercise

Which constraint makes sure that students.course_id can only hold an id that exists in the courses table?

UNIQUECHECKFOREIGN KEYDEFAULT