SQL: Creating a Lookup Table
A guide to creating a lookup table in PostgreSQL using a foreign key constraint. The primary 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:
Table "public.links"
Column | Type | Modifiers
----------+----------------------+-----------
name | character varying |
url | character varying |
What we're going to do is add a third column, type, which we want to be constrained to allow only certain values. These values are going to be sourced from a 'lookup table' that we first need to create:
CREATE TABLE type_lookup (
name varchar(8) PRIMARY KEY
) WITHOUT oids;
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "type_lookup_pkey" for table "type_lookup"
Note: You can safely ignore the NOTICE as it's just informing you of the creation of an index on the new table.
The result we're looking for is illustrated in the diagram below:
This arrangement is different from a one-to-many relationship as the type_lookup table is only referenced when inserting or updating and will not be used in SELECT statements (because the actual value already appears in the type table).
The new table, type_lookup, has a single field, name, which will accept up to 8 characters with each entry being unique. This table will use name as it's primary key and therefore doesn't require an oids column.
The links table acquires an extra field, type, which will only accept values already present in the lookup table.
Adding a Constraint
Back to the links table and adding that extra column:
ALTER TABLE links ADD COLUMN type varchar(8) REFERENCES type_lookup (name);
The table definition now appears as follows:
Table "public.links"
Column | Type | Modifiers
----------+----------------------+-----------
name | character varying |
url | character varying |
type | character varying(8) |
Foreign-key constraints:
"$1" FOREIGN KEY (type) REFERENCES type_lookup(name)
Basically we've reached our goal. A simple test will confirm this:
UPDATE links set type='foo';
ERROR: insert or update on table "links" violates foreign key constraint "$1"
DETAIL: Key (type)=(foo) is not present in table "type_lookup".
But if we first insert that value into the lookup table:
INSERT INTO type_lookup (name) VALUES ('foo');
INSERT 0 1
Then the error won't appear when we run the UPDATE query again:
UPDATE links SET type='foo';
If we then try to delete that value from the lookup table the constraint would be violated so we also get an error:
DELETE FROM type_lookup WHERE name='foo';
ERROR: update or delete on "type_lookup" violates foreign key constraint "$1" on "links"
DETAIL: Key (name)=(foo) is still referenced from table "links".
So we've achieved our goal. The fields links.type and type_lookup.name are inextricably linked.
Naming or Renaming a Constraint
As you can see above, the constraint has been allocated a name "$1" which isn't exactly descriptive. In version 8 a more meaningful name is assigned so it's not such a problem, but let's see how we go about renaming it in earlier versions:
ALTER TABLE links DROP CONSTRAINT "$1";
ALTER TABLE links ADD CONSTRAINT type_constraint FOREIGN KEY (type) REFERENCES type_lookup (name);
Note: If you get an error when trying to add a constraint, it may be that you need to first pre-populate the lookup table (see below for details).
The main reason for doing this is so that any warnings or errors become easier to decipher. In this simple example we have a single constraint on the table so there's no confusion, but if we had multiple constraints then "$1", "$2", ... quickly becomes meaningless.
Table "public.links"
Column | Type | Modifiers
----------+----------------------+-----------
name | character varying |
url | character varying |
type | character varying(8) |
Foreign-key constraints:
"type_constraint" FOREIGN KEY (type) REFERENCES type_lookup(name)
As far as I know there's no way to rename a constraint without dropping and recreating it.
Pre-populating the Lookup Table
If in the example above, the table links already had a column called type with existing data, then before the constraint can be added, all values from that column need to be present in the lookup table.
INSERT INTO type_lookup (name) SELECT DISTINCT type FROM links;
Now the constraint can be created as described above.
Adding a Primary Key
The field you're referencing needs to be a primary key. If the table you want to use as a reference doesn't have a primary key defined (which will be the case if you're relying on oids) then you can add one using:
ALTER TABLE table ADD PRIMARY KEY (field);
