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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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          If foreign key constraints are enabled  disable them using PRAGMA   foreign keys OFF    Start a transaction    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     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    Transfer content from X into new X using a statement like  INSERT   INTO new X SELECT     FROM X    Drop the old table X  DROP TABLE X    Change the name of new X to X using  ALTER TABLE new X RENAME TO X    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    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    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    Commit the transaction started in step 2    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

User · Answer

You can try this   ALTER TABLE  Child  ADD COLUMN column name INTEGER REFERENCES parent table name column id

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

Examples related to sql

Examples related to sqlite

Examples related to foreign-keys

Examples related to ddl