Primary and Foreign Keys

Info

We have another brief explainer about primary and foreign keys here.

Altering Tables to Add Constraints

It can be difficult to add constraints to a table after table creation. In SQLite, a generic method of creating a table with constraints from a table without constraints is to execute the following SQL, seen below in generic form.

You need to follow these steps to work around the limitation:

• First, set the foreign key constarint check off.
• Next, rename the table to another table name (old_table)
• Then, create a new table (table) with exact structure of the table that you have been renamed.
• After that, copy data from the old_table to the table.
• Finally, turn on the foreign key constraint check on

PRAGMA foreign_keys=off;

BEGIN TRANSACTION;

ALTER TABLE table RENAME TO old_table;

-- define the primary key constraint here
CREATE TABLE table ( ... );

INSERT INTO table SELECT * FROM old_table;

COMMIT;

PRAGMA foreign_keys=on;

Info

What the heck is PRAGMA??? PRAGMA is an SQL command that's used in SQLite specifically, in order to set various values that the database is going to use as part of its configuration. We don't know why it's called that, but it's explained here

In order to add the primary key to the new_roots.id column, add a foreign key between the new_roots.userId->users.id columns, you perform the following steps:

Tip

Just copy and paste the SQL below into DB Browser for SQLite. You don't have to type the entire thing! 👆

-- turn off FOREIGN KEY constraints temporarily
PRAGMA foreign_keys=off;

-- start a transaction. All changes until COMMIT or ROLLBACK
-- are either committed or rolled back.
--BEGIN TRANSACTION;

--
-- fix the users table first so that new_roots can reference it
--

-- first, move the users table to old_users
ALTER TABLE users RENAME TO old_users;

-- now, we can re-create the users table with the correct constraints applied
CREATE TABLE "users" (
    "id"    INTEGER NOT NULL PRIMARY KEY,
    "email" TEXT NOT NULL,
    "first" TEXT NOT NULL,
    "last"  TEXT NOT NULL,
    "username"  TEXT NOT NULL
);

-- fix the users table so that the userId of 4 in new_roots will reference a user
-- this will also allow id to be a PRIMARY KEY
UPDATE old_users SET id = 4 WHERE username="rdoe";

-- copy the user data from the corrected old_users table to the new users table
INSERT INTO users
SELECT * FROM old_users;

-- now we can drop the temporary table that held the incorrect schema and data
DROP TABLE old_users;

--
-- fix the new_roots table 
--

-- first, move the new_roots table to old_roots
ALTER TABLE new_roots RENAME TO old_roots;

-- now, we can re-create the new_roots table with the correct constraints applied
CREATE TABLE "new_roots" (
    "id"    INTEGER NOT NULL PRIMARY KEY,
    "userid"    INTEGER NOT NULL,
    "english"   TEXT NOT NULL,
    "nicodemus" TEXT NOT NULL,
    "root"  TEXT NOT NULL,
    FOREIGN KEY ("userid") REFERENCES "users"("id") ON DELETE CASCADE
);

-- fix NULL ids so that the PRIMARY KEY constraint can be applied
UPDATE old_roots SET id=28 WHERE nicodemus="a?";
UPDATE old_roots SET id=29 WHERE nicodemus="aaaa...!";
UPDATE old_roots SET id=30 WHERE nicodemus="beetlyem";

-- the userId of 4 in new_roots will now correctly reference a user
-- however, we need to make the NULL userId references correctly reference a user
UPDATE old_roots SET userid=4 WHERE nicodemus="stbiiso";
UPDATE old_roots SET userid=4 WHERE nicodemus="beetlyem";

-- copy the new_roots data from the corrected old_roots table to the new_roots table
INSERT INTO new_roots
SELECT * FROM old_roots;

-- now we can drop the temporary table that held the incorrect schema and data
DROP TABLE old_roots;

-- finalize the transaction (if no errors)
--COMMIT;

-- turn the FOREIGN KEY constraints back on
PRAGMA foreign_keys=on;

You may notice that the BEGIN TRANSACTION and COMMIT lines are commented out. If this was run from the command line interface, you would want to include these lines. Since we are running this in DB Browser, the SQL Statement tab automatically wraps everything in a transaction. Since you cannot execute the PRAGMAs inside of a transaction, the code will raise an error if you leave the transaction in.

And always note, any time you want to actually update the database on disk you need to press the Write Changes button.

Here is the new_roots table with constraints and fixed data:

and the fixed users table:

and the new schemas for the tables:

ALTER TABLE Statements

ALTER TABLE new_roots RENAME TO old_roots;

In our case, we used an ALTER TABLE statement to rename the table. You can add/remove columns and change constraints with an ALTER TABLE statement. These statements can be quite limited in SQLite due to the way it stores data. PostGres and other DBMS's have more functionality.

INSERT INTO .. SELECT Statements

INSERT INTO new_roots
SELECT * FROM old_roots;

This is a statement that inserts a blob of data into a table given the selection results from another table. This is very convenient for copying subsets of data from one table to another. The returned results from SELECT must match the columns of the table that INSERT adds data into.

DROP TABLE Statements

DROP TABLE old_roots;

This simply deletes an entire table schema and all of the data in it. A dangerous statement to be used with caution.

PRIMARY KEY Statements

CREATE TABLE "new_roots" (
    "id"    INTEGER NOT NULL PRIMARY KEY,
    "userid"    INTEGER NOT NULL,
    "english"   TEXT NOT NULL,
    "nicodemus" TEXT NOT NULL,
    "root"  TEXT NOT NULL,
    FOREIGN KEY ("userid") REFERENCES "users"("id") ON DELETE CASCADE
);

The PRIMARY KEY constraint is added to the "id" column for new_roots and users. These keys are usually INTEGER type, with a NOT NULL constraint. When you INSERT data into a table with a defined PRIMARY KEY, you don't even have to specify a value for the id or even reference the column. An id will be automatically generated for you.

FOREIGN KEY Statements

CREATE TABLE "new_roots" (
    "id"    INTEGER NOT NULL PRIMARY KEY,
    "userid"    INTEGER NOT NULL,
    "english"   TEXT NOT NULL,
    "nicodemus" TEXT NOT NULL,
    "root"  TEXT NOT NULL,
    FOREIGN KEY ("userid") REFERENCES "users"("id") ON DELETE CASCADE
);

The FOREIGN KEY constraint references the "userid" column for new_roots to the "id" column for users. The referenced key, "users.id", needs to be unique, which usually means it is the PRIMARY KEY for the referenced table.

This specifies that the value in the "userid" column must exist in the "id" column of the "users" table. In other words, I must reference a valid user.

The ON DELETE CASCADE will be described in the mutations for joined data tutorial. Note that in most cases you won't want to add this specification, but it is useful to see what this does and why it exists.

Relationships

Relationships in SQL databases are just made by having columns in one table referencing the values in columns on other tables.

Next Steps

If you've already learned about UPDATE🚀 and DELETE🚀 for a single table, you're ready to learn how to do these operations (as well as INSERT) on joined tables too🚀.