Tables

Creating tables

CREATE TABLE is an SQL statement used to create a new table in a database. When you create a table, you define:

• Table name
• Column names
• Data types for each column
• Constraints (rules like PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, etc.)

Once created, the table becomes a permanent structure in your database (until dropped).

General Syntax

CREATE TABLE table_name (
    column_name1 data_type constraint,
    column_name2 data_type constraint,
    ...
    table_constraints
);

• table_name → The name of your new table.
• column_name → Each column’s name.
• data_type → The kind of data the column will store (e.g., INT, VARCHAR, DATE, DECIMAL).
• constraint → Rules for the column (e.g., NOT NULL, UNIQUE, PRIMARY KEY).
• table_constraints → Constraints applied to the table as a whole (e.g., a PRIMARY KEY defined at the end).

Example

CREATE TABLE students (
    student_id CHAR(10) PRIMARY KEY,         -- Unique ID like 'STU2025001'
    name VARCHAR(100) NOT NULL,              -- Student name
    email VARCHAR(255) UNIQUE NOT NULL,      -- Unique email address
    date_of_birth DATE,                      -- Date of birth
    enrollment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- Auto-filled when record created
    gpa DECIMAL(3,2) CHECK (gpa >= 0.00 AND gpa <= 4.00) -- Must be between 0 and 4
);

1. student_id CHAR(10) PRIMARY KEY
   • CHAR(10) → Fixed length string of exactly 10 characters.
   • PRIMARY KEY → Ensures each student_id is unique and not null.
2. name VARCHAR(100) NOT NULL
   • VARCHAR(100) → Variable-length text up to 100 characters.
   • NOT NULL → Cannot be left empty.
3. email VARCHAR(255) UNIQUE NOT NULL
   • UNIQUE → No two students can have the same email.
   • NOT NULL → Must always have an email.
4. date_of_birth DATE
   • Stores date values (no time).
5. enrollment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
   • TIMESTAMP → Date and time.
   • DEFAULT CURRENT_TIMESTAMP → Automatically stores the current date & time when inserted.
6. gpa DECIMAL(3,2) CHECK (gpa >= 0.00 AND gpa <= 4.00)
   • DECIMAL(3,2) → Number with up to 3 digits, 2 of them after the decimal (e.g., 3.75).
   • CHECK → Ensures GPA is within 0.00 to 4.00.

Viewing the table structure

DESCRIBE students;

Modifying tables

ALTER TABLE is an SQL command used to modify an existing table’s structure without dropping and recreating it. It allows you to:

• Add new columns
• Modify existing columns
• Rename columns
• Drop columns
• Add or remove constraints
• Rename the table itself

General Syntax

ALTER TABLE table_name
action;

Where action could be:

• ADD column_name data_type constraints
• DROP COLUMN column_name
• ALTER COLUMN column_name TYPE new_data_type
• RENAME COLUMN old_name TO new_name
• ADD CONSTRAINT constraint_name constraint_definition
• DROP CONSTRAINT constraint_name
• RENAME TO new_table_name

Dropping tables

DROP TABLE is an SQL command used to completely delete a table from a database. When executed:

• The table’s structure is removed.
• All data in the table is permanently deleted.
• Any indexes, constraints, and triggers related to the table are also deleted.
• Important: This action cannot be undone (unless you have a backup).

General Syntax

DROP TABLE table_name; -- single
DROP TABLE table1, table2, ...; -- multiple

If you try to drop a table that doesn’t exist, some databases throw an error. To avoid that:

DROP TABLE IF EXISTS students;

If another table has a foreign key referencing the table you’re trying to drop, most databases will block the operation. In PostgreSQL, you can force it with CASCADE:

DROP TABLE students CASCADE;

• Drops students and all dependent objects (foreign keys, views, etc.).
• Be very careful — CASCADE can remove more than you expect.

Key Differences from TRUNCATE TABLE

DROP TABLE	TRUNCATE TABLE
Deletes table structure & data	Deletes only data, keeps table structure
Removes indexes, constraints	Keeps indexes, constraints
Cannot be rolled back (unless in a transaction)	Often can be rolled back in transactions
Requires re-creating table to use again	Table is ready to use after truncating

Adding and Removing Constraints

Constraints are rules applied to columns or tables to ensure data integrity and accuracy.

Common constraints include:

• PRIMARY KEY → Uniquely identifies each row.
• FOREIGN KEY → Links rows to another table.
• UNIQUE → Ensures all values in a column are different.
• NOT NULL → Prevents null values.
• CHECK → Ensures values meet a condition.
• DEFAULT → Sets a default value for a column.

Adding Constraints with ALTER TABLE

Adding a PRIMARY KEY

ALTER TABLE table_name
ADD CONSTRAINT pk_constraint_name PRIMARY KEY (column_name);

• pk_constraint_name → Name of the primary key constraint.
• (column_name) → Column(s) used for the primary key.
• Column must be NOT NULL and unique.

Adding a FOREIGN KEY

ALTER TABLE table_name
ADD CONSTRAINT fk_constraint_name FOREIGN KEY (column_name)
REFERENCES parent_table(parent_column)
[ON DELETE CASCADE]
[ON UPDATE CASCADE];

• fk_constraint_name → Name of the foreign key.
• Ensures values in the column exist in the parent table.
• Optional ON DELETE / ON UPDATE actions enforce cascading behavior.

Adding a UNIQUE Constraint

ALTER TABLE table_name
ADD CONSTRAINT uq_constraint_name UNIQUE (column_name);

• Guarantees all values in the column are distinct.

Adding a CHECK Constraint

ALTER TABLE table_name
ADD CONSTRAINT chk_constraint_name CHECK (condition);

• Ensures column values meet a defined condition.

DEFAULT Constraint

ALTER TABLE table_name
ALTER COLUMN column_name SET DEFAULT default_value;

• Assigns a default value when none is provided.

Removing Constraints

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

How to Find Constraint Names

Before removing a constraint, you must know its exact name.

\d Students; -- PostgreSQL
SHOW CREATE TABLE students; -- MySQL

• Displays the full CREATE TABLE statement for the students table.
• Includes all defined constraints (with their names).