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

Question

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

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

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

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

Answer 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)

Answer 2

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

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

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

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

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

Answer 3

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

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

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'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;

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

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

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

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

Answer 4

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

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

1. Если ограничения внешнего ключа включены, отключите их, используя PRAGMA foreign_keys=OFF.
2. Начать транзакцию.
3. Запомните формат всех индексов и триггеров, связанных с таблицей X. Эта информация понадобится на шаге 8 ниже. Один из способов сделать это - выполнить запрос, подобный следующему: тип SELECT, sql FROM sqlite_master WHERE tbl_name='X'.
4. Используйте CREATE TABLE, чтобы создать новую таблицу "new_X", которая находится в желаемом пересмотренном формате таблицы X. Конечно, убедитесь, что имя "new_X" не конфликтует с каким-либо существующим именем таблицы.
5. Перенесите содержимое из X в new_X, используя следующую инструкцию: INSERT INTO new_X SELECT ... FROM X.
6. Оставьте старую таблицу X: DROP TABLE X.
7. Измените имя new_X на X, используя: ALTER TABLE new_X RENAME TO X.
8. Используйте CREATE INDEX и CREATE TRIGGER, чтобы восстановить индексы и триггеры, связанные с таблицей X. Возможно, используйте старый формат триггеров и индексов, сохраненных из шага 3 выше, в качестве руководства, внося изменения в соответствии с изменениями.
9. Если какие-либо представления ссылаются на таблицу X таким образом, на который влияет изменение схемы, то отбросьте эти представления, используя DROP VIEW, и воссоздайте их с любыми изменениями, необходимыми для адаптации изменения схемы, с помощью CREATE VIEW.
10. Если ограничения внешнего ключа были изначально включены, запустите PRAGMA foreign_key_check, чтобы убедиться, что изменение схемы не нарушило никаких ограничений внешнего ключа.
11. Подтвердите транзакцию, начатую на шаге 2.
12. Если ограничения внешних ключей были изначально включены, включите их сейчас.

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

Answer 5

Как Daniel Vassallo , вы не можете этого сделать. Код, который вам нужно использовать, выглядит примерно так:

Учитывая таблицу:

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

Я предполагаю, что вы хотите добавить следующий ключ Foreignk:

      FOREIGN KEY (parent_id) REFERENCES parent(id);

Итак, я бы создал временную таблицу на основе этой таблицы, затем я бы создал новую таблицу как первую, но с внешним ключом, и, наконец, я бы добавил к ней данные временной таблицы:

      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;

Answer 6

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

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

Answer 7

Если вы используете Db Browser для sqlite, вам будет легко изменить таблицу. вы можете добавить внешний ключ в существующую таблицу без написания запроса.

• Откройте свою базу данных в браузере Db,
• Просто щелкните правой кнопкой мыши по таблице и выберите изменить,
• Прокрутите до столбца внешнего ключа,
• дважды щелкните поле, которое хотите изменить,
• Затем выберите таблицу и ее поле и нажмите ОК.

это оно. Вы успешно добавили внешний ключ в существующую таблицу.

Answer 8

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

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

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

FOREIGN KEY (parent_id) REFERENCES parent(id)

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

Ссылка: Sqlite Manager

Answer 9

Создайте внешний ключ к существующей таблице SQLLite:

Прямого способа сделать это для SQL LITE нет. Выполните приведенный ниже запрос, чтобы воссоздать таблицу STUDENTS с внешними ключами. Запустите запрос после создания исходной таблицы STUDENTS и вставки данных в таблицу.

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) 
);

Вставить данные в таблицу СТУДЕНТЫ.

Затем добавьте FOREIGN KEY: сделав BADGE_NO внешним ключом той же таблицы STUDENTS

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;

мы также можем добавить внешний ключ из любой другой таблицы.

Answer 10

Если кому-то еще нужна информация о SQLiteStudio, вы можете легко сделать это через графический интерфейс.

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

Наконец, нажмите зеленую галочку, чтобы зафиксировать изменения в структуре.

ПОМНИТЕ, ЧТО ЭТИ ШАГИ СОЗДАЮТ SQL-СКРИПТЫ, КОТОРЫЕ УДАЛЯЮТ ТАБЛИЦУ И ВОССТАНАВЛИВАЮТ ЕЕ!!

Сделайте резервную копию ваших данных из базы данных.

Answer 11

Вы можете!

Попробуйте следующую команду, и вам не нужна временная таблица. Это работает для меня в Android Studio.

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

Answer 12

Просто чтобы визуально завершить объяснение @Gaurav Ganani, которое отлично сработало для меня (и я надеюсь, что оно сработает для тех, кто использует DB Browser для SQLite), позвольте мне вставить это изображение сюда:

Answer 13

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

Answer 14

Сначала добавьте столбец в дочернюю таблицу 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