SQLite 데이터 타입(NULL, INTEGER, TEXT, REAL, BLOB) / 제약 조건(Constraints) / rowid, PK

SQLite Data Types

 

1. 데이터 타입 종류

1. NULL

NULL value

정보가 없거나 알 수 없음을 의미

 

2. INTEGER

정수

크기에 따라 0,1, 2, 3,4,6 또는 8바이트 같은 가변 크기를 가진다.

 

3. REAL

실수

 

4. TEXT

문자 데이터

 

5. BLOB (Binary Large Object)

입력된 그대로 저장된 데이터 덩어리

바이너리 등 멀티 미디어 파일

예시 ) 이미지 파일

 

 

 

* SQLite에는 별도의 Boolean 타입이 없기 때문에 0(false), 1(true)으로 저장한다.

* SQLite에는 날짜 및 시간을 저장하기 위한 타입이 없다. 대신 SQLite의 built-in "data and time functions"으로 TEXT, REAL 또는 INTEGER 값으로 지정한다.

https://www.sqlite.org/lang_datefunc.html

Date And Time Functions

 

- binary data

데이터의 저장, 처리를 목적으로 0과 1의 이진 형식으로 인코딩 된 파일

기본적으로 컴퓨터의 모든 데이터는 binary data로 이를 필요에 따라서 텍스트 타입으로 변형해서 사용한다.

 

 

2. SQLite Datatypes 특징

1. 동적 타입 시스템 (dynamic type system)

다른 모든 데이터베이스 엔진(MySQL, oracle, PostgreSQL 등)의 정적이고 엄격한 타입 (static, rigid) 이 아닌,

동적 타입 시스템 (dynamic type system)을 이용한다.

 

컬럼에 선언된 데이터 타입에 의해서가 아니라 컬럼에 저장된 값에 따라 데이터 타입이 결정된다.

동일한 컬럼에 정수 1을 넣을 경우 INTEGER 타입이 지정되고, 문자 '1'을 넣을 경우 TEXT 타입으로 지정된다. 엄격하게 타입이 지정된 정적 데이터베이스에서는 불가능한 작업을 유연하게 할 수 있다.

하지만 이것은 다른 데이터베이스와의 호환성 문제가 있기 때문에 테이블 생성시 데이터 타입을 지정하는 것을 권장한다.

 

데이터 타입을 지정하게 되면 SQLite는 입력된 데이터 타입을 지정된 데이터 타입으로 변환한다.

TEXT타입 컬럼에 정수 1을 입력하면 문자타입의 '1'로 저장된다.

자동으로 형변환 할 수 있는 타입은 위와 같다.

 

 

* static, rigid typing 데이터베이스

저장된 값의 데이터 타입은 컬럼에 선언된 데이터타입에 의해 결정된다.

 

 

2. 타입 선호도 (Type Affinity)

특정 컬럼에 저장된 데이터에 권장되는 타입

데이터 타입 작성시 SQLite의 5가지 데이터 타입이 아닌 다른 데이터 타입을 선언한다면 내부적으로 각 타입의 지정된 선호도에 따라 5가지로 인식된다.

INT같은 SQlite에서 지원하지 않는 데이터타입이 입력되어도 INTEGER으로 인식되는 것이다.

 

 

 

 

제약조건(Constraints)

1. 제약조건

입력하는 자료에 대해 제약을 정한다. 

제약에 맞지 않으면 입력이 거부된다.

데이터의 무결성을 유지하기 위함이다.

 

 

2. 데이터 무결성

데이터에 대한 정확성, 일관성을 보장하기 위해 데이터 변경, 수정시 제한을 두어 데이터의 정확성을 보증하는 것이다.

데이터 베이스에 저장된 데이터의 무결성을 보장하고 데이터베이스의 상태를 일관되게 유지한다.

무결성 => 데이터의 정확성, 일관성

 

 

3. 제약조건 종류

1. NOT NULL

컬럼이 NULL값을 허용하지 않도록 한다.

NOT NULL 제약조건 없이 기본적으로는 NULL 값을 허용한다.

 

 

2. UNIQUE

컬럼의 모든 값이 서로 구별되거나 고유한 값이 되도록한다.

중복될 수 없다.

 

 

3. PRIMARY KEY

테이블에서 행의 고유성을 식별하는데 사용되는 컬럼

각 테이블에는 하나의 기본키만 있다.

NOT NULL 제약 조건이 포함되어있다.

데이터 타입 INT, BIGINT와 같은 것들은 허용되지 않고 INTEGER이어야한다.

 

 

 

4. AUTOINCREMENT

PRIMARY KEY 와 같이 사용될 수 있는 제약조건으로 사용되지 않은 값이나 이전에 삭제된 행의 값을 재사용하는 것을 방지 한다. Django에서 테이블 생성시 id 컬럼에 기본적으로 사용하는 제약조건이다.

 

CREAGE TABLE talbe_name (
id INTEGER PRIMARY KEY AUTOINCREMENT,
);

id를 1, 2, 3 으로 사용하다가 3을 삭제 후 데이터를 추가하면 3이 아닌 4가 id로 반영된다.

Django에서 테이블 생성시 id 컬럼에 기본적으로 사용되는 제약조건이다.

 

 

 

4. rowid

테이블 행을 고유하게 식별하는 64비트 부호있는 정수값, 암시적 자동 증가 컬럼

테이블을 생성할 때 마다 rowid라는 암시적 자동 증가 컬럼이 자동으로 생성된다.

테이블에 새 행을 삽입할 때 마다 정수 값을 자동으로 할당하고, 1에서 부터 시작한다.

 

SQLite에서는 명시적으로 숨겨진 기본키인 rowid를 추가하고, INTERGER PRIMARY KEY 컬럼은 rowid를 대체한다.

rowid는 숨겨질 컬럼이라서 SELECT 문으로 질의할 때 *를 사용해서는 가지고 올 수 없고, 명시적으로 'rowid'라고 지정해야 해당 컬럼을 조회할 수 있다.

 

만약 INTERGER PRIMARY KEY 키워드를 가진 컬럼을 직접 만들면, 이 컬럼은 rowid컬럼의 별칭(alias)이 된다.

새로 만든 컬럼으로 rowid에 엑세스 할 수 있고 rowid 이름으로도 여전히 엑세스 가능하다.

TEXT 타입의 PK가 지정된 경우 SELECT rowid로 rowid를 조회하면 컬럼명은 rowid인 데이터를 조회할 수 있다.

INTEGER 타입의 PK가 지정된 경우는 SELECT rowid이나 SELECT PK컬럼명으로 rowid를 조회할 수 있고, 컬럼명은 (대체된) 지정한 PK 컬럼명으로 보인다.

 

데이터 삽입시에 rowid 또는 INTERGER PRIMARY KEY 컬럼에 명시적으로 값이 지정되지 않은 경우는 SQLite는 테이블에서 가장 큰 rowid의 다음 순차 정수를 자동으로 할당한다.

AUTOINCREMENT가 설정된 것은 삭제 된 번호 다음이 지정된다.

 

데이터가 최대 값에 도달하고 새 행을 삽입하려고 하면 SQLite는 사용되지 않는 정수를 찾아서 사용한다.

만약 SQLite가 사용되지 않은 정수를 찾을 수 없으면 SQLITE_FULL 에러가 발생한다.

일부 행을 삭제하고 새 행을 삽입하면 SQLite는 삭제된 행에서 AUTOINCREMENT가 설정된 것이 아니라면 rowid값을 재사용하려고 시도한다.