Comment ajouter une clé étrangère à une table sqlite (3.6.21) existante ?

Question

J'ai le tableau suivant :

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

Comment ajouter une contrainte de clé étrangère sur parent_id ? Supposons que les clés étrangères sont activées.

La plupart des exemples supposent que vous créez la table. J'aimerais ajouter la contrainte à une table existante.

Answer 1

Tu ne peux pas.

Bien que la syntaxe SQL-92 pour ajouter une clé étrangère à votre table soit la suivante :

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

SQLite ne supporte pas le site ADD CONSTRAINT variante de la ALTER TABLE ( sqlite.org : Caractéristiques SQL que SQLite n'implémente pas ).

Par conséquent, la seule façon d'ajouter une clé étrangère dans sqlite 3.6.1 est pendant le processus d'ajout de clé étrangère. CREATE TABLE comme suit :

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

Malheureusement, vous devrez enregistrer les données existantes dans une table temporaire, supprimer l'ancienne table, créer la nouvelle table avec la contrainte FK, puis recopier les données à partir de la table temporaire. ( sqlite.org - FAQ : Q11 )

Answer 2

Vous pouvez ajouter la contrainte si vous modifiez la table et ajoutez la colonne qui utilise la contrainte.

Tout d'abord, créez une table sans le parent_id :

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

Ensuite, modifiez la table :

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

Answer 3

Oui, vous pouvez, sans ajouter une nouvelle colonne. Vous devez faire attention à le faire correctement afin d'éviter de corrompre la base de données, vous devez donc sauvegarder complètement votre base de données avant d'essayer.

pour votre exemple spécifique :

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

ou plus généralement :

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;

Quoi qu'il en soit, vous voudrez probablement voir d'abord quelle est la définition SQL avant d'apporter des modifications :

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

Si vous utilisez l'approche replace(), vous trouverez peut-être utile, avant de l'exécuter, de tester d'abord votre commande replace() en l'exécutant :

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

Answer 4

Veuillez vérifier https://www.sqlite.org/lang_altertable.html#otheralter

Les seules commandes de modification de schéma directement prises en charge par SQLite sont les commandes suivantes sont les commandes "rename table" et "add column" présentées ci-dessus. Cependant, les applications peuvent effectuer d'autres changements arbitraires au format d'une table en utilisant une simple séquence d'opérations. Les étapes pour effectuer des de la conception du schéma d'une table X sont les suivantes :

1. Si les contraintes de clé étrangère sont activées, désactivez-les en utilisant PRAGMA foreign_keys=OFF.
2. Commencez une transaction.
3. Se souvenir du format de tous les index et déclencheurs associés à la table X. Cette information sera nécessaire à l'étape 8 ci-dessous. Une façon de Une façon de le faire est d'exécuter une requête comme la suivante : SELECT type, sql FROM sqlite_master WHERE tbl_name='X'.
4. Utilisez CREATE TABLE pour construire une nouvelle table "new_X" au format révisé souhaité. format révisé de la table X. Assurez-vous que le nom "new_X" n'entre pas en conflit n'entre pas en collision avec un nom de table existant, bien sûr.
5. Transférer le contenu de X dans new_X en utilisant une instruction comme : INSERT INTO new_X SELECT ... FROM X.
6. Supprimez l'ancienne table X : DROP TABLE X.
7. Changez le nom de new_X en X en utilisant : ALTER TABLE new_X RENAME TO X.
8. Utilisez CREATE INDEX et CREATE TRIGGER pour reconstruire les index et les triggers. triggers associés à la table X. Utilisez peut-être l'ancien format de des déclencheurs et des index sauvegardés à l'étape 3 ci-dessus comme guide, en effectuant des en apportant les modifications appropriées à l'altération.
9. Si des vues font référence à la table X d'une manière qui est affectée par le changement de schéma, abandonnez ces vues à l'aide de DROP VIEW et recréez les recréer avec les modifications nécessaires pour tenir compte de la modification du changement de schéma en utilisant CREATE VIEW.
10. Si les contraintes de clé étrangère étaient initialement activées, exécutez PRAGMA foreign_key_check pour vérifier que la modification du schéma n'a pas rompu aucune contrainte de clé étrangère.
11. Valider la transaction commencée à l'étape 2.
12. Si les contraintes de clés étrangères étaient initialement activées, réactivez-les maintenant.

La procédure ci-dessus est tout à fait générale et fonctionnera même si l'option changement de schéma entraîne une modification des informations stockées dans la table. Ainsi, la procédure complète ci-dessus est appropriée pour supprimer une colonne, changer l'ordre des colonnes, ajouter ou supprimer une contrainte UNIQUE ou PRIMARY KEY, ajouter des contraintes CHECK ou FOREIGN KEY ou NOT NULL, ou changer le type de données d'une colonne, par exemple.

Answer 5

Comme @Daniel Vassallo a dit, vous ne pouvez pas le faire. Le code que vous devez utiliser est quelque chose comme ça :

Étant donné le tableau :

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

Je suppose que vous voulez ajouter la clé étrangère suivante :

FOREIGN KEY (parent_id) REFERENCES parent(id);

Je créerais donc une table temporaire basée sur cette table, puis je créerais une nouvelle table comme la première mais avec la clé étrangère et enfin j'y ajouterais les données de la table temporaire :

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;