SQLite FOREIGN KEY 설정 - SQLite FOREIGN KEY seoljeong

해당 코드는 kotlin으로 작성되었다.

오류 코드 및 상황

외래키 설정을 해 준 자식 테이블(detail_goal_time_report_db)의 값을 Delete하거나 Insert 할 때

아래와 같이 foreign key mismatch 오류가 발생했다.

Caused by: android.database.sqlite.SQLiteException: foreign key mismatch - "detail_goal_time_report_db" referencing "detail_goal_db" (code 1 SQLITE_ERROR)

해결방안

오류 당시 detail_goal_db 테이블과 detail_goal_time_report_db 테이블의 상황은 다음과 같았다.

db!!.execSQL("CREATE TABLE detail_goal_db (detail_goal_name text PRIMARY KEY, " +
                "icon text, count int, big_goal_name text, color text, " +
                "FOREIGN KEY (big_goal_name) REFERENCES big_goal_db(big_goal_name) ON UPDATE CASCADE ON DELETE CASCADE);")

db!!.execSQL("CREATE TABLE detail_goal_time_report_db (detail_goal_name text, " +
                "lock_date DATE, photo_name text, big_goal_name text, is_active INT, is_complete INT, " +
                "FOREIGN KEY (detail_goal_name, big_goal_name) REFERENCES detail_goal_db(detail_goal_name, big_goal_name) ON UPDATE CASCADE ON DELETE CASCADE);")

이때 외래키로 참조한 big_goal_name이 detail_goal_db에서는 PRIMARY KEY가 아니었기 때문에 오류가 발생했던 것이다...

따라서 big_goal_name을 PRIMARY KEY로 갖고 있는 다른 테이블에서 끌어와 외래키 제약 조건을 걸어 해결했다.

db!!.execSQL("CREATE TABLE detail_goal_time_report_db (detail_goal_name text, " +
                "lock_date DATE, photo_name text, big_goal_name text, is_active INT, is_complete INT, " +
                "FOREIGN KEY (detail_goal_name) REFERENCES detail_goal_db(detail_goal_name) ON UPDATE CASCADE ON DELETE CASCADE, " +
                "FOREIGN KEY (big_goal_name) REFERENCES big_goal_db(big_goal_name) ON UPDATE CASCADE ON DELETE CASCADE);")

참고자료

https://stackoverflow.com/questions/5208245/what-is-causing-foreign-key-mismatch-error

What is causing Foreign Key Mismatch error?

I have an sqlite database structured as follows: CREATE TABLE IF NOT EXISTS Patient ( PatientId INTEGER PRIMARY KEY AUTOINCREMENT ); CREATE TABLE IF NOT EXISTS Event ( PatientId INTEGER REFERE...

stackoverflow.com

https://www.sqlite.org/foreignkeys.html#fk_indexes

SQLite Foreign Key Support

No support for the MATCH clause. According to SQL92, a MATCH clause may be attached to a composite foreign key definition to modify the way NULL values that occur in child keys are handled. If "MATCH SIMPLE" is specified, then a child key is not required t

www.sqlite.org

1. 외래키 (Foreign Key)란?

외래키는 두 테이블을 서로 연결하는 데 사용되는 키이다.

외래키가 포함된 테이블을 자식 테이블이라고 하고 외래키 값을 제공하는 테이블을 부모 테이블이라한다.

2. 외래키 사용시 주의 사항

1) 외래키 값은 NULL이거나 부모 테이블의 기본키 값과 동일해야한다. (참조 무결성 제약조건)

2) 부모 테이블의 기본키, 고유키를 외래키로 지정할 수 있다.

3) 부모 테이블의 기본키, 고유키가 여러개의 컬럼으로 이루어져 있다면 부모가 가진 기본키, 고유키 컬럼을 원하는 개수만큼 묶어서 외래키로 지정할 수 있다. 

CREATE TABLE `parent` (
	`id1` INT(11) NOT NULL,
	`id2` INT(11) NOT NULL,
	`id3` INT(11) NOT NULL,
	`uk1` INT(11) NOT NULL,
	`uk2` INT(11) NOT NULL,
	`uk3` INT(11) NOT NULL,
	PRIMARY KEY (`id1`, `id2`, `id3`),
	UNIQUE KEY (`uk1`, `uk2`, `uk3`)
);

CREATE TABLE `child` (
	`id` INT(11) NOT NULL,
	`id1` INT(11) NOT NULL,
	`id2` INT(11) NOT NULL,
	`uk1` INT(11) NOT NULL,
	`uk2` INT(11) NOT NULL,
	PRIMARY KEY (`id`),
	FOREIGN KEY (`id1`, `id2`) REFERENCES `parent` (`id1`, `id2`),
	FOREIGN KEY (`uk1`, `uk2`) REFERENCES `parent` (`uk1`, `uk2`)
)

4) 외래키로 지정할 두 테이블의 필드는 같은 데이터 타입이어야 한다.

3. 외래키 예제

데이터베이스 마다 선언 하는 방식이 다를 수 있으며, Mysql 기준으로 설명하겠다.

create table department(
  	id int auto_increment primary key,
	name varchar(20) not null,
	code char(13) not null unique key
);

create table employee (
  	id int auto_increment primary key,
	name varchar(20) not null,
	code char(13) not null unique key,
	dept_id int,
	foreign key (dept_id) references department(id)
);

department(부서)와 employee(회사원) 테이블이 있다. department이 부모 테이블이고, employee가 자식 테이블이다.

외래키를 가진 테이블이 자식 테이블이고, 참조되는 테이블이 부모 테이블이다. 

  
  CONSTRAINT [CONSTRAINT_NAME] FOREIGN KEY (자식 테이블 컬럼 명) REFERENCES 참조테이블(부모 테이블 기본키명) 
  ON UPDATE 옵션 ON DELETE 옵션;
  
  # CONSTRAINT [CONSTRAINT_NAME]은 생략이 가능하다.
  

4. 외래키 옵션

1) On Delete

 Cascade : 부모 데이터 삭제 시 자식 데이터도 삭제 

 Set null : 부모 데이터 삭제 시 자식 테이블의 참조 컬럼을 Null로 업데이트

 Set default : 부모 데이터 삭제 시 자식 테이블의 참조 컬럼을 Default 값으로 업데이트

 Restrict : 자식 테이블이 참조하고 있을 경우, 데이터 삭제 불가

No Action : Restrict와 동일, 옵션을 지정하지 않았을 경우 자동으로 선택된다.

  2) On Update

 Cascade : 부모 데이터 업데이트 시 자식 데이터도 업데이트 

 Set null : 부모 데이터 업데이트 시 자식 테이블의 참조 컬럼을 Null로 업데이트

 Set default : 부모 데이터 업데이트 시 자식 테이블의 참조 컬럼을 Default 값으로 업데이트

 Restrict : 자식 테이블이 참조하고 있을 경우, 업데이트 불가

 No Action : Restrict와 동일, 옵션을 지정하지 않았을 경우 자동으로 선택된다.

5. 외래키 추가

ALTER TABLE employee
ADD FOREIGN KEY (dept_id) REFERENCES department(id);

6. 외래키 삭제

외래키를 삭제하려면 CONSTRAINT_NAME을 알아야한다.

select * 
from information_schema.table_constraints
where TABLE_SCHEMA = 'DB명' and TABLE_NAME = '테이블명'

삭제하고 싶은 키의 CONSTRAINT_NAME을 확인한다.

ALTER TABLE [Table_Name]
DROP CONSTRAINT [CONSTRAINT_NAME];

또는

ALTER TABLE [Table_Name]
DROP FOREIGN KEY [CONSTRAINT_NAME];

7. TEST

# 외래키 이름을 검색한다. ex) employee_ibfk_1
select * 
from information_schema.table_constraints
where TABLE_SCHEMA = 'myDB' and TABLE_NAME = 'employee'

# 외래키를 삭제한다.
ALTER TABLE employee
DROP FOREIGN KEY employee_ibfk_1;

#새로운 조건의 외래키를 추가한다.
#부모 행이 삭제되었을 경우 외래키 ID를 NULL로 업데이트한다.
ALTER TABLE employee
ADD CONSTRAINT employee_ibfk_1 FOREIGN KEY (dept_id) REFERENCES department(id) ON DELETE SET NULL;

도움이 되셨다면 공감버튼을 눌러주세요!