foreign key - sql foreign key

SQL FOREIGN KEY — Unlock Table Relationships

Relational databases store information in table cells — in columns that describe aspects of an item and rows which tie together the columns. In a properly decomposed database design — one in which tables are created to reduce data redundancy and improve data integrity — tables must refer to one-another. Foreign keys enforce referential integrity.

This blog post walks through a use case, showing the evolution from initial idea to decomposed tables utilizing foreign keys.

A Back-of-a-Napkin Scribbled Version Initial Use Case

We’re developing an idea to track the handbags we sell. Our first pass at a table to hold the orders might be:

CREATE TABLE orders (
customerINTNOT NULL,
numberINTUNIQUE NOT NULL,
itemVARCHAR(99)NOT NULL
);

We may be in a rush, scribbling designs on napkins, but we remember to make the order number unique and each of the columns require input; we’re trying to avoid future problems. The table of our early sales looks like:

customernumberitem
11234Hermès Fuchsia Diamond-Studded Birkin
21235Hermès Niloticus Crocodile Himalaya Birkin
31236Hermès Chaîne d’Ancre
41237Hermès Niloticus Crocodile Himalaya Birkin
51238Hermès Fuchsia Diamond-Studded Birkin

Refinement #1 — Decomposing A Common Value

The above table shows duplication on the item names — like the 42 characters of "Hermès Niloticus Crocodile Himalaya Birkin” — a duplication which will require more storage space as the sales mount and we copy those 42 characters again and again. Version 1 of our efforts at table decomposition moves the item description into its own table:

CREATE TABLE bags_version_1 (
   id INT UNIQUE NOT NULL,
   bag VARCHAR(99) UNIQUE NOT NULL
);
INSERT INTO bags_version_1
VALUES
( 9999, 'Hermès Blue Crocodile Birkin' ),
( 9998, 'Hermès Chaine d\’Ancre' ),
( 9997, 'Hermès Crocodile Exotic Skin Kelly' ),
( 9996, 'Hermès Fuchsia Diamond-Studded Birkin' ),
( 9995, 'Hermès Ginza Tanaka Crafted Birkin' ),
( 9994, 'Hermès Graphite Crocodile Birkin' ),
( 9993, 'Hermès Matte Birkin Crocodile' ),
( 9992, 'Hermès Niloticus Crocodile Himalaya Birkin' ),
( 9991, 'Hermès Rose Gold Kelly' ),
( 9990, 'Hermès Graphite Porosus Crocodile Birkin with Palladium Hardware' );

Let’s use that refinement to use a FOREIGN KEY to link the orders and items together in the next version of our orders table:

CREATE TABLE orders_version_2 (
customerINTNOT NULL,
numberINTUNIQUE NOT NULL,
itemINT,
FOREIGN KEY (item) REFERENCES bags_version_1(id)
);

Instead of duplicating the long text strings, the item column now REFERENCES the id column of the bags_version_1 table. The contents of our orders table now looks like:

customernumberitem
112349996
212359992
312369998
412379992
512389996

Recreate the original, easy-to-read orders table with a SQL Inner Join:

SELECT
  customer,
  number,
  Bags_version_2.bag AS item
FROM
  orders_version_2
INNER JOIN bags_version_2 ON
  orders_version_2.item = bags_version_2.id ;
customernumberitem
11234Hermès Fuchsia Diamond-Studded Birkin
21235Hermès Niloticus Crocodile Himalaya Birkin
31236Hermès Chaîne d’Ancre
41237Hermès Niloticus Crocodile Himalaya Birkin
51238Hermès Fuchsia Diamond-Studded Birkin

Refinement #2 — Decomposing All The Common Values

A further refinement would be to decompose all the common values, leading to tables of makers and the stylistic bag families:

CREATE TABLE makers (
id INT UNIQUE NOT NULL,
name VARCHAR(22) UNIQUE NOT NULL
) ;
INSERT INTO makers
VALUES
( 1, 'Hermès' ),
( 2, 'Judith Leiber' ),
( 3, 'Mouawad' );
 
CREATE TABLE families (
id INT UNIQUE NOT NULL,
name VARCHAR(33) UNIQUE NOT NULL
) ;
INSERT INTO families
VALUES
    ( 0, '' ),
( 10, 'Birkin' ),
( 11, 'Kelly' );

The next version of our bags table then references the above tables:

CREATE TABLE bags_version_3 (
   id INT UNIQUE NOT NULL,
   maker INT NOT NULL,
   family INT,
   bag VARCHAR(99) UNIQUE NOT NULL,
   FOREIGN KEY (maker) REFERENCES makers(id),
   FOREIGN KEY (family) REFERENCES families(id)
);
INSERT INTO bags_version_3
VALUES
( 9999, 1, 10, 'Blue Crocodile' ),
( 9998, 1, 0, 'Chaine d\’Ancre' ),
( 9997, 1, 11, 'Crocodile Exotic Skin' ),
( 9996, 1, 10, 'Fuchsia Diamond-Studded' ),
( 9995, 1, 10, 'Ginza Tanaka Crafted' ),
( 9994, 1, 10, 'Graphite Crocodile' ),
( 9993, 1, 10, 'Matte Crocodile' ),
( 9992, 1, 10, 'Niloticus Crocodile Himalaya' ),
( 9991, 1, 11, 'Rose Gold' ),
( 9990, 1, 10, 'Graphite Porosus Crocodile with Palladium Hardware' );

Recreate the original orders table with SQL which uses the several FOREIGN KEY references found in the bags_version_3 table, above:

SELECT
  customer,
  number,
  makers.name AS maker,
  families.name AS family,
  bags_version_3.bag AS item
FROM
  orders_version_2
INNER JOIN bags_version_3 ON
  orders_version_2.item = bags_version_3.id
INNER JOIN makers ON
  makers.id = bags_version_3.maker
INNER JOIN families ON
  families.id = bags_version_3.family
ORDER BY customer ;

This decomposition gives the flexibility of extracting data by maker and bag family, which will be useful when generating statistics on the ever-growing business to forecast fortuitous purchases.

One limitation of the schema developed here is that the bag family table is a single flat namespace. Should two makers wind up using the same family name a collision will happen. This limitation would be resolved in a more mature software solution.

Conclusion

In this blog post we’ve covered:

  •  the basics of using a FOREIGN KEY to enforce data integrity between database tables
  • the software development refinement process of table decomposition (which leverages the FOREIGN KEYs to enforce the relationships between tables)
  • the technique of nesting INNER JOINs to create simple tables from complex, related tables

Start Learning

To learn more about SQL, check out other SQL blog posts and enroll in our SQL Nanodegree program.

SQL Sample Table Code

The following will build and populate the sample data referred to in this blog post. Test SQL interactively at  SQL Fiddle or ExtendsClass.

CREATE TABLE orders (
customerINTNOT NULL,
numberINTUNIQUE NOT NULL,
itemVARCHAR(99)NOT NULL
);
INSERT INTO orders
VALUES
( 1, 1234, 'Hermès Fuchsia Diamond-Studded Birkin' ),
( 2, 1235, 'Hermès Niloticus Crocodile Himalaya Birkin' ),
( 3, 1236, 'Hermès Chaîne d\'Ancre' ),
( 4, 1237, 'Hermès Niloticus Crocodile Himalaya Birkin' ),
( 5, 1238, 'Hermès Fuchsia Diamond-Studded Birkin' );
 

CREATE TABLE bags_version_2 (
   id INT UNIQUE NOT NULL,
   bag VARCHAR(99) UNIQUE NOT NULL
);
INSERT INTO bags_version_2
VALUES
( 9999, 'Hermès Blue Crocodile Birkin' ),
( 9998, 'Hermès Chaine d\’Ancre' ),
( 9997, 'Hermès Crocodile Exotic Skin Kelly' ),
( 9996, 'Hermès Fuchsia Diamond-Studded Birkin' ),
( 9995, 'Hermès Ginza Tanaka Crafted Birkin' ),
( 9994, 'Hermès Graphite Crocodile Birkin' ),
( 9993, 'Hermès Matte Birkin Crocodile' ),
( 9992, 'Hermès Niloticus Crocodile Himalaya Birkin' ),
( 9991, 'Hermès Rose Gold Kelly' ),
( 9990, 'Hermès Graphite Porosus Crocodile Birkin with Palladium Hardware' );
 
CREATE TABLE orders_version_3 (
customerINTNOT NULL,
numberINTUNIQUE NOT NULL,
itemINT,
FOREIGN KEY (item) REFERENCES bags_version_1(id)
);
INSERT INTO orders_version_3
VALUES
( 1, 1234, 9996 ),
( 2, 1235, 9992 ),
( 3, 1236, 9998 ),
( 4, 1237, 9992 ),
( 5, 1238, 9996 );

CREATE TABLE makers (
id INT UNIQUE NOT NULL,
name VARCHAR(22) UNIQUE NOT NULL
) ;
INSERT INTO makers
VALUES
( 1, 'Hermès' ),
( 2, 'Judith Leiber' ),
( 3, 'Mouawad' );
 
CREATE TABLE hermes_families (
id INT UNIQUE NOT NULL,
name VARCHAR(33) UNIQUE NOT NULL
) ;
INSERT INTO hermes_families
VALUES
( 10, 'Birkin' ),
( 11, 'Kelly' );
 
CREATE TABLE bags_version_3 (
   id INT UNIQUE NOT NULL,
   maker INT NOT NULL,
   bag VARCHAR(99) UNIQUE NOT NULL,
   FOREIGN KEY (maker) REFERENCES makers(id)
);
INSERT INTO bags_version_3
VALUES
( 9999, 1, 'Blue Crocodile Birkin' ),
( 9998, 1, 'Chaine d\’Ancre' ),
( 9997, 1, 'Crocodile Exotic Skin Kelly' ),
( 9996, 1, 'Fuchsia Diamond-Studded Birkin' ),
( 9995, 1, 'Ginza Tanaka Crafted Birkin' ),
( 9994, 1, 'Graphite Crocodile Birkin' ),
( 9993, 1, 'Matte Birkin Crocodile' ),
( 9992, 1, 'Niloticus Crocodile Himalaya Birkin' ),
( 9991, 1, 'Rose Gold Kelly' ),
( 9990, 1, 'Graphite Porosus Crocodile Birkin with Palladium Hardware' );
 
CREATE TABLE orders_version_2 (
customerINTNOT NULL,
numberINTUNIQUE NOT NULL,
itemINT,
FOREIGN KEY (item) REFERENCES bags_version_2(id)
);
INSERT INTO orders_version_2
VALUES
( 1, 1234, 9996 ),
( 2, 1235, 9992 ),
( 3, 1236, 9998 ),
( 4, 1237, 9992 ),
( 5, 1238, 9996 );