Как добавить внешний ключ в существующую таблицу SQLite?

У меня есть следующая таблица:

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

Как добавить ограничение внешнего ключа для parent_id? Предположим, что внешние ключи включены.

В большинстве примеров предполагается, что вы создаете таблицу - я бы хотел добавить ограничение к существующей.

Ответ 1

Вы не можете.

Хотя синтаксис SQL-92 для добавления внешнего ключа в вашу таблицу будет выглядеть следующим образом:

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

SQLite не поддерживает вариант ADD CONSTRAINT команды ALTER TABLE (sqlite.org: функции SQL, которые SQLite Не выполняется).

Следовательно, единственный способ добавить внешний ключ в sqlite 3.6.1 - во время CREATE TABLE следующим образом:

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

К сожалению, вам придется сохранить существующие данные во временной таблице, отбросить старую таблицу, создать новую таблицу с ограничением FK, а затем скопировать данные обратно из временной таблицы. (sqlite.org - FAQ: Q11)

Ответ 2

Вы можете добавить ограничение, если вы измените таблицу и добавьте столбец, который использует ограничение.

Сначала создайте таблицу без parent_id:

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

Затем измените таблицу:

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

Ответ 3

Пожалуйста, проверьте https://www.sqlite.org/lang_altertable.html#otheralter

Единственными командами изменения схемы, напрямую поддерживаемыми SQLite, являются команды "переименовать таблицу" и "добавить столбец", показанные выше. Однако, приложения могут вносить другие произвольные изменения в формат таблицы используя простую последовательность операций. Шаги по созданию произвольных изменения в схеме схемы некоторой таблицы X выглядят следующим образом:

Если ограничения внешнего ключа включены, отключите их с помощью PRAGMA foreign_keys = OFF.

Запустите транзакцию.

Помните формат всех индексов и триггеров, связанных с таблицу X. Эта информация потребуется на шаге 8 ниже. Один из способов сделайте это, чтобы выполнить запрос следующим образом: SELECT type, sql FROM sqlite_master WHERE tbl_name = 'X'.

Используйте CREATE TABLE для создания новой таблицы "new_X", которая находится в желаемый пересмотренный формат таблицы X. Убедитесь, что имя "new_X" не сталкивается с каким-либо существующим именем таблицы, конечно.

Перенос содержимого из X в new_X с помощью оператора типа INSERT INTO new_X SELECT... FROM X.

Отбросьте старую таблицу X: DROP TABLE X.

Измените имя new_X на X, используя: ALTER TABLE new_X RENAME TO X.

Используйте CREATE INDEX и CREATE TRIGGER для восстановления индексов и триггеры, связанные с таблицей X. Возможно, используйте старый формат триггеры и индексы, сохраненные с шага 3 выше, в качестве руководства, изменения в зависимости от изменений.

Если какие-либо представления относятся к таблице X способом, на который влияет измените схему, затем отбросьте эти представления с помощью DROP VIEW и заново создайте их с любыми изменениями, необходимыми для размещения схемы с помощью CREATE VIEW.

Если ограничения внешнего ключа были первоначально включены, запустите PRAGMA foreign_key_check, чтобы проверить, не изменилось ли изменение схемы любые ограничения внешнего ключа.

Завершить транзакцию, начатую на шаге 2.

Если ограничения внешних ключей были первоначально включены, повторно их использовать Теперь.
Процедура выше полностью общая и будет работать, даже если изменение схемы приводит к изменению информации, хранящейся в таблице. Так полная процедура выше подходит для удаления столбца, изменение порядка столбцов, добавление или удаление ограничения UNIQUE или PRIMARY KEY, добавив ограничения CHECK или FOREIGN KEY или NOT NULL, или, например, изменение типа данных для столбца.

Ответ 4

Если вы используете дополнительный SQL-менеджер Firefox, вы можете сделать следующее:

Вместо того, чтобы отбрасывать и создавать таблицу снова, можно просто изменить ее следующим образом.

В текстовом поле "Столбцы" щелкните правой кнопкой мыши по последнему имени столбца, чтобы отобразить контекстное меню и выберите "Редактировать столбцы". Обратите внимание, что если последний столбец в определении TABLE является PRIMARY KEY, тогда необходимо будет сначала добавить новый столбец, а затем изменить тип столбца нового столбца, чтобы добавить определение FOREIGN KEY. В поле "Тип столбца" добавьте запятую и

FOREIGN KEY (parent_id) REFERENCES parent(id)

после типа данных. Нажмите кнопку "Изменить", а затем нажмите кнопку "Да" в диалоговом окне "Опасная операция".

Ссылка: Sqlite Manager

Ответ 5

Да, вы можете, не добавляя новый столбец. Вы должны быть осторожны, чтобы сделать это правильно, чтобы не повредить базу данных, поэтому вы должны полностью создать резервную копию базы данных, прежде чем пытаться это сделать.

для вашего конкретного примера:

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

или в целом:

pragma writable_schema=1;

// replace the entire table 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;

В любом случае, вы, возможно, захотите сначала посмотреть, что такое определение SQL, прежде чем вносить какие-либо изменения:

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

Если вы используете подход replace(), вам может быть полезно перед выполнением сначала проверить команду replace(), выполнив:

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

Ответ 6

Вы можете попробовать это:

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

Ответ 7

По сути, вы не можете, но можете обойти ситуацию.

Правильный способ добавить ограничение внешнего ключа в существующую таблицу - следующая команда.

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

Затем скопируйте данные parent_Id в newCol, а затем удалите столбец Parent_Id. Следовательно, нет необходимости во временной таблице.

Ответ 8

Сначала добавьте столбец в дочернюю таблицу Cid как int, затем alter table с помощью кода ниже. Таким образом вы можете добавить внешний ключ Cid в качестве основного ключа родительской таблицы и использовать его в качестве внешнего ключа в дочерней таблице... надеюсь, что это поможет вам, поскольку это полезно для меня:

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