SQL: Creating a Lookup Table

Tweet 0 Shares 0 Tweets 0 Comments

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.

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

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 its primary key.

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

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.

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.

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.

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);

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.

References

PostgreSQL: PostgreSQL 9.1: Modifying Tables
PostgreSQL: PostgreSQL 9.1: Constraints

SQL Creating a lookup table with id reference
SQL Creating a Lookup Table

< SQL

Post your comment or question