Skip to content

Instantly share code, notes, and snippets.

@esfand
Last active February 14, 2023 10:04
Show Gist options
  • Save esfand/9444313 to your computer and use it in GitHub Desktop.
Save esfand/9444313 to your computer and use it in GitHub Desktop.
SQL: Creating a lookup table

SQL: Creating a Lookup SQL Table

Creating a Lookup SQL Table

Origin: http://www.the-art-of-web.com/sql/lookup/[]

A guide to creating a lookup table in PostgreSQL using a foreign key constraint.
The main table will then only accept values that are already present in the lookup table.

1. Creating a Lookup Table

Suppose we start with a single table links with the following structure:

postgres=# CREATE TABLE links (
    type character varying(16) NOT NULL,
    name character varying NOT NULL,
    url character varying NOT NULL
);
CREATE TABLE
postgres=# \d links
           Table "public.links"
 Column |         Type         | Modifiers
--------+----------------------+-----------
 type   | character varying(16) | not null
 name   | character varying    | not null
 url    | character varying    | not null

What we’re going to do is restrict input to the type field to a list of values contained in a separate 'lookup' table.

The first step then is to create the lookup table:

postgres=# CREATE TABLE links_type_lookup (
  name varchar(16) PRIMARY KEY
);

NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "links_type_lookup_pkey"
         for table "links_type_lookup"
CREATE TABLE
postgres=# \d links_type_lookup
     Table "public.links_type_lookup"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 name   | character varying(16) | not null
Indexes:
    "links_type_lookup_pkey" PRIMARY KEY, btree (name)

You can safely ignore the NOTICE - it’s just informing you of the creation of a unique index on the new table. Unless you see an ERROR the table was successfully created.

The result we’re looking for is illustrated in the diagram below:

database relationship
Figure 1. database relationship

In this simple example we’re storing the value from the lookup table directly in the main table. A more common approach is to store an id in the main table referencing an id in the lookup table. That makes it easier to rename the lookup values at a later date.

The new table links_type_lookup has a single field name accepting up to 16 characters with each entry being unique. This table uses name as it’s primary key.

Now we need to inform the main links table that it must use and enforce the constraint.

2. Adding a constraint

We alter the definition of the links table to connect the type field to the matching column of the lookup table using a CONSTRAINT:

postgres=# ALTER TABLE links ADD CONSTRAINT "type_constraint"
    FOREIGN KEY (type) REFERENCES links_type_lookup (name);
ALTER TABLE

If you get an ERROR when trying to add the constraint, it may be that you need to first pre-populate the lookup table or add a primary key (see below).

The table definitions now appears as follows:

postgres=# \d links
           Table "public.links"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 type   | character varying(16) | not null
 name   | character varying     | not null
 url    | character varying     | not null
Foreign-key constraints:
    "type_constraint" FOREIGN KEY (type) REFERENCES links_type_lookup(name)
postgres=# \d links_type_lookup
     Table "public.links_type_lookup"
 Column |         Type          | Modifiers
--------+-----------------------+-----------
 name   | character varying(16) | not null
Indexes:
    "links_type_lookup_pkey" PRIMARY KEY, btree (name)
Referenced by:
    TABLE "links" CONSTRAINT "type_constraint"
                  FOREIGN KEY (type)
                  REFERENCES links_type_lookup(name)

Basically we’ve reached our goal. A simple test will confirm:

postgres=# UPDATE links SET type='foo';
ERROR:  insert or update on table "links" violates foreign key constraint "type_constraint"
DETAIL:  Key (type)=(foo) is not present in table "links_type_lookup".

But if we first insert that value into the lookup table:

postgres=# INSERT INTO links_type_lookup (name) VALUES ('foo');
INSERT 0 1

Then the same error won’t appear when we run the UPDATE query again:

postgres=# UPDATE links SET type='foo';
UPDATE 1

If we then try to delete that value from the lookup table the constraint would be violated so we also get an error:

postgres=# DELETE FROM links_type_lookup WHERE name='foo';
ERROR:  update or delete on table "links_type_lookup" violates
        foreign key constraint "type_constraint" on table "links"
DETAIL: Key (name)=(foo) is still referenced from table "links".

So we’ve achieved our goal. You can’t INSERT values into the type field of the links table that aren’t already present in the lookup table. And you also can’t DELETE values from the lookup table that are still in use.

3. Pre-populating the Lookup Table

If in the example above, the table links already has existing data then before the constraint can be added all values from that column needs to be inserted in the lookup table.

postgres=# INSERT INTO links_type_lookup (name) SELECT DISTINCT type FROM links;
INSERT 0 42

Now the constraint can be created as described above.

4. SQL for this example

Here is the SQL code for setting up this example. No data included:

CREATE TABLE links_type_lookup (
    name character varying(16) PRIMARY KEY
);

CREATE TABLE links (
    type character varying(16) NOT NULL REFERENCES links_type_lookup(name),
    name character varying NOT NULL,
    url character varying NOT NULL
);

We tend to use this kind of shorthand when creating a new database. A pg_dump will put it back into long form:

CREATE TABLE links (
    type character varying(16) NOT NULL,
    name character varying NOT NULL,
    url character varying NOT NULL
);

CREATE TABLE links_type_lookup (
    name character varying(16) NOT NULL
);

ALTER TABLE ONLY links_type_lookup
    ADD CONSTRAINT links_type_lookup_pkey PRIMARY KEY (name);

ALTER TABLE ONLY links
    ADD CONSTRAINT type_constraint
        FOREIGN KEY (type)
        REFERENCES links_type_lookup(name) ON DELETE CASCADE;

The only difference here is that with the shorthand version your constraints and any indexes will be named with default values.

5. Adding a Primary Key

The field you’re referencing needs to be a primary key. If the table/field you want to use as a lookup table doesn’t already have a primary key defined then you can add one using:

postgres=# ALTER TABLE links_type_lookup ADD PRIMARY KEY (name);

6. Editing constraint details

Sometimes a new constraint will be allocated a name "$1" which isn’t very helpful. To rename a constraint, or change other parameters, just DROP and then recreate it as follow:

postgres=# ALTER TABLE links DROP CONSTRAINT "$1";
ALTER TABLE
postgres=# ALTER TABLE links ADD CONSTRAINT "type_constraint"
    FOREIGN KEY (type) REFERENCES links_type_lookup (name);
ALTER TABLE

It can also be useful to add a CASCADE option - to automatically delete records from the links table when the associated value in the lookup table is deleted. Use this with caution:

postgres=# ALTER TABLE links
    DROP CONSTRAINT "type_constraint",
    ADD CONSTRAINT "links_type_fkey"
        FOREIGN KEY (type) REFERENCES links_type_lookup (name)
        ON DELETE CASCADE;
ALTER TABLE

Note here that we can run multiple ALTER TABLE statements as a single command.

  • Creating a Lookup Table

  • Creating a lookup table with id reference

8. References

  • PostgreSQL 9.1: Modifying Tables

  • PostgreSQL 9.1: Constraints

SQL: Creating a lookup table

SQL: Creating a lookup table with id reference

Following on from our article on creating a lookup table in PostgreSQL this article goes one step further by using an id value as the reference instead of the text - so a true relational model.

1. The setup

We’re starting with a simple flat table of links with the following definition and data:

postgres=# CREATE TABLE links (
    type character varying(16) NOT NULL,
    name character varying NOT NULL,
    url character varying NOT NULL
);
postgres=# COPY links (type, name, url) FROM stdin;
HTML	W3C Markup Validation Service	http://validator.w3.org/
SQL	PostgreSQL: The world's most advanced open source database	http://www.postgresql.org/
SQL	MySQL: The world's most popular open source database	http://www.mysql.com/
System	Fail2Ban	http://fail2ban.sourceforge.net/
System	ImageMagick: Convert, Edit, and Compose Images	http://www.imagemagick.org/
Useful Links	Assigned Port Numbers	http://www.iana.org/assignments/port-numbers
Useful Links	Down for everyone or just me?	http://downforeveryoneorjustme.com/
Useful Links	Lynx Viewer: text-only browser	http://www.delorie.com/web/lynxview.html
Web Fonts	Google Web Fonts	http://www.google.com/webfonts
Optimization	GTmetrix: Website Speed and Performance Optimization	http://gtmetrix.com/
Optimization	Resource Expert Droid (REDbot)	http://redbot.org/
CSS	When can I use... Support tables for HTML5, CSS3, etc	http://caniuse.com/
\.

The goal is to move the "type" values into a separate table and reference them using an id.

Using the command line you first need to enter the COPY statement and then hit <enter> before pasting the data. All output shown is from psql (9.1.9).

2. Creating the lookup table

We create the lookup table using a sequence to populate the id field with unique values:

postgres=# CREATE TABLE link_categories (
    id serial,
    name character varying(16) NOT NULL,
    description text,
    PRIMARY KEY(id)
);

NOTICE:  CREATE TABLE will create implicit sequence "link_categories_id_seq"
         for serial column "link_categories.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "link_categories_pkey"
         for table "link_categories"
CREATE TABLE
postgres=# \d link_categories
                                   Table "public.link_categories"
   Column    |       Type            |                          Modifiers
-------------+-----------------------+--------------------------------------------------------------
 id          | integer               | not null default nextval
             |                       | ('link_categories_id_seq'::regclass)
 name        | character varying(16) | not null
             |                       |
 description | text                  |

Indexes:
    "link_categories_pkey" PRIMARY KEY, btree (id)

The serial data type has the effect of creating a sequence and using it to auto-populate the id field as new rows are inserted. The primary key is needed so we can reference the id value from other tables.

For more details on thie refer to the previous article.

3. Populating the lookup table

Before we can link the two tables we need to make sure all the values of the type field are present in the loookup table:

postgres=# INSERT INTO link_categories (name) SELECT DISTINCT type FROM links;
INSERT 0 7

postgres=# SELECT * FROM link_categories;
 id |     name     | description
----+--------------+-------------
  1 | CSS          |
  2 | Optimization |
  3 | HTML         |
  4 | SQL          |
  5 | Useful Links |
  6 | System       |
  7 | Web Fonts    |

The description field is blank at this stage, but it shows one of the advantages of using a relational database as we can now associate extra information with each category without duplication.

4. Linking the tables

First we add a new column categoryid to the links table that will ultimately replace the type column:

postgres=# ALTER TABLE links ADD COLUMN categoryid int;
ALTER TABLE

Now the following statement will populate this field with the correct values based on existing data. And assuming no missing values we can now enforce a NOT NULL requirement:

postgres=# UPDATE links SET categoryid=link_categories.id FROM link_categories
    WHERE links.type=link_categories.name;
UPDATE 12
postgres=# ALTER TABLE links ALTER categoryid SET NOT NULL;
ALTER TABLE

We add the constraint:

postgres=# ALTER TABLE links ADD CONSTRAINT "links_categoryid_fkey"
    FOREIGN KEY (categoryid) REFERENCES link_categories(id);
ALTER TABLE

And finally, but only after checking that the data is correct, we can drop the now redundant type field:

postgres=# ALTER TABLE links DROP COLUMN type;
ALTER TABLE

5. Final status

After all this the database definition has now become:

postgres=# \d links
            Table "public.links"
   Column   |       Type        | Modifiers
------------+-------------------+-----------
 name       | character varying | not null
 url        | character varying | not null
 categoryid | integer           | not null
Foreign-key constraints:
    "links_categoryid_fkey" FOREIGN KEY (categoryid) REFERENCES link_categories(id)
postgres=# \d link_categories
                                   Table "public.link_categories"
   Column    |       Type            |          Modifiers
-------------+-----------------------+-------------------------------------
 id          | integer               | not null default nextval
             |                       | ('link_categories_id_seq'::regclass)
 name        | character varying(16) | not null
 description | text                  |
Indexes:
    "link_categories_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "links" CONSTRAINT "links_categoryid_fkey"
                  FOREIGN KEY (categoryid)
                  REFERENCES link_categories(id)

And in the data you can see that each link now references an id from the new categories table:

postgres=# SELECT * FROM links;
                            name                            |                     url                      | categoryid
------------------------------------------------------------+----------------------------------------------+------------
 When can I use... Support tables for HTML5, CSS3, etc      | http://caniuse.com/                          |          1
 Resource Expert Droid (REDbot)                             | http://redbot.org/                           |          2
 GTmetrix: Website Speed and Performance Optimization       | http://gtmetrix.com/                         |          2
 W3C Markup Validation Service                              | http://validator.w3.org/                     |          3
 MySQL: The world's most popular open source database       | http://www.mysql.com/                        |          4
 PostgreSQL: The world's most advanced open source database | http://www.postgresql.org/                   |          4
 Lynx Viewer: text-only browser                             | http://www.delorie.com/web/lynxview.html     |          5
 Down for everyone or just me?                              | http://downforeveryoneorjustme.com/          |          5
 Assigned Port Numbers                                      | http://www.iana.org/assignments/port-numbers |          5
 ImageMagick: Convert, Edit, and Compose Images             | http://www.imagemagick.org/                  |          6
 Fail2Ban                                                   | http://fail2ban.sourceforge.net/             |          6
 Google Web Fonts                                           | http://www.google.com/webfonts               |          7
(12 rows)

This has been a very simple example, but of a very common problem encountered when a database is being created or upgraded to relational form. Some of the commands are specific to PostgreSQL and will need adjusting for other environments.

When making any structural changes to the database you should always start by making a full backup of the schema and data.

7. References

SQL update fields of one table from fields of another one

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment