How do I add a foreign key to an existing SQLite table?

Question

I have the following table:

CREATE TABLE child( 
  id INTEGER PRIMARY KEY, 
  parent_id INTEGER, 
  description TEXT);

How do I add a foreign key constraint on parent_id? Assume foreign keys are enabled.

Most examples assume you're creating the table - I'd like to add the constraint to an existing one.

Lurid

The SQLite ALTER command only supports "rename table" and "add column". However, we can make other arbitrary changes to the format of a table using a simple sequence of operations. Check my answer – Bib

Answer 1

You can't.

Although the SQL-92 syntax to add a foreign key to your table would be as follows:

ALTER TABLE child ADD CONSTRAINT fk_child_parent
                  FOREIGN KEY (parent_id) 
                  REFERENCES parent(id);

SQLite doesn't support the ADD CONSTRAINT variant of the ALTER TABLE command (sqlite.org: SQL Features That SQLite Does Not Implement).

Therefore, the only way to add a foreign key in sqlite 3.6.1 is during CREATE TABLE as follows:

CREATE TABLE child ( 
    id           INTEGER PRIMARY KEY, 
    parent_id    INTEGER, 
    description  TEXT,
    FOREIGN KEY (parent_id) REFERENCES parent(id)
);

Unfortunately you will have to save the existing data to a temporary table, drop the old table, create the new table with the FK constraint, then copy the data back in from the temporary table. (sqlite.org - FAQ: Q11)

Wakeen

I think it is easier to rename the old table, create the new table and copy the data back in. Then you can drop the old table. – Alathia

Yes, that is easier. I was just quoting the sqlite FAQ: sqlite.org/faq.html#q11. In fact, RENAME TO is one of the few ALTER TABLE variants that is currently supported in sqlite 3. – Wakeen

Shouldn't it be: FOREIGN KEY (parent_id) REFERENCES parent(id) True, Jonathan didn't give the name of the "parent table". In fact, the table should be named person, but... – Gyration

This seems to be a big problem to me. Usually when you dump a database, you export CREATE TABLE commands first. Then INSERT INTO commands, and finally ADD CONSTRAINT commands. If there is circular (foreign key value) dependency in your data, then you cannot insert your data while foreign keys are enforced. But if you cannot add the foreign key constraints later, then you are stuck. Of course there are deferred constraints, but this is very clumsy. – Abjure

DO NOT rename old table as said in first comment if other tables have references to this table! In this case you will have to recreate all this tables too. – Wisla

There is a perfectly viable answer by @Jorge Novaes with less votes. Down-voted this answer. – Biopsy

@Abjure You can disable the foreign keys (PRAGMA foreign_keys = OFF;) while you are inserting the data, and then enable it again. (PRAGMA foreign_keys = OFF;) – Rolfston

It is possible to add a column with the foreign key constraint. Please see my answer. – Purgatorial

This is only partly correct. It is entirely possible using supported sqlite3 commands, however, it must be executed property to avoid database corruption. see my answer below. – Unroot

@Alathia When following your procedure to insert a new column, how do you prevent foreign keys in other tables from changing their reference to the renamed and deleted table? – Aut

Answer 2

You can add the constraint if you alter table and add the column that uses the constraint.

First, create table without the parent_id:

CREATE TABLE child( 
  id INTEGER PRIMARY KEY,  
  description TEXT);

Then, alter table:

ALTER TABLE child ADD COLUMN parent_id INTEGER REFERENCES parent(id);

Depolarize

Good to get used to this sequence, but this doesn't answer the actual question: I'd like to add the constraint to an existing one. – Kopeisk

@wolf - Necro I know, but... Add new column, copy values from old column to new column, delete old column. – Rhigolene

Answer 3

Yes, you can, without adding a new column. You have to be careful to do it correctly in order to avoid corrupting the database, so you should completely back up your database before trying this.

for your specific example:

CREATE TABLE child(
  id INTEGER PRIMARY KEY,
  parent_id INTEGER,
  description TEXT
);

--- create the table we want to reference
create table parent(id integer not null primary key);

--- now we add the foreign key
pragma writable_schema=1;
update SQLITE_MASTER set sql = replace(sql, 'description TEXT)',
    'description TEXT, foreign key (parent_id) references parent(id))'
) where name = 'child' and type = 'table';

--- test the foreign key
pragma foreign_keys=on;
insert into parent values(1);
insert into child values(1, 1, 'hi'); --- works
insert into child values(2, 2, 'bye'); --- fails, foreign key violation

or more generally:

pragma writable_schema=1;

// replace the entire table's SQL definition, where new_sql_definition contains the foreign key clause you want to add
UPDATE SQLITE_MASTER SET SQL = new_sql_definition where name = 'child' and type = 'table';

// alternatively, you might find it easier to use replace, if you can match the exact end of the sql definition
// for example, if the last column was my_last_column integer not null:
UPDATE SQLITE_MASTER SET SQL = replace(sql, 'my_last_column integer not null', 'my_last_column integer not null, foreign key (col1, col2) references other_table(col1, col2)') where name = 'child' and type = 'table';

pragma writable_schema=0;

Either way, you'll probably want to first see what the SQL definition is before you make any changes:

select sql from SQLITE_MASTER where name = 'child' and type = 'table';

If you use the replace() approach, you may find it helpful, before executing, to first test your replace() command by running:

select replace(sql, ...) from SQLITE_MASTER where name = 'child' and type = 'table';

Unroot

This was the easier solution for me, thanks for sharing that! :) – Cantle

Answer 4

Please check https://www.sqlite.org/lang_altertable.html#otheralter

The only schema altering commands directly supported by SQLite are the "rename table" and "add column" commands shown above. However, applications can make other arbitrary changes to the format of a table using a simple sequence of operations. The steps to make arbitrary changes to the schema design of some table X are as follows:

1. If foreign key constraints are enabled, disable them using PRAGMA foreign_keys=OFF.
2. Start a transaction.
3. Remember the format of all indexes and triggers associated with table X. This information will be needed in step 8 below. One way to do this is to run a query like the following: SELECT type, sql FROM sqlite_master WHERE tbl_name='X'.
4. Use CREATE TABLE to construct a new table "new_X" that is in the desired revised format of table X. Make sure that the name "new_X" does not collide with any existing table name, of course.
5. Transfer content from X into new_X using a statement like: INSERT INTO new_X SELECT ... FROM X.
6. Drop the old table X: DROP TABLE X.
7. Change the name of new_X to X using: ALTER TABLE new_X RENAME TO X.
8. Use CREATE INDEX and CREATE TRIGGER to reconstruct indexes and triggers associated with table X. Perhaps use the old format of the triggers and indexes saved from step 3 above as a guide, making changes as appropriate for the alteration.
9. If any views refer to table X in a way that is affected by the schema change, then drop those views using DROP VIEW and recreate them with whatever changes are necessary to accommodate the schema change using CREATE VIEW.
10. If foreign key constraints were originally enabled then run PRAGMA foreign_key_check to verify that the schema change did not break any foreign key constraints.
11. Commit the transaction started in step 2.
12. If foreign keys constraints were originally enabled, reenable them now.

The procedure above is completely general and will work even if the schema change causes the information stored in the table to change. So the full procedure above is appropriate for dropping a column, changing the order of columns, adding or removing a UNIQUE constraint or PRIMARY KEY, adding CHECK or FOREIGN KEY or NOT NULL constraints, or changing the datatype for a column, for example.

Bib

Answer 5

As @Daniel Vassallo has said, you can't do it. The code that you have to use is something like this:

Given the table:

CREATE TABLE child( 
id INTEGER PRIMARY KEY, 
parent_id INTEGER, 
description TEXT);

I assume that you want to add the following Foreignk Key:

FOREIGN KEY (parent_id) REFERENCES parent(id);

So I would create a temporary table based on that table, then I would create a new table as the first one but with the Foreign Key and finally I would add the data of the temporary table to it:

CREATE TEMPORARY TABLE temp AS
SELECT 
    id,
    parent_id,
    description
FROM child;

DROP TABLE child;

CREATE TABLE child (
    id INTEGER PRIMARY KEY, 
    parent_id INTEGER, 
    description TEXT,
    FOREIGN KEY(parent_id) REFERENCES parent(id));

INSERT INTO child
 (  id,
    parent_id,
    description)
SELECT
    id,
    parent_id,
    description
FROM temp;

Detraction

Didn't know this handy shorthand syntax to copy a table (CREATE AS). – Wizardry

Answer 6

You can try this:

ALTER TABLE [Child] ADD COLUMN column_name INTEGER REFERENCES parent_table_name(column_id);

Saxtuba

Answer 7

If you are using the Firefox add-on sqlite-manager you can do the following:

Instead of dropping and creating the table again one can just modify it like this.

In the Columns text box, right click on the last column name listed to bring up the context menu and select Edit Column. Note that if the last column in the TABLE definition is the PRIMARY KEY then it will be necessary to first add a new column and then edit the column type of the new column in order to add the FOREIGN KEY definition. Within the Column Type box , append a comma and the

FOREIGN KEY (parent_id) REFERENCES parent(id)

definition after data type. Click on the Change button and then click the Yes button on the Dangerous Operation dialog box.

Reference: Sqlite Manager

Kopje

Answer 8

If you use Db Browser for sqlite ,then it will be easy for you to modify the table. you can add foreign key in existing table without writing a query.

• Open your database in Db browser,
• Just right click on table and click modify,
• At there scroll to foreign key column,
• double click on field which you want to alter,
• Then select table and it's field and click ok.

that's it. You successfully added foreign key in existing table.

Rosemaryrosemond

I can do this with DB Browser version 3.10.1, but the information is not saved. If make the changes, click OK and then click on "modify" again, the changes have disappeared. With which version does this work? – Commutual

It worked smoothly for me! I added an image down below because GUI is not straight forward it was not clear for me that the scroll has to be horizontal. Thanks! – Fungosity

@Commutual - I'm fairly sure you have to commit the changes after you make any modifications. Use the menu: File > Write Changes. – Unmoor

Answer 9

Create a foreign key to the existing SQLLite table:

There is no direct way to do that for SQL LITE. Run the below query to recreate STUDENTS table with foreign keys. Run the query after creating initial STUDENTS table and inserting data into the table.

CREATE TABLE    STUDENTS    (       
    STUDENT_ID  INT NOT NULL,   
    FIRST_NAME  VARCHAR(50) NOT NULL,   
    LAST_NAME   VARCHAR(50) NOT NULL,   
    CITY    VARCHAR(50) DEFAULT NULL,   
    BADGE_NO    INT DEFAULT NULL
    PRIMARY KEY(STUDENT_ID) 
);

Insert data into STUDENTS table.

Then Add FOREIGN KEY : making BADGE_NO as the foreign key of same STUDENTS table

BEGIN;
CREATE TABLE STUDENTS_new (
    STUDENT_ID  INT NOT NULL,   
    FIRST_NAME  VARCHAR(50) NOT NULL,   
    LAST_NAME   VARCHAR(50) NOT NULL,   
    CITY    VARCHAR(50) DEFAULT NULL,   
    BADGE_NO    INT DEFAULT NULL,
    PRIMARY KEY(STUDENT_ID) ,
    FOREIGN KEY(BADGE_NO) REFERENCES STUDENTS(STUDENT_ID)   
);
INSERT INTO STUDENTS_new SELECT * FROM STUDENTS;
DROP TABLE STUDENTS;
ALTER TABLE STUDENTS_new RENAME TO STUDENTS;
COMMIT;

we can add the foreign key from any other table as well.

Magenta

Answer 10

In case somebody else needs info on SQLiteStudio, you can easily do it form it's GUI.

Double-click on the column and double-click foreign key row, then tick foreign key and click configure. You can add the reference column, then click OK in every window.

Finally click on the green tick to commit changes in the structure.

BE AWARE THAT THESE STEPS CREATE SQL SCRIPTS THAT DELETES THE TABLE AND RECREATES IT!!

Backup your data from the database.

Glaser

Answer 11

Just to visually complete @Gaurav Ganani explanation, which worked fine for me (and i hope it works for those using DB Browser for SQLite), let me paste this image here:

Fungosity

Answer 12

Open your database, right click your table, select modify database. In the window which will open select the column you want to make the foreign key and scroll to your right you will find a column written foreign key and easily select the table yo are referencing to plus the column.

Nuisance

Welcome to SO! Your answer refers to a database tool you don't specify. This is probably not useful as a DDL command is asked for. – Psychosurgery

Answer 13

Basically you cannot but you can bypass the situation.

The correct way to add the foreign key constraint to an existing table is the following command.

db.execSQL("alter table child add column newCol integer REFERENCES parent(parent_Id)");

Then copy the parent_Id data to the newCol and then delete the Parent_Id column. Hence, no need for temporary table.

Purgatorial

It seems you didn't read the question carefully. The problem was to add a foreign constraint only, not to add a column with a constraint. – Kopeisk

Nope. It does not answer the question asked. – Koumis

Answer 14

First add a column in child table Cid as int then alter table with the code below. This way you can add the foreign key Cid as the primary key of parent table and use it as the foreign key in child table ... hope it will help you as it is good for me:

ALTER TABLE [child] 
  ADD CONSTRAINT [CId] 
  FOREIGN KEY ([CId]) 
  REFERENCES [Parent]([CId]) 
  ON DELETE CASCADE ON UPDATE NO ACTION;
GO

Kalat

This is not valid in SQLite. Also this is MS SQL syntax. – Huge