CREATE TABLE and DROP TABLE
35 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)A table is born from one SQL statement: CREATE TABLE gives the table a name and lists its columns, each with a data type. Before the first row arrives, the database already knows the table's exact shape, and it keeps every row to that shape.
CREATE TABLE: columns and their types
CREATE TABLE makes a new, empty table. You give the table a name and list its columns inside round brackets, each column with a name and a data type.
CREATE TABLE makes a new, empty table. You write the table name, then the columns inside round brackets, separated by commas.
Example 1 · Create a hostel rooms table
CREATE TABLE hostel_rooms ( room_no INT, block CHAR(1), beds TINYINT, rent DECIMAL(8,2), warden VARCHAR(40));
INSERT INTO hostel_rooms (room_no, block, beds, rent, warden) VALUES (101, 'A', 2, 4500.00, 'Mr. Iyer'), (102, 'A', 3, 3800.00, 'Mr. Iyer'), (201, 'B', 1, 6200.00, 'Mrs. Das');
SELECT * FROM hostel_rooms ORDER BY room_no;+---------+-------+------+---------+----------+ | room_no | block | beds | rent | warden | +---------+-------+------+---------+----------+ | 101 | A | 2 | 4500.00 | Mr. Iyer | | 102 | A | 3 | 3800.00 | Mr. Iyer | | 201 | B | 1 | 6200.00 | Mrs. Das | +---------+-------+------+---------+----------+
What this code does
CREATE TABLEprints nothing when it works: the table now exists, empty. TheINSERTthen adds three rows, andSELECT * ... ORDER BY room_noshows every column of every row, sorted by room number.- Each column has a type:
INTandTINYINThold whole numbers,CHAR(1)exactly one character,DECIMAL(8,2)an exact amount with 2 digits after the point,VARCHAR(40)text of up to 40 characters. - In the result, numbers are right-aligned and text is left-aligned. The
mysqlclient also prints a line such as3 rows in set (0.00 sec); lessons leave it out.
Example 2 · A comma after the last column
CREATE TABLE cinema_shows ( show_id INT, movie VARCHAR(60), screen TINYINT,);ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 5
What this code does
- The comma after
screen TINYINTtells MySQL another column follows, but)comes instead. Error 1064 is the general "this is not valid SQL" error. - The text after
nearis where MySQL got confused, here). The real mistake is usually just before that point. Remove the last comma and the statement works.
Each column is a name followed by a data type: the kind of value it holds, such as a whole number (INT) or text of up to 40 characters (VARCHAR(40)).
Why declare types first? A spreadsheet lets anyone type "two" into a column of numbers, and the totals break later. A database checks every value against the column's type when it arrives, so bad data is stopped at the door. The pieces, clause by clause:
CREATE TABLE hostel_roomsnames the new table. Use lowercase words joined by_.room_no INTis one column: its name, then its type. The next lessons cover each type in detail.- Commas go between columns, never after the last one. The closing
)and the;end the statement.
Fix the error
Fix the CREATE TABLE
Read the real error, then pick the line that fixes it.
The table will not be created.
Error
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 5
INSERT to add a few rows and SELECT to show them. Both get their own modules later; here they only show what the table holds.DESCRIBE: see the columns of a table
DESCRIBE lists the columns of a table, one column per row, with its type, whether it may be NULL, its key, its default value and extras such as auto_increment.
DESCRIBE table_name lists a table's columns with their types, one column per row. Use it whenever you forget a column's exact name or type, or before you write data into a table someone else made.
Example 3 · DESCRIBE a new table and a practice table
CREATE TABLE cinema_shows ( show_id INT, movie VARCHAR(60), screen TINYINT, show_day DATE);
DESCRIBE cinema_shows;DESCRIBE products;+----------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+-------+ | show_id | int | YES | | NULL | | | movie | varchar(60) | YES | | NULL | | | screen | tinyint | YES | | NULL | | | show_day | date | YES | | NULL | | +----------+-------------+------+-----+---------+-------+ +-------------+---------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+---------------+------+-----+---------+----------------+ | product_id | int | NO | PRI | NULL | auto_increment | | name | varchar(80) | NO | | NULL | | | category_id | int | YES | MUL | NULL | | | price | decimal(10,2) | NO | | NULL | | | stock | int | NO | | 0 | | +-------------+---------------+------+-----+---------+----------------+
What this code does
cinema_showswas created with no extra rules, so every column may be empty (NullisYES) and has no default (NULL).productsfrom the practice database shows more:product_idis the primary key (PRI) and fills itself (auto_increment),nameandpriceare required (NO),stockdefaults to0, andcategory_idis indexed (MUL) because it links tocategories.- MySQL 8.0 and later print
intandtinyintwith no number after them; older versions printedint(11). You may still see that form in old tutorials.
Each row of the result tells you:
Fieldis the column name andTypeits data type.Nullsays whether the column may be left empty (YES) or not (NO).KeyshowsPRIfor the primary key, the column that identifies each row, andUNIorMULfor other indexed columns.Defaultis the value used when an insert leaves the column out;Extrashows extras such asauto_increment.
DESC products; is a short form of DESCRIBE products;, and SHOW COLUMNS FROM products; gives the same table.SHOW CREATE TABLE: the full definition
SHOW CREATE TABLE prints the complete CREATE TABLE statement that would rebuild the table, including details DESCRIBE does not show, such as the storage engine and character set.
SHOW CREATE TABLE table_name prints the complete CREATE TABLE statement that would rebuild the table, including details DESCRIBE does not show. It is how you copy a table's design into another database, or check exactly what MySQL stored.
Example 4 · SHOW CREATE TABLE with \G
CREATE TABLE cinema_shows ( show_id INT, movie VARCHAR(60), screen TINYINT, show_day DATE);
SHOW CREATE TABLE cinema_shows\G*************************** 1. row ***************************
Table: cinema_shows
Create Table: CREATE TABLE `cinema_shows` (
`show_id` int DEFAULT NULL,
`movie` varchar(60) DEFAULT NULL,
`screen` tinyint DEFAULT NULL,
`show_day` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ciWhat this code does
\Gends the statement like;but prints the row asname: valuelines. Themysqlclient understands it; other tools such as MySQL Workbench show the same text in a grid cell.- MySQL added backticks,
DEFAULT NULLand the table optionsENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci. These are the defaults of MySQL 8.0 and later; your server may show a different collation if someone changed its settings, which is why this is a sample.
The printed statement is MySQL's own version of what you typed. It puts every name between backticks (the slanted quote on the key left of 1), writes DEFAULT NULL for columns that may be empty, and adds the table options it chose for you: the storage engine InnoDB, the character set utf8mb4 and the collation. The Naming lesson of this module explains those three.
The result is one long row. In the mysql client, end the statement with \G instead of ; and each column of the result prints on its own line, which is much easier to read.
DROP TABLE and DROP TABLE IF EXISTS
DROP TABLE deletes a table with its structure and every row in it, and there is no undo. DROP TABLE IF EXISTS does the same only when the table is there, so a script can run again without an error.
DROP TABLE table_name deletes the table: its structure and every row in it. There is no undo and no "Are you sure?" prompt, so read the table name twice before you press Enter on a real database.
Example 5 · A script you can run again and again
DROP TABLE IF EXISTS train_seats;
CREATE TABLE train_seats ( train_no INT, coach VARCHAR(4), seat_no TINYINT, berth VARCHAR(12));
INSERT INTO train_seats (train_no, coach, seat_no, berth) VALUES (12627, 'S4', 33, 'Lower'), (12627, 'S4', 36, 'Side Upper');
SELECT * FROM train_seats ORDER BY seat_no;
DROP TABLE train_seats;DROP TABLE IF EXISTS train_seats;DROP TABLE customers;+----------+-------+---------+------------+ | train_no | coach | seat_no | berth | +----------+-------+---------+------------+ | 12627 | S4 | 33 | Lower | | 12627 | S4 | 36 | Side Upper | +----------+-------+---------+------------+ ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'orders_ibfk_1' on table 'orders'.
What this code does
- The first
DROP TABLE IF EXISTSdoes nothing on a fresh database, so the script never fails at the top. Run it twice and you still get exactly two rows, because the old table is dropped first. - After
DROP TABLE train_seats, the secondDROP TABLE IF EXISTSfinds nothing and only leaves a note; it is not an error. DROP TABLE customersfails with error 3730:orderspoints tocustomersthrough a foreign key, so MySQL refuses. The client stops at the first error, so nothing after it would run.
DROP TABLE IF EXISTS table_name does the same, but only if the table is there; if it is missing, MySQL does nothing and records a note instead of an error. Practice scripts start with it, so you can run the same script again and again and always get a fresh table.
MySQL also protects tables that other tables point to. The practice table orders points to customers through a foreign key (a link the Keys & Constraints module covers), so dropping customers would leave orders without their customers.
Order of execution
Order a re-runnable script
Put the statements of a re-runnable table script in order.
Errors: table already exists, unknown table
Two errors appear again and again while you build tables. Running the same CREATE TABLE twice gives error 1050: ERROR 1050 (42S01): Table 'hostel_rooms' already exists.
Example 6 · Table already exists, and IF NOT EXISTS
CREATE TABLE exam_halls ( hall_code CHAR(3), capacity INT);INSERT INTO exam_halls (hall_code, capacity) VALUES ('H01', 60);
CREATE TABLE IF NOT EXISTS exam_halls ( hall_code CHAR(3), capacity INT, floor_no TINYINT);SHOW WARNINGS;SELECT * FROM exam_halls;
CREATE TABLE exam_halls ( hall_code CHAR(3), capacity INT);+-------+------+-----------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------+ | Note | 1050 | Table 'exam_halls' already exists | +-------+------+-----------------------------------+ +-----------+----------+ | hall_code | capacity | +-----------+----------+ | H01 | 60 | +-----------+----------+ ERROR 1050 (42S01): Table 'exam_halls' already exists
What this code does
CREATE TABLE IF NOT EXISTSsaw the name was taken and skipped the create.SHOW WARNINGSshows the note it left (code 1050), and the old row is still there.- The new
floor_nocolumn was not added:IF NOT EXISTSchecks only the name, never the columns. - The plain
CREATE TABLEat the end fails with error 1050, because the table already exists.
Dropping or reading a table that is not there gives ERROR 1051 (42S02): Unknown table 'practice.hostel_room' for DROP, and ERROR 1146 (42S02): Table 'practice.hostel_room' doesn't exist for SELECT. The part before the dot is your database's name.
The usual cause of 1051 and 1146 is a typo (hostel_room for hostel_rooms) or the wrong database selected with USE. Run SHOW TABLES; to see the real names. For 1050 you have two choices. DROP TABLE IF EXISTS first, when you want a fresh table. Or CREATE TABLE IF NOT EXISTS, which skips the create when the table is already there and keeps its rows.
Predict the output
Predict the error
Run the two statements one after the other. What happens at the second one?
CREATE TABLE IF NOT EXISTS only checks the name. If an old table with different columns has that name, MySQL keeps the old table and your new columns never appear. When the design changes, drop and recreate (practice data) or use ALTER TABLE (real data).Next step
Check your understanding
11 quick questions