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.
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 https://validator.w3.org/
SQL PostgreSQL: The world's most advanced open source database https://www.postgresql.org/
SQL MySQL: The world's most popular open source database http://www.mysql.com/
System Fail2Ban https://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).
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.
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.
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
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 | https://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 | https://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 | https://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.
References
- Stack Overflow: SQL update fields of one table from fields of another one
Related Articles - Lookup Tables
- SQL Creating a lookup table with id reference
- SQL Creating a Lookup Table