skip to content

SQL: Creating a lookup table with id reference

 Tweet0 Shares0 Tweets

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

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 | 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.

References

< SQL

Send a message to The Art of Web:


used only for us to reply, and to display your gravatar.

<- copy the digits from the image into this box

press <Esc> or click outside this box to close

Post your comment or question
top