Jak dodać klucz obcy do istniejącej tabeli SQLite?

Możesz dodać ograniczenie, jeśli zmienisz tabelę i dodasz kolumnę, która używa ograniczenia.

Najpierw Utwórz tabelę bez parent_id:

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

Następnie zmień tabelę:

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

Proszę sprawdzić https://www.sqlite.org/lang_altertable.html#otheralter

Jedynymi poleceniami zmieniającymi schemat bezpośrednio obsługiwanymi przez SQLite są polecenia "Zmień nazwę tabeli" i "dodaj kolumnę" pokazane powyżej. Jednakże, aplikacje mogą wprowadzać inne dowolne zmiany w formacie tabeli za pomocą prostej sekwencji operacji. Kroki, aby dowolne zmiany w projekcie schematu niektórych tabel X są następujące:

1. Jeśli ograniczenia klucza obcego są enabled, disable them using PRAGMA foreign_keys = OFF.
2. rozpocznij transakcję.
3. Zapamiętaj format wszystkich indeksów i wyzwalaczy związanych z tabela X. informacje te będą potrzebne w kroku 8 poniżej. One way to wykonaj to, aby uruchomić zapytanie takie jak: SELECT type, sql FROM SQLITE_MASTER WHERE tbl_name= 'X'.
4. użyj CREATE TABLE, aby utworzyć nową tabelę "new_X", która znajduje się w pożądany zmieniony format tabeli X. upewnij się, że nazwa " new_X" nie oczywiście zderzaj się z dowolną istniejącą nazwą tabeli.
5. przenieś zawartość z X do new_X używając instrukcji typu: INSERT Do new_X wybierz ... Od X.
6. Drop the old table X: DROP TABLE X.
7. Zmień nazwę new_X na X używając: ALTER TABLE new_X Zmień nazwę na X.
8. Użyj CREATE INDEX I CREATE TRIGGER do rekonstruowania indeksów i wyzwalacze związane z tabelą X. być może należy użyć starego formatu wyzwalacze i indeksy zapisane z kroku 3 powyżej jako przewodnik, dzięki czemu zmiany właściwe dla danej zmiany.
9. Jeśli jakieś widoki odnoszą się do tabeli X w sposób, na który ma wpływ zmienić schemat, a następnie upuścić te widoki za pomocą DROP VIEW i odtworzyć je z wszelkimi zmianami, które są niezbędne do dostosowania schematu Zmiana za pomocą widoku Utwórz.
10. Jeśli ograniczenia klucza obcego były pierwotnie włączone, Uruchom PRAGMA foreign_key_check aby sprawdzić czy zmiana schematu nie została złamana wszelkie ograniczenia klucza obcego.
11. Zatwierdź transakcję rozpoczęty w Kroku 2.
12. jeśli pierwotnie włączono ograniczenia kluczy obcych, można je ponownie teraz.

Powyższa procedura jest całkowicie ogólna i będzie działać nawet jeśli zmiana schematu powoduje zmianę informacji przechowywanych w tabeli. Więc pełna powyższa procedura jest odpowiednia dla zrzucania kolumny, Zmiana kolejności kolumn, dodanie lub usunięcie unikalnego ograniczenia lub klucz główny, dodanie klucza sprawdzającego lub obcego lub nie NULL, lub zmiana typ danych dla kolumny, na przykład.

Jeśli używasz dodatku do Firefoksa SQLite-manager, możesz wykonać następujące czynności:

Zamiast upuszczać i tworzyć tabelę od nowa, można ją zmodyfikować w ten sposób.

W polu tekstowym kolumny kliknij prawym przyciskiem myszy ostatnią nazwę kolumny, aby otworzyć menu kontekstowe i wybrać Edytuj kolumnę. Zauważ, że jeżeli ostatnia kolumna w definicji tabeli jest kluczem podstawowym wtedy trzeba będzie najpierw dodać nową kolumnę a następnie edytować typ kolumny nowej kolumny w polecenie dodania definicji klucza obcego. W polu Typ kolumny należy dodać przecinek i

FOREIGN KEY (parent_id) REFERENCES parent(id)

Definicja po typie danych. Kliknij przycisk Zmień, a następnie kliknij przycisk Tak w oknie dialogowym niebezpieczna operacja.

Odniesienie: SQLite Manager

Tak, możesz, bez dodawania nowej kolumny. Musisz uważać, aby zrobić to poprawnie, aby uniknąć uszkodzenia bazy danych, więc należy całkowicie wykonać kopię zapasową bazy danych przed próbą tego.

Dla Twojego konkretnego przykładu:

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

Lub bardziej ogólnie:

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;

Tak czy inaczej, prawdopodobnie będziesz chciał najpierw zobaczyć, jaka jest definicja SQL przed dokonaniem jakichkolwiek zmian:

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

Jeśli używasz metody replace (), może okazać się ona pomocna przed wykonaniem najpierw przetestuj polecenie replace (), uruchamiając:

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

Możesz!

Spróbuj użyć następującego polecenia, a nie potrzebujesz tymczasowej tabeli. Działa mi w Android Studio.

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

Najpierw dodaj kolumnę w tabeli potomnej Cid jako int następnie alter table z poniższym kodem. W ten sposób możesz dodać klucz obcy Cid jako klucz podstawowy tabeli nadrzędnej i użyć go jako klucz obcy w tabeli podrzędnej ... mam nadzieję, że ci to pomoże, jak dla mnie jest dobre:

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