Skip to content
SQL

SQL CREATE TABLE: Data Types and Constraints

Ihsan Ullah · 13 min read
SQL CREATE TABLE, Lesson 16 of the SQL course: data types, primary keys, foreign keys and constraints

SQL course · Module 5: Creating and changing data · Lesson 16 of 40

Quick answer: SQL CREATE TABLE makes a new table: you give each column a name and a data type, and add constraints, the rules every row must follow. PRIMARY KEY identifies each row, FOREIGN KEY (REFERENCES) links to another table, UNIQUE blocks repeats, NOT NULL blocks missing values, CHECK tests a condition and DEFAULT fills in a value you leave out. In PostgreSQL, use GENERATED ALWAYS AS IDENTITY for automatic ids instead of the older serial.

In this lesson you will learn:

  • how to write SQL CREATE TABLE and choose data types
  • what each constraint does, and the exact error when a row breaks it
  • the difference between GENERATED ... AS IDENTITY and SERIAL
  • how to remove a table safely with DROP TABLE IF EXISTS

Before you start: Lesson 1, What Is a Relational Database? Tables, Rows, Columns and Keys and Lesson 3, Install PostgreSQL and Load a Sample Database for SQL Practice.

We ran every example on PostgreSQL 16.15 in the shop database and checked the current PostgreSQL 18 manual. This lesson creates its own practice tables and drops them at the end, so the four shop tables stay unchanged.

Why a table needs rules

The shop wants product reviews. Here is a first try, with column names and types only:

CREATE TABLE reviews_draft (
    product_id  integer,
    customer_id integer,
    rating      integer,
    comment     text
);

The table accepts a review with no product, a customer who does not exist, a rating of 9 and an empty comment:

INSERT INTO reviews_draft (product_id, customer_id, rating, comment)
VALUES (NULL, 99, 9, '');
INSERT 0 1
SELECT * FROM reviews_draft;
 product_id | customer_id | rating | comment 
------------+-------------+--------+---------
     (null) |          99 |      9 | 
(1 row)

A data type only checks the kind of value. Constraints check whether the value makes sense. Drop the draft:

DROP TABLE reviews_draft;

The CREATE TABLE syntax

After CREATE TABLE and the table name comes a list in brackets. Each column is a name, a data type and optional rules, separated by commas. Rules that use more than one column, such as “one review per customer per product”, go at the end as table constraints. The manual explains that column constraints “can also be written as table constraints”, but not always the other way round (PostgreSQL docs: Constraints).

Choosing data types

These types cover most beginner tables. The shop tables use several of them.

typestoresexample in shop
integerwhole numbers from -2147483648 to +2147483647ids, quantity
bigintlarger whole numbersids in very large tables
numeric(10,2)exact decimals: 10 digits, 2 after the pointprice, unit_price
texttext of any lengthname, email, city
varchar(n)text with a maximum length n(not used)
booleantrue or false(the reviews table below)
datea calendar datesignup_date, order_date
timestamptzdate and time with time zone(not used)

For money, use numeric. The manual recommends it “for storing monetary amounts and other quantities where exactness is required” (PostgreSQL docs: Numeric Types). With numeric(10,2), extra decimals are rounded:

SELECT 12.345::numeric(10,2) AS price;
 price 
-------
 12.35
(1 row)

For text, the PostgreSQL wiki suggests text (or varchar without a length) instead of varchar(n) by default, because varchar(n) throws an error when a value is longer than n (PostgreSQL wiki: Don’t Do This). In our test, 'abcdefgh' in a varchar(5) column gave ERROR: value too long for type character varying(5).

SQL CREATE TABLE with constraints

Here is the real reviews table. DROP TABLE IF EXISTS first removes an old copy, so you can run the file again:

DROP TABLE IF EXISTS reviews;

CREATE TABLE reviews (
    id          integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id  integer NOT NULL REFERENCES products (id),
    customer_id integer NOT NULL REFERENCES customers (id),
    rating      integer NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment     text CONSTRAINT comment_not_empty CHECK (comment <> ''),
    verified    boolean NOT NULL DEFAULT false,
    UNIQUE (product_id, customer_id)
);

In psql, \d reviews describes the table:

                           Table "public.reviews"
   Column    |  Type   | Collation | Nullable |           Default            
-------------+---------+-----------+----------+------------------------------
 id          | integer |           | not null | generated always as identity
 product_id  | integer |           | not null | 
 customer_id | integer |           | not null | 
 rating      | integer |           | not null | 
 comment     | text    |           |          | 
 verified    | boolean |           | not null | false
Indexes:
    "reviews_pkey" PRIMARY KEY, btree (id)
    "reviews_product_id_customer_id_key" UNIQUE CONSTRAINT, btree (product_id, customer_id)
Check constraints:
    "comment_not_empty" CHECK (comment <> ''::text)
    "reviews_rating_check" CHECK (rating >= 1 AND rating <= 5)
Foreign-key constraints:
    "reviews_customer_id_fkey" FOREIGN KEY (customer_id) REFERENCES customers(id)
    "reviews_product_id_fkey" FOREIGN KEY (product_id) REFERENCES products(id)

Every constraint has a name. PostgreSQL chose most of them, such as reviews_rating_check. We named one ourselves with CONSTRAINT comment_not_empty. These names appear in error messages. Now two good rows:

INSERT INTO reviews (product_id, customer_id, rating, comment)
VALUES (1, 1, 5, 'Small and quiet.'),
       (8, 3, 4, NULL);

SELECT * FROM reviews;
 id | product_id | customer_id | rating |     comment      | verified 
----+------------+-------------+--------+------------------+----------
  1 |          1 |           1 |      5 | Small and quiet. | f
  2 |          8 |           3 |      4 | (null)           | f
(2 rows)

We did not give id or verified. The identity column numbered the rows, and DEFAULT false filled verified (f is how psql shows false). The manual says the default “will be used in any insert operation that does not specify a value for the column. If there is no default for a column, then the default is null” (PostgreSQL docs: CREATE TABLE).

Breaking each rule: the real errors

Each insert below breaks one rule.

NOT NULL

INSERT INTO reviews (product_id, customer_id, comment) VALUES (2, 2, 'Nice keys.');
ERROR:  null value in column "rating" of relation "reviews" violates not-null constraint
DETAIL:  Failing row contains (3, 2, 2, null, Nice keys., f).

We left out rating, which has no default, so it would be NULL.

Data type

INSERT INTO reviews (product_id, customer_id, rating) VALUES (2, 2, 'five');
ERROR:  invalid input syntax for type integer: "five"
LINE 1: ...ews (product_id, customer_id, rating) VALUES (2, 2, 'five');
                                                               ^

CHECK

INSERT INTO reviews (product_id, customer_id, rating) VALUES (2, 2, 6);
ERROR:  new row for relation "reviews" violates check constraint "reviews_rating_check"
DETAIL:  Failing row contains (4, 2, 2, 6, null, f).

BETWEEN 1 AND 5 includes both ends, as in Lesson 5.

INSERT INTO reviews (product_id, customer_id, rating, comment) VALUES (2, 2, 4, '');
ERROR:  new row for relation "reviews" violates check constraint "comment_not_empty"
DETAIL:  Failing row contains (5, 2, 2, 4, , f).

The comment can still be NULL, as in the second good row. The manual says “a check constraint is satisfied if the check expression evaluates to true or the null value”, and NULL <> '' is NULL, not false (Lesson 6). Add NOT NULL when a value must be present.

UNIQUE

INSERT INTO reviews (product_id, customer_id, rating) VALUES (1, 1, 4);
ERROR:  duplicate key value violates unique constraint "reviews_product_id_customer_id_key"
DETAIL:  Key (product_id, customer_id)=(1, 1) already exists.

Customer 1 already reviewed product 1. The table constraint UNIQUE (product_id, customer_id) checks the pair; customer 1 may still review other products. NULLs are a special case: “By default, two null values are not considered equal”, so a UNIQUE column can hold many NULLs, like email in customers. PostgreSQL 15 added UNIQUE NULLS NOT DISTINCT to change that (PostgreSQL 15 release notes).

FOREIGN KEY

INSERT INTO reviews (product_id, customer_id, rating) VALUES (99, 2, 4);
ERROR:  insert or update on table "reviews" violates foreign key constraint "reviews_product_id_fkey"
DETAIL:  Key (product_id)=(99) is not present in table "products".

REFERENCES products (id) means every product_id must exist in products. The manual calls this “referential integrity”.

PRIMARY KEY and the identity column

A primary key must be “both unique and not null”, and “a table can have at most one primary key”. Our id is also GENERATED ALWAYS AS IDENTITY, so PostgreSQL refuses an id typed by hand:

INSERT INTO reviews (id, product_id, customer_id, rating) VALUES (10, 2, 2, 4);
ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

Now a correct insert, and look at the new id:

INSERT INTO reviews (product_id, customer_id, rating, comment) VALUES (2, 2, 4, 'Nice keys.');

SELECT id, product_id, customer_id, rating, verified FROM reviews ORDER BY id;
 id | product_id | customer_id | rating | verified 
----+------------+-------------+--------+----------
  1 |          1 |           1 |      5 | f
  2 |          8 |           3 |      4 | f
  8 |          2 |           2 |      4 | f
(3 rows)

Id 8, not 3. Five failed inserts had already taken ids 3 to 7 (the “Failing row” details show 3, 4 and 5). The manual says a used sequence value “is not reclaimed for re-use”, so PostgreSQL ids “cannot be used to obtain ‘gapless’ sequences” (PostgreSQL docs: Sequence Functions). Gaps are normal; never use the id to count rows.

GENERATED AS IDENTITY vs SERIAL

Older tutorials write id serial PRIMARY KEY. The manual says serial types “are not true types, but merely a notational convenience”: serial creates a sequence and a default that calls it. Create both and describe them in psql:

CREATE TABLE demo_serial (
    id   serial PRIMARY KEY,
    name text
);

CREATE TABLE demo_identity (
    id   integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name text
);

\d demo_serial
\d demo_identity
                            Table "public.demo_serial"
 Column |  Type   | Collation | Nullable |                 Default                 
--------+---------+-----------+----------+-----------------------------------------
 id     | integer |           | not null | nextval('demo_serial_id_seq'::regclass)
 name   | text    |           |          | 
Indexes:
    "demo_serial_pkey" PRIMARY KEY, btree (id)

                        Table "public.demo_identity"
 Column |  Type   | Collation | Nullable |             Default              
--------+---------+-----------+----------+----------------------------------
 id     | integer |           | not null | generated by default as identity
 name   | text    |           |          | 
Indexes:
    "demo_identity_pkey" PRIMARY KEY, btree (id)

The same manual page calls serial “a PostgreSQL-specific way” and points to “the SQL-standard identity column feature” (PostgreSQL docs: Numeric Types). The PostgreSQL wiki advises against serial because its “weird behaviors” make “schema, dependency, and permission management unnecessarily cumbersome”. Its one exception is supporting PostgreSQL older than version 10.

There are two identity options. ALWAYS refuses ids typed by hand, as you saw. BY DEFAULT accepts them, which can cause a trap:

INSERT INTO demo_identity (id, name) VALUES (1, 'typed by hand');
INSERT INTO demo_identity (name) VALUES ('automatic');
INSERT 0 1
ERROR:  duplicate key value violates unique constraint "demo_identity_pkey"
DETAIL:  Key (id)=(1) already exists.

The first insert worked. The sequence did not know that 1 was taken, so it handed out 1 again. Prefer ALWAYS unless you must insert ids yourself. The shop tables use BY DEFAULT, which is why Lesson 1 could try to insert id 1 by hand.

DROP TABLE and DROP TABLE IF EXISTS

DROP TABLE removes a table and all its rows; there is no undo. PostgreSQL refuses when another table depends on it:

DROP TABLE products;
ERROR:  cannot drop table products because other objects depend on it
DETAIL:  constraint order_items_product_id_fkey on table order_items depends on table products
constraint reviews_product_id_fkey on table reviews depends on table products
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Be careful with the hint: with CASCADE, PostgreSQL removes the foreign key constraints in the other tables (PostgreSQL docs: DROP TABLE). Dropping a table that does not exist is an error, but IF EXISTS turns it into a notice. Here it is in psql, dropping reviews twice and then once with IF EXISTS:

shop=# DROP TABLE reviews;
DROP TABLE
shop=# DROP TABLE reviews;
ERROR:  table "reviews" does not exist
shop=# DROP TABLE IF EXISTS reviews;
NOTICE:  table "reviews" does not exist, skipping
DROP TABLE

Try it yourself

Exercise 1: A suppliers table

Create suppliers with an automatic id that cannot be typed by hand, a required name that must be unique, and a required country that is 'Pakistan' when left out. Insert one supplier giving only the name.

Exercise 2: Test the UNIQUE rule

Insert a second supplier with the same name and the country 'UK'. What happens?

Exercise 3: A stock table

Create stock with product_id as the primary key that must exist in products, and a required quantity that cannot be negative. Insert 25 units of product 1, then try -3 units of product 2.

Solutions

Solution 1

CREATE TABLE suppliers (
    id      integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name    text NOT NULL UNIQUE,
    country text NOT NULL DEFAULT 'Pakistan'
);

INSERT INTO suppliers (name) VALUES ('Lahore Office Supply');
SELECT * FROM suppliers;
 id |         name         | country  
----+----------------------+----------
  1 | Lahore Office Supply | Pakistan
(1 row)

The id and the country came from the identity column and the default.

Solution 2

INSERT INTO suppliers (name, country) VALUES ('Lahore Office Supply', 'UK');
ERROR:  duplicate key value violates unique constraint "suppliers_name_key"
DETAIL:  Key (name)=(Lahore Office Supply) already exists.

UNIQUE checks only the name column, so a different country does not help.

Solution 3

CREATE TABLE stock (
    product_id integer PRIMARY KEY REFERENCES products (id),
    quantity   integer NOT NULL CHECK (quantity >= 0)
);

INSERT INTO stock (product_id, quantity) VALUES (1, 25);
INSERT INTO stock (product_id, quantity) VALUES (2, -3);
ERROR:  new row for relation "stock" violates check constraint "stock_quantity_check"
DETAIL:  Failing row contains (2, -3).

The first insert worked (INSERT 0 1); the second broke the CHECK. A column can be a primary key and a foreign key at the same time. Clean up with DROP TABLE IF EXISTS stock, suppliers, demo_serial, demo_identity;.

Common mistakes

  • No NOT NULL on required columns. A CHECK alone lets NULL through. Add NOT NULL when a value must be present.
  • A foreign key to a table that does not exist yet. You get ERROR: relation "not_created_yet" does not exist. Create the referenced table first (products before reviews).
  • Using money for prices. Use numeric(10,2). The wiki says money “isn’t actually very good for storing monetary values”.
  • Typing ids by hand into a BY DEFAULT identity column. Later automatic inserts fail with duplicate key value violates unique constraint.
  • Running CREATE TABLE twice. The second run fails with ERROR: relation "practice" already exists. Start practice scripts with DROP TABLE IF EXISTS, never scripts that touch real data.

Frequently asked questions

What is the difference between a primary key and a unique key?

Both block repeated values. A primary key also forbids NULL, and a table can have only one. A table can have many UNIQUE constraints, and they allow NULLs unless the column is also NOT NULL.

Should I use SERIAL or IDENTITY in PostgreSQL?

Use GENERATED ALWAYS AS IDENTITY (or BY DEFAULT if you must insert ids yourself) for new tables. It is the SQL-standard syntax, and the PostgreSQL wiki lists serial under “Don’t Do This”.

Can I add a constraint after creating the table?

Yes, with ALTER TABLE, for example ALTER TABLE practice ADD CONSTRAINT qty_not_negative CHECK (qty >= 0);. PostgreSQL checks the rows already in the table; in our test, one row with -3 made it fail with check constraint "qty_not_negative" of relation "practice" is violated by some row. Designing tables and keys is covered in Lessons 28 to 30.

Summary

  • SQL CREATE TABLE lists columns with a name, a data type and optional constraints.
  • Use integer or bigint for counts and ids, numeric for money, text for text, date and timestamptz for time.
  • NOT NULL, CHECK, UNIQUE, PRIMARY KEY and FOREIGN KEY stop bad rows, each with a clear error; a CHECK passes NULL.
  • Prefer GENERATED ALWAYS AS IDENTITY to serial; ids can have gaps.
  • DROP TABLE IF EXISTS removes a table without an error if it is missing; a table that others reference cannot be dropped without CASCADE.

For practice, the SQL window functions practice problems start with a CREATE TABLE sales that has no constraints at all: try adding a primary key and a CHECK on amount. In the data engineering projects list, the first project loads processed data into PostgreSQL, which starts with tables like these.

Next lesson

Next: Lesson 17, SQL INSERT, UPDATE and DELETE: Change Data Safely with RETURNING.

Sources

Ihsan Ullah

Written & tested by Ihsan Ullah

Data analyst with 3 years of experience and a Bachelor's degree in Computer Science. I write clear, practical guides on data, SQL, Python and AI.

More about me →

Leave a comment

Your email address will not be published. Required fields are marked *