DbSchema PostgreSQL starts when you begin the lesson

Lesson 8

Primary keys and foreign keys

For readers who finished part 2 and can query and summarize one table.

After this lesson you can say what a primary key and a foreign key guarantee, read them in a diagram, and create a table that has both.
  • The primary key names one row
  • A key of two columns
  • The foreign key points at another table
  • What PostgreSQL refuses
  • Keys in a diagram
  • 4 exercises

Start the lesson

Every query runs on real PostgreSQL, in your browser. It downloads once, about 6 MB, then starts in a few seconds.

For a reference page on the same statements, read What Is a Primary Key in SQL? Definition and Examples.

The idea

A primary key names exactly one row

Table public.departments ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The departments of the shop. Every employee works in one of them.departments 🔑 Pk departments_pkey ( department_id ) ⧉ department_id * int4 ↙ employees( department_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the department.department_id 🔗 Referred by employees ( department_id ) 🔍 Unq departments_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the department, such as Sales. No two departments share a name.name
Hover a table or a column to read its comment.

This is how departments was created:

create table departments (
    department_id integer primary key,
    name          text not null unique
);

The primary key is department_id. No two departments may have the same id, and none may be without one, so a department id always means one department and only one.

Try it

Add a department whose id is taken

Table public.departments ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The departments of the shop. Every employee works in one of them.departments 🔑 Pk departments_pkey ( department_id ) ⧉ department_id * int4 ↙ employees( department_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the department.department_id 🔗 Referred by employees ( department_id ) 🔍 Unq departments_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the department, such as Sales. No two departments share a name.name
Hover a table or a column to read its comment.
insert into departments values (1, 'Purchasing');

PostgreSQL refuses the row and names the key in its message: departments_pkey is the primary key of departments.

The idea

A key can take two columns

The DbSchema Edit Table dialog of order_items, on its Columns tab: order_id and line_no carry a key icon, followed by product_id, quantity and unit_price
The order_items table in DbSchema: the key icons mark the two columns of its primary key.

An order line is named by its order and its number within that order: the table ends with primary key (order_id, line_no). Many lines have line_no 1, one per order, and an order has several lines. Only the pair is unique: (7, 1) is line 1 of order 7.

The idea

A foreign key points at a row of another table

🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) 🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) customer_id Table public.customers ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The people who buy from the shop.customers 🔑 Pk customers_pkey ( customer_id ) ⧉ customer_id * int4 ↙ orders( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the customer.customer_id 🔗 Referred by orders ( customer_id ) ⧉ first_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The first name of the customer.first_name ⧉ last_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The last name of the customer.last_name 🔍 Unq customers_email_key ( email ) ⧉ email * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The email address of the customer. No two customers share one.email ⧉ phone text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The phone number of the customer. NULL when the customer gave none.phone ⧉ country * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The country the customer lives in.country ⧉ signup_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the customer created an account.signup_date Table public.orders ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ One purchase by one customer. Its products are the rows of order_items.orders 🔑 Pk orders_pkey ( order_id ) ⧉ order_id * int4 ↙ order_items( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the order.order_id 🔗 Referred by order_items ( order_id ) ⧉ customer_id * int4 ↗ customers( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The customer who placed the order: a customer_id of customers.customer_id 🔗 References customers ( customer_id ) ⧉ employee_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id 🔗 References employees ( employee_id ) ⧉ order_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the order was placed.order_date ⧉ status * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ Where the order stands: pending, shipped, delivered or cancelled.status
The two tables, drawn by DbSchema from the practice database.

In orders, customer_id integer not null references customers makes customer_id a foreign key: its value must be the id of a customer that exists.

The line in the diagram is that key. It runs from the column that points to the table it points at.

Try it

Add an order for a customer who does not exist

Table public.orders ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ One purchase by one customer. Its products are the rows of order_items.orders 🔑 Pk orders_pkey ( order_id ) ⧉ order_id * int4 ↙ order_items( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the order.order_id 🔗 Referred by order_items ( order_id ) ⧉ customer_id * int4 ↗ customers( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The customer who placed the order: a customer_id of customers.customer_id 🔗 References customers ( customer_id ) ⧉ employee_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id 🔗 References employees ( employee_id ) ⧉ order_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the order was placed.order_date ⧉ status * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ Where the order stands: pending, shipped, delivered or cancelled.status
Hover a table or a column to read its comment.
insert into orders values (5000, 999, null, date '2026-10-17', 'pending');

The foreign key orders_customer_id_fkey refuses an order that would point at nobody.

Try it

Delete a customer who has orders

Table public.customers ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The people who buy from the shop.customers 🔑 Pk customers_pkey ( customer_id ) ⧉ customer_id * int4 ↙ orders( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the customer.customer_id 🔗 Referred by orders ( customer_id ) ⧉ first_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The first name of the customer.first_name ⧉ last_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The last name of the customer.last_name 🔍 Unq customers_email_key ( email ) ⧉ email * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The email address of the customer. No two customers share one.email ⧉ phone text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The phone number of the customer. NULL when the customer gave none.phone ⧉ country * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The country the customer lives in.country ⧉ signup_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the customer created an account.signup_date
Hover a table or a column to read its comment.
delete from customers where customer_id = 1;

The same key works the other way: a customer that orders point at cannot go, or those orders would point at nobody. Lesson 13 deletes rows in the order the keys allow.

The idea

The lines of a diagram are the foreign keys

🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) 🔗 Foreign Key orders_customer_id_fkey orders ➔ customers ( customer_id ) customer_id 🔗 Foreign Key orders_employee_id_fkey orders ➔ employees ( employee_id ) 🔗 Foreign Key orders_employee_id_fkey orders ➔ employees ( employee_id ) employee_id 🔗 Foreign Key order_items_order_id_fkey order_items ➔ orders ( order_id ) 🔗 Foreign Key order_items_order_id_fkey order_items ➔ orders ( order_id ) order_id 🔗 Foreign Key order_items_product_id_fkey order_items ➔ products ( product_id ) 🔗 Foreign Key order_items_product_id_fkey order_items ➔ products ( product_id ) product_id 🔗 Foreign Key employees_department_id_fkey employees ➔ departments ( department_id ) 🔗 Foreign Key employees_department_id_fkey employees ➔ departments ( department_id ) department_id 🔗 Foreign Key employees_manager_id_fkey employees ➔ employees ( manager_id -> employee_id ) 🔗 Foreign Key employees_manager_id_fkey employees ➔ employees ( manager_id -> employee_id ) manager_id Table public.customers ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The people who buy from the shop.customers 🔑 Pk customers_pkey ( customer_id ) ⧉ customer_id * int4 ↙ orders( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the customer.customer_id 🔗 Referred by orders ( customer_id ) ⧉ first_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The first name of the customer.first_name ⧉ last_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The last name of the customer.last_name 🔍 Unq customers_email_key ( email ) ⧉ email * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The email address of the customer. No two customers share one.email ⧉ phone text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The phone number of the customer. NULL when the customer gave none.phone ⧉ country * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The country the customer lives in.country ⧉ signup_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the customer created an account.signup_date Table public.orders ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ One purchase by one customer. Its products are the rows of order_items.orders 🔑 Pk orders_pkey ( order_id ) ⧉ order_id * int4 ↙ order_items( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the order.order_id 🔗 Referred by order_items ( order_id ) ⧉ customer_id * int4 ↗ customers( customer_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The customer who placed the order: a customer_id of customers.customer_id 🔗 References customers ( customer_id ) ⧉ employee_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id 🔗 References employees ( employee_id ) ⧉ order_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the order was placed.order_date ⧉ status * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ Where the order stands: pending, shipped, delivered or cancelled.status Table public.order_items ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The lines of an order: one row for each product in it.order_items 🔑 Pk order_items_pkey ( order_id, line_no ) ⧉ order_id * int4 ↗ orders( order_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The order this line belongs to: an order_id of orders.order_id 🔗 References orders ( order_id ) 🔑 Pk order_items_pkey ( order_id, line_no ) ⧉ line_no * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number of the line within its order. With order_id, it identifies the line.line_no ⧉ product_id * int4 ↗ products( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The product on this line: a product_id of products.product_id 🔗 References products ( product_id ) ⧉ quantity * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces of the product were ordered.quantity ⧉ unit_price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The price of one piece on this line, kept with the order so that a later change of products.price leaves it as it was.unit_price Table public.products ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ What the shop sells.products 🔑 Pk products_pkey ( product_id ) ⧉ product_id * int4 ↙ order_items( product_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the product.product_id 🔗 Referred by order_items ( product_id ) 🔍 Unq products_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the product. No two products share a name.name ⧉ category * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The group the product belongs to, such as Accessories or Displays.category ⧉ price * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The current price, with two decimals. Always more than 0.price ⧉ stock * int4 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ How many pieces are in the warehouse. Never below 0.stock Table public.employees ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The people who work for the shop.employees 🔑 Pk employees_pkey ( employee_id ) ⧉ employee_id * int4 ↙ employees( manager_id ) ↙ orders( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the employee.employee_id 🔗 Referred by employees ( manager_id -> employee_id ) Referred by orders ( employee_id ) ⧉ first_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The first name of the employee.first_name ⧉ last_name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The last name of the employee.last_name 🔍 Unq employees_email_key ( email ) ⧉ email * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The work email address. No two employees share one.email ⧉ department_id * int4 ↗ departments( department_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The department the employee works in: a department_id of departments.department_id 🔗 References departments ( department_id ) ⧉ manager_id int4 ↗ employees( employee_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The employee this employee reports to: an employee_id of this same table. NULL for the head of a department.manager_id 🔗 References employees ( manager_id -> employee_id ) ⧉ hire_date * date ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The day the employee started.hire_date ⧉ salary * numeric(10,2) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The yearly salary, with two decimals.salary Table public.departments ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The departments of the shop. Every employee works in one of them.departments 🔑 Pk departments_pkey ( department_id ) ⧉ department_id * int4 ↙ employees( department_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the department.department_id 🔗 Referred by employees ( department_id ) 🔍 Unq departments_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the department, such as Sales. No two departments share a name.name
The practice database, drawn by DbSchema.

Every line is a foreign key: an order points at a customer and an employee, an order line at an order and a product, an employee at a department and at their manager. Lesson 9 joins tables along these lines.

In DbSchema

Open a foreign key to see what it links

The DbSchema Edit Foreign Key dialog for orders_customer_id_fkey: table public.orders references table customers, with the referring column customer_id paired with the referred column customer_id, On Delete and On Update set to noAction
The foreign key from orders to customers, opened in DbSchema.

In a DbSchema diagram, a double-click on a line opens the Edit Foreign Key dialog: the table it points at, and which column pairs with which. Connect DbSchema to your own PostgreSQL and it draws these lines from the keys your tables already have.

Download DbSchema

The Community Edition is free. Every download starts with a 15-day trial of all features.

Exercise 1 of 4

Which columns form the primary key of order_items?

Each answer sends the exercise and whether the answer was right to dbschema.com, without your IP address.

Exercise 2 of 4

Add a department named Purchasing, with id 8.

Table public.departments ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The departments of the shop. Every employee works in one of them.departments 🔑 Pk departments_pkey ( department_id ) ⧉ department_id * int4 ↙ employees( department_id ) ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The number that identifies the department.department_id 🔗 Referred by employees ( department_id ) 🔍 Unq departments_name_key ( name ) ⧉ name * text ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯ The name of the department, such as Sales. No two departments share a name.name
Hover a table or a column to read its comment.
Expected result

It shows once PostgreSQL is ready.


    
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.

    
    

        Show answer
        
insert into departments values (8, 'Purchasing')

    






    
Exercise 3 of 4

    




What happens when you add an order for customer 999, who does not exist? Run it here first if you like.










  

    
        
    
    
        
        
        
        
        
        
        
        
        
        
        
        
        
    
    
        
        
    
    
        
        
    
    
        
        
        
        
    
    
         
         
        
             
             
        
    
    
        
        
    
    
        
        
    
    
        
        
        
        
        
        
        
        
        
        
        
        
        
    

  
    
    
    
  
  
    
    
    
  
  
    
    
    
    
  
  
    
    
  
  
    
  
  
    
  
  
      
      
  
  
      
      
  










Table public.orders
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      One purchase by one customer. Its products are the rows of order_items.orders
  🔑  Pk orders_pkey ( order_id ) ⧉  order_id
     * int4
 ↙ order_items( order_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The number that identifies the order.order_id
🔗  Referred by order_items ( order_id ) 
  ⧉  customer_id
     * int4
 ↗ customers( customer_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The customer who placed the order: a customer_id of customers.customer_id
🔗  References customers ( customer_id ) 
  ⧉  employee_id
     int4
 ↗ employees( employee_id )
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The employee who took the order: an employee_id of employees. NULL for an order placed online.employee_id
🔗  References employees ( employee_id ) 
  ⧉  order_date
     * date
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      The day the order was placed.order_date
  ⧉  status
     * text
 ⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯⎯
      Where the order stands: pending, shipped, delivered or cancelled.status


Hover a table or a column to read its comment.


insert into orders values (5000, 999, null, date '2026-10-17', 'pending');



    

    

        
        
        
        
    

    
Each answer sends the exercise and whether the answer was right to dbschema.com, without your IP address.

    





    
Exercise 4 of 4

    




Create a table reviews with four integer columns: review_id as its primary key, product_id that must be a product that exists, customer_id that must be a customer that exists, and rating.




    

    

        Expected result
        
It shows once PostgreSQL is ready.

    

    

    
Each check sends the exercise, your SQL and any PostgreSQL error to dbschema.com, without your IP address, so that the hints can improve.

    
    

        Show answer
        
create table reviews (review_id integer primary key, product_id integer not null references products, customer_id integer not null references customers, rating integer not null check (rating between 1 and 5))