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.
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 TABLEpostgres=# \d links
Table "public.links"
Column | Type | Modifiers
--------+----------------------+-----------
type | character varying(16) | not null
name | character varying | not null
url | character varying | not nullWhat 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 TABLEpostgres=# \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:
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.
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 TABLEIf 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 1Then the same error won’t appear when we run the UPDATE query again:
postgres=# UPDATE links SET type='foo';
UPDATE 1If 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.
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 42Now the constraint can be created as described above.
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.
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);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 TABLEIt 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 TABLENote here that we can run multiple ALTER TABLE statements as a single command.
-
Creating a Lookup Table
-
Creating a lookup table with id reference
