[sql] 기존 SQLite 테이블에 외래 키를 어떻게 추가합니까?

다음 표가 있습니다.

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

에 외래 키 제약 조건을 parent_id어떻게 추가 합니까? 외래 키가 활성화되어 있다고 가정합니다.

대부분의 예에서는 테이블을 생성한다고 가정합니다. 기존 테이블에 제약 조건을 추가하고 싶습니다.

답변

당신은 할 수 없습니다.

테이블에 외래 키를 추가하는 SQL-92 구문은 다음과 같습니다.

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

SQLite는 명령 의 ADD CONSTRAINT변형을 지원하지 않습니다ALTER TABLE ( sqlite.org : SQLite가 구현하지 않는 SQL 기능 ).

따라서 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 )

답변

테이블을 변경하고 제약 조건을 사용하는 열을 추가하면 제약 조건을 추가 할 수 있습니다.

먼저 parent_id없이 테이블을 만듭니다.

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

그런 다음 테이블을 변경하십시오.

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

답변

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을 사용하여 테이블 X의 원하는 수정 된 형식으로 된 새 테이블 “new_X”를 생성합니다. 물론 이름 “new_X”가 기존 테이블 이름과 충돌하지 않는지 확인하십시오.

INSERT INTO new_X SELECT … FROM X와 같은 문을 사용하여 X에서 new_X로 콘텐츠를 전송합니다.

이전 테이블 X를 삭제하십시오. DROP TABLE X.

ALTER TABLE new_X RENAME TO X를 사용하여 new_X의 이름을 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 제약 조건 추가 또는 열에 대한 데이터 유형 변경에 적합합니다.

답변

예, 새 열을 추가하지 않고도 할 수 있습니다. 데이터베이스 손상을 방지하려면 올바르게 수행하도록주의해야하므로이를 시도하기 전에 데이터베이스를 완전히 백업해야합니다.

특정 예 :

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 replace(sql, ...) from SQLITE_MASTER where name = 'child' and type = 'table';

답변

Firefox 애드온 sqlite-manager를 사용하는 경우 다음을 수행 할 수 있습니다.

테이블을 삭제하고 다시 생성하는 대신 다음과 같이 수정할 수 있습니다.

열 텍스트 상자에서 나열된 마지막 열 이름을 마우스 오른쪽 버튼으로 클릭하여 상황에 맞는 메뉴를 표시하고 열 편집을 선택합니다. TABLE 정의의 마지막 열이 PRIMARY KEY이면 먼저 새 열을 추가 한 다음 FOREIGN KEY 정의를 추가하기 위해 새 열의 열 유형을 편집해야합니다. 열 유형 상자 내에 쉼표를 추가하고

FOREIGN KEY (parent_id) REFERENCES parent(id)

데이터 유형 후 정의. 변경 버튼을 클릭 한 다음 위험한 작업 대화 상자에서 예 버튼을 클릭합니다.

참조 :
Sqlite Manager

답변

이것을 시도 할 수 있습니다.

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

답변

기본적으로 그럴 수는 없지만 상황을 우회 할 수 있습니다.

기존 테이블에 외래 키 제약 조건을 추가하는 올바른 방법은 다음 명령입니다.

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

그런 다음 복사 PARENT_ID을 받는 데이터를 뉴콜 후 삭제 PARENT_ID의 열을. 따라서 임시 테이블이 필요하지 않습니다.