Data, Entity, Domain, and Referential Integrity in Databases
데이터 무결성, 개체 무결성, 영역 무결성, 참조 무결성
Contents
1️⃣ 데이터 무결성 (Data Integrity)
2️⃣ 개체 무결성 (Entity Integrity)
3️⃣ 영역 무결성 (Domain Integrity)
4️⃣ 참조 무결성 (Referential Integrity)
1️⃣ 데이터 무결성 (Data Integrity)
데이터 무결성은 데이터의 정확성, 일관성, 유효성, 신뢰성을 보장하기 위한 개념으로, 유효하지 않은 데이터로부터 데이터베이스를 보호하는 것을 의미한다. 데이터베이스 내의 데이터가 현실 세계의 올바른 데이터를 반영하도록 하며, 데이터 무결성을 강화함으로써 데이터의 신뢰성을 높일 수 있다.
- 데이터 무결성 강화는 데이터가 유효한 상태를 유지하도록 관리하고 보호하는 과정이다.
중요한 부분:
데이터의 정확성, 일관성, 유효성, 신뢰성 보장:
- 데이터 무결성은 잘못된 수정이나 입력으로부터 데이터를 보호하여, 데이터베이스 내의 데이터가 실제 현실을 올바르게 반영하도록 한다.
현실 세계와 데이터의 일치:
- 데이터베이스의 데이터가 현실 세계의 실제 데이터를 정확하게 표현하고 있는지를 확인하고, 그 상태를 유지하는 것이 중요합니다.
무결성 강화(enforcement):
- 데이터 무결성을 유지하기 위해서는 일정한 규칙이나 제약 조건(예: 기본 키, 외래 키, 고유 제약 등)을 강제하여 데이터의 일관성을 지속적으로 관리해야 한다.
요약된 핵심:
- 데이터 무결성은 데이터의 정확성과 신뢰성을 보장하는 중요한 개념으로, 잘못된 데이터 수정으로부터 데이터를 보호하여 데이터베이스의 신뢰성을 유지하는 데 필수적이다.
무결성 강화의 필요성
데이터 무결성 강화는 데이터 품질을 유지하기 위해 필수적인 과정이다. 프로그램 완성 후나 데이터 축적 후에는 데이터 클린징이나 무결성 강화에 큰 비용이 발생할 수 있으므로, 데이터베이스 설계 및 구축 단계에서 적절한 무결성 방안을 마련해야 한다.
중요한 부분:
데이터 품질에 직접적인 영향:
- 데이터 무결성은 데이터의 품질을 결정하는 중요한 요소이다. 무결성이 유지되지 않으면 데이터의 신뢰성과 정확성이 떨어지게 된다.
사후 무결성 강화 비용:
- 데이터가 축적된 후에 무결성을 보장하기 위한 데이터 클린징 작업은 많은 시간과 비용이 발생할 수 있다. 따라서 사전 방지가 중요하다.
초기 설계 단계에서 무결성 확보:
- 데이터베이스 설계 및 구축 단계에서 미리 적절한 무결성 방안을 마련하면, 이후 발생할 수 있는 데이터 품질 문제와 비용을 예방할 수 있다.
요약된 핵심:
- 데이터 무결성은 데이터 품질을 좌우하는 중요한 요소이며, 사후 비용을 줄이기 위해 초기 단계에서 무결성을 강화해야 한다. 편리함과 무결성은 반대의 개념이다. 무결성이 없다면 아무 데이터나 넣어도 되기때문에 작업시에는 편할 수 있지만 추후의 문제를 위해 초기에 무결성을 강화해야하는 이유이다.
데이터 무결성의 종류
아래의 네 가지 무결성 유형은 데이터베이스의 정확성과 일관성을 보장하는 핵심 요소이다.
개체 무결성(Entity Integrity):
테이블의 각 **로우(행)**를 유일하게 식별할 수 있는 속성이나 속성 그룹을 가져야 한다.
즉, **기본 키(Primary Key)**와 같은 속성을 통해 각 인스턴스가 고유하게 식별되어야 한.
영역 무결성(Domain Integrity):
특정 컬럼의 값이 해당 컬럼이 허용하는 값의 범위(도메인) 내에 있어야 함.
예를 들어, 나이를 저장하는 컬럼은 음수가 아닌 값만 가질 수 있도록 제한하는 것.
참조 무결성(Reference Integrity):
한 컬럼의 값이 다른 테이블의 값을 참조할 때, 해당 값이 참조할 수 없는 값을 가지지 않도록 보장해야 함.
주로 **외래 키(Foreign Key)**를 통해 다른 테이블의 값을 참조할 때 이를 유지한다.
사용자 정의 무결성(User-defined Integrity):
비즈니스 규칙에 따라 정의된 다양한 무결성 조건을 일관성 있게 유지해야 한다.
특정 비즈니스 규칙을 적용하여 데이터의 일관성을 확보한다.
중요한 부분:
개체 무결성: 기본 키를 통해 각 행이 고유하게 식별되는 것이 중요하다.
영역 무결성: 컬럼 값이 허용된 범위 내에서만 입력되도록 관리해야 한다.
참조 무결성: 외래 키를 사용하여 참조 무결성을 유지하며, 참조할 수 없는 잘못된 값이 입력되지 않도록 해야 한다.
사용자 정의 무결성: 비즈니스 규칙에 따라 추가적인 무결성 조건을 유지하여 데이터의 일관성을 보장하는 것이 중요하다.
무결성 강화 방법
애플리케이션에서의 무결성 강화:
- 데이터를 조작하는 프로그램 내에서 데이터 생성, 수정, 삭제 시 무결성 조건을 검증하는 코드를 추가하여 무결성을 보장할 수 있다.
데이터베이스 트리거 사용:
트리거 이벤트가 발생하면 미리 정의된 SQL을 실행하여 무결성 조건을 자동으로 적용하게 된다.
트리거는 데이터 변경 시 자동으로 실행되므로, 무결성 규칙을 강제하는 데 유용합니다.
제약 조건 선언:
데이터베이스 내에서 제약 조건(예: 기본 키, 외래 키, 고유성 등)을 선언하여 무결성을 유지합니다.
제약 조건은 데이터베이스가 자동으로 무결성을 검사하고 위반될 경우 오류를 발생시킵니다.
중요한 부분:
애플리케이션 수준:
애플리케이션 내에서 무결성 조건을 직접 검증하면 유연성이 높지만, 데이터베이스 외부에서 검증되기 때문에 데이터 일관성이 낮을 수 있습니다.
특히 애플리케이션 간 통합 시 복잡해질 수 있습니다.
데이터베이스 트리거:
- 트리거는 데이터 변경 시 자동으로 실행되므로, 자동 무결성 검사가 가능하지만, 과도한 트리거 사용은 성능 문제를 일으킬 수 있습니다.
제약 조건:
- 가장 강력한 무결성 보장 방법입니다. 데이터베이스 수준에서 데이터를 보호하므로 일관성과 안정성을 확보할 수 있습니다.
요약된 핵심:
무결성 강화는 애플리케이션 코드, 데이터베이스 트리거, 제약 조건 등을 통해 달성되며, 각각 성능과 유연성에 차이가 있습니다.
제약 조건이 가장 강력한 방법이며, 트리거와 애플리케이션 코드는 상황에 맞게 보완적으로 사용됩니다.
무결성 강화 방법의 장단점
애플리케이션:
장점: 사용자 정의 같은 복잡한 사용자 정의 무결성 조건을 구현할 수 있음.
단점: 소스 코드에 분산되어 관리가 어려움. 개별적으로 실행되므로 적절한 검토가 어렵고 통합 관리에 어려움이 있다.
데이터베이스 트리거:
장점: 통합 관리 가능, 복잡한 요건 구현 가능
단점: 운영 중 변경이 어려움. 사용 시 주의가 필요하며 성능 문제 발생 가능.
제약 조건:
장점: 간단한 선언으로 구현 가능하며, 변경과 유효/무효 상태 전환이 용이하다. 원천적으로 잘못된 데이터 발생을 막을 수 있다.
단점: 복잡한 제약 조건 구현이 불가하다. 예외적인 상황 처리에 한계가 있다.
중요한 부분:
애플리케이션은 복잡한 무결성 조건을 처리할 수 있지만, 관리의 복잡성이 크다는 단점이 있다.
트리거는 통합 관리가 가능하지만, 운영 중 변경이 어렵고 주의가 필요함.
제약 조건은 가장 간단하고 효율적으로 무결성을 보장하지만, 복잡한 제약 조건을 처리할 수 없으며 예외 상황 관리가 어려움.
2️⃣ 개체 무결성 (Entity Integrity)
💡Keyword: 유일키(Unique Key), 기본키(Primary Key), Unique Constraint
개체 무결성의 정의 ⬇️
💡요약: 개체 무결성은 테이블의 각 행이 고유하게 식별되도록 보장한다. 이를 위해 기본키와 유일성 제약조건이 사용된다. 이러한 무결성은 DBMS의 제약조건으로 유지되며, 프로그램이나 트리거에 의존하지 않는다.
유일성(Unique) 제약조건: 기본키 외의 다른 열에서도 중복된 값이 없도록 보장하는 제약조건이다. 유일키는 NULL 값을 허용할 수 있지만, 값이 있을 경우 반드시 유일해야 한다.
기본키(Primary Key) 제약조건: 테이블에 저장된 레코드가 고유하게 식별되도록 보장하는 제약조건입니다. 기본키는 NULL 값이 될 수 없고, 중복된 값을 허용하지 않는다.
기본키(Primary Key) 제약조건 기본키와 유일키의 비교(오라클) ⬇️
💡요약: 기본키와 유일키는 모두 테이블에서 고유한 값을 보장하지만, 기본키는 NULL 값을 허용하지 않고, 테이블당 하나만 설정할 수 있다는 차이가 있다. 반면 유일키는 여러 개 설정할 수 있으며 NULL 값을 허용할 수 있다.
유일키(Unique) 제약조건 정의 ⬇️
💡요약: 유일키는 테이블의 특정 열의 고유성을 보장하며, 중복된 값을 허용하지 않지만 NULL 값은 가능할 수 있다. 복합 유일키는 여러 컬럼을 결합하여 고유한 값을 보장하는 제약 조건이다.
중복된 값 불허(Duplicate values are not allowed): 유일키가 설정된 열은 중복된 값을 가질 수 없다. 즉, 테이블에서 각 값은 고유해야 한다.
여러 개의 유일키 허용(Multiple unique keys are allowed): 하나의 테이블에서 여러 개의 유일키를 가질 수 있다. 각 유일키는 특정 열의 고유성을 보장한다.
NULL 값 허용(NULL values are allowed): 유일키는 NULL 값을 가질 수 있다. 단, NULL 값은 서로 비교되지 않기 때문에, 여러 개의 NULL 값이 있을 수 있다.
복합 유일키(Composite Unique Key):
- 여러 컬럼 조합(Combination of multiple columns): 복합 유일키는 한 개 이상의 컬럼을 조합하여 유일키를 구성할 수 있다. 즉, 여러 컬럼의 값을 합쳐 고유성을 보장한다. 개별 컬럼의 값은 중복될 수 있지만, 조합된 값은 유일해야 한다.
유일키(Unique) 제약조건 지정방법⬇️
💡요약
컬럼 단위로 유일성을 설정할 때는 각 컬럼에
UNIQUE제약을 바로 지정한다.테이블 단위에서는
CONSTRAINT키워드를 사용해 유일성 제약을 명시한다.복합 유일키는 여러 컬럼을 조합해 고유성을 보장한다.
CREATE TABLE unique_test (
col1 VARCHAR2(10) UNIQUE,
col2 VARCHAR2(10),
col3 VARCHAR2(10),
col4 VARCHAR2(10),
CONSTRAINT "col2_uk" UNIQUE(col2),
CONSTRAINT "col3col4_uk" UNIQUE(col3, col4)
);
col1에 유일성 제약 부여:
col1 VARCHAR2(10) UNIQUE는col1컬럼에 대해 중복된 값을 허용하지 않도록 유일성 제약을 설정하였다. 이 방식은 컬럼 단위로 제약을 설정하는 방법이다.col2에 유일성 제약 부여:
CONSTRAINT "col2_uk" UNIQUE(col2)는 테이블 정의 아래에서 테이블 단위로col2컬럼에 유일성 제약을 추가하는 방식이다.col3와 col4의 복합 유일키 설정:
CONSTRAINT "col3col4_uk" UNIQUE(col3, col4)는col3과col4컬럼을 결합하여 복합 유일키(Composite Unique Key)를 설정하는 구문이다. 이는 두 컬럼의 값이 조합되어 중복되지 않도록 보장한다.
유일키(Unique) 제약조건 키생성과 확인 ~ 테이블 unique_test 생성 #1 ⬇️
유일키와 복합 유일키를 설정하는 테이블 생성 과정을 실습한다.
테이블
unique_test를 생성하는 SQL 코드이다.col1에 대해 **유일성 제약(UNIQUE)**이 설정되어 있으며,col2,col3,col4에 대해 **테이블 수준에서 제약(CONSTRAINT)**을 설정하고 있다.col2는 단일 유일키로 지정되었고,col3과col4는 복합 유일키로 설정되었다.
유일키(Unique) 제약조건 키생성과 확인 ~ 유일키 제약 확인 #2 ⬇️
생성된 유일성 제약 조건을 Oracle SQL Developer에서 조회하여 확인하는 과정이다.
테이블
unique_test에 대해 생성된 유일키 제약을 확인하는 화면이다.여기서
SYS C008491,col2_UK,col3col4_UK와 같은 유일성 제약 조건이 Oracle SQL Developer에서 확인되고 있다.각각의 제약 조건들이 테이블에 제대로 적용된 것을 시각적으로 확인할 수 있다.
유일키(Unique) 제약조건 유일키 동작 ~ 데이터 삽입 #1 ⬇️
💡요약: 유일키 제약이 설정된 테이블에서는 중복된 값을 삽입하거나 수정하려고 할 때 유일성 제약 오류가 발생한다. 이는 테이블의 고유성을 보장하기 위해 데이터베이스에서 제공하는 기본적인 보호 메커니즘이다.
unique_test 테이블에 데이터를 삽입하는 SQL 문을 보여주고 있다. 두 개의 삽입문이 실행되었고, SELECT 문을 통해 정상적으로 데이터가 조회되었음을 확인할 수 있다.
A1,B1,C1,D1과A2,B2,C2,D2값이 정상적으로 삽입되었다.
유일키(Unique) 제약조건 유일키 동작 ~ 데이터 삽입 #2 ⬇️
동일한 값을 다시 삽입하려는 시도를 하고 있으며, ORA-00001 오류가 발생했다.
- 이 오류는 유일성 제약을 위반했기 때문에 발생한다.
A2,B2,C2,D2값이 이미 존재하고, 중복된 데이터를 삽입하려 했기 때문에 DBMS가 이를 허용하지 않고 오류를 반환한 것이다.
유일키(Unique) 제약조건 유일키 동작 ~ 데이터 수정 #1 ⬇️
여기서는 UPDATE 문을 통해 col2의 값을 수정하려 했다. 그러나 수정하려는 값이 이미 존재하는 경우, 유일성 제약 조건에 위배되므로 ORA-00001 오류가 발생한다.
col2의B2값을B1으로 수정하려 했으나,B1값은 이미 존재하므로 유일성 제약에 의해 수정이 거부된 것이다.
기본키(Primary Key) 제약조건 정의 ⬇️
💡요약: 기본키는 테이블에서 각 레코드를 고유하게 식별하기 위한 필수 요소로, 유일성과 NULL 불허 제약을 가진다. 테이블당 하나의 기본키만 설정할 수 있으며, 복합키도 가능하며 자동으로 인덱스가 생성된다.
테이블당 하나만 지정 가능(Only one can be designated per table): 기본키는 각 테이블마다 하나만 설정할 수 있다. 이 기본키를 통해 테이블의 각 레코드를 고유하게 식별한다.
유일(Unique)해야 하며, NULL 값 불가(It must be unique, and NULL values are not allowed): 기본키로 지정된 컬럼은 반드시 유일한 값을 가져야 하며, NULL 값을 가질 수 없다. 즉, 기본키는 중복된 값을 허용하지 않고, 비어 있는 값을 가질 수 없다는 뜻.
자동으로 Unique와 NOT NULL 속성 부여(Automatically assigned Unique and NOT NULL attributes): 특정 컬럼을 기본키로 지정하면, 그 컬럼은 자동으로 유일성(Unique)과 NULL 불허(NOT NULL) 속성을 갖게 된다.
복합키 가능(Composite key possible): 기본키는 하나의 컬럼으로만 만들 수 있는 것이 아니라, 여러 컬럼을 결합하여 하나의 기본키로 만들 수 있다. 이를 복합 기본키라고 한다.
인덱스 생성(Index creation): 기본키를 만들면 기본키 제약 조건과 함께 유일성 인덱스가 자동으로 생성된다. 유일키를 만들면 동일하게 유일성 인덱스가 생성되며, 두 조건 모두 유일성 인덱스를 형성한다.
기본키(Primary Key) 제약조건 지정 방법 ⬇️
💡요약: 첫 번째 방법은 컬럼을 정의할 때 바로 기본키를 설정하는 방법이며, 간결하다. 두 번째 방법은 테이블 정의 후 제약조건을 통해 기본키를 설정하는 방식으로, 제약조건의 이름을 명시할 수 있다.
1. 첫 번째 방법: 컬럼 정의 시 기본키 지정
CREATE TABLE pk_test (
col1 VARCHAR2(10) Primary Key,
col2 VARCHAR2(10)
);
이 방법은 테이블을 정의할 때 컬럼을 생성하면서 **직접 기본키를 지정(Directly specify the primary key)**하는 방법이다.
col1컬럼을 정의할 때, 바로Primary Key제약조건을 명시하여 기본키로 설정한다.이 방식은 간단하며, 컬럼 선언과 동시에 기본키 제약을 지정할 수 있다.
2. 두 번째 방법: 테이블 수준에서 제약조건으로 기본키 지정
CREATE TABLE pk_test2 (
col1 VARCHAR2(10),
col2 VARCHAR2(10),
CONSTRAINT "col1_PK" PRIMARY KEY(col1)
);
이 방법은 테이블을 정의한 후 **제약조건(CONSTRAINT)**을 사용하여 기본키를 지정하는 방식이다.
CONSTRAINT "col1_PK"를 사용하여col1컬럼을 기본키로 지정하며, 제약조건의 이름을"col1_PK"로 명시할 수 있다.이 방식은 **제약조건에 이름을 부여(Assign a name to the constraint)**할 수 있다는 장점이 있으며, 여러 컬럼을 포함한 **복합키(Composite key)**를 설정할 때도 자주 사용된다.
기본키(Primary Key) 제약조건 생성⬇️
💡요약: pk_test라는 테이블을 col1을 기본키로 설정하여 생성하는 과정을 보여주고 있으며, 테이블 생성이 성공적으로 완료되었음을 알 수 있다.
pk_test라는 테이블을 생성하는 SQL 코드가 보여지고 있다. 이 테이블은col1을 **Primary Key(기본키)**로 설정하고 있다. 기본키는 테이블 내에서 각 행을 고유하게 식별할 수 있도록 해주며, 중복된 값이나 NULL 값을 허용하지 않는다.SQL 실행 결과 하단에는 "Table PK_TEST이(가) 생성되었습니다."라는 메시지가 표시되어, 테이블이 성공적으로 생성되었음을 확인할 수 있다.
기본키(Primary Key) 제약조건 확인 ⬇️
💡요약: PK_TEST 테이블에 기본키가 적용된 것을 확인하는 과정으로, 기본키가 성공적으로 설정되었음을 보여주고 있다. 자동으로 생성된 제약조건의 이름과 해당 제약조건의 유형(Primary Key)을 확인할 수 있다.
CONSTRAINT_NAME칼럼에서 SYS C008494라는 제약조건이 생성되었고, CONSTRAINT_TYPE으로 Primary Key가 지정되어 있는 것을 볼 수 있다.이 제약 조건은 기본키로 설정된
col1에 대해 자동으로 생성된 것이다.
기본키(Primary Key) 제약조건 테이블 생성 후 제약조건 지정 방법 ⬇️
💡요약: ALTER TABLE 명령어를 사용해 테이블이 생성된 후에도 기본키나 유일성 제약조건을 추가할 수 있다. 제약조건에는 이름을 부여할 수 있으며, 이 예시는 각각 기본키와 유일성 제약을 추가하는 방법을 보여준다.
1. 기본키 제약조건 추가
ALTER TABLE pk_test
ADD CONSTRAINT pl_col1 PRIMARY KEY(col1);
pk_test테이블에 대해col1컬럼을 **기본키(Primary Key)**로 설정하는 방법이다.ADD CONSTRAINT를 사용하여 새로운 기본키 제약조건을 추가하며, 제약조건의 이름은pl_col1으로 지정된다.
2. 유일성(Unique) 제약조건 추가
ALTER TABLE pk_test
ADD CONSTRAINT pl_col1 UNIQUE(col1);
- 이 명령문은
pk_test테이블의col1컬럼에 대해 유일성(Unique) 제약조건을 추가한다. 마찬가지로 제약조건 이름은pl_col1로 지정되었다.
3️⃣ 영역 무결성 (Domain Integrity)
💡Keyword: 데이터 타입(크기) 제약조건, NOT NULL 제약조건, 유효 값(CHECK) 제약조건, DEFAULT 속성
영역 무결성의 정의(Definition of Domain Integrity) ⬇️
💡요약: 영역 무결성은 데이터베이스의 컬럼에 입력될 수 있는 값이 미리 정의된 영역 내에서만 유효하도록 보장하는 제약이다. 이를 통해 데이터의 정확성을 유지하고, 잘못된 값이 입력되는 것을 방지할 수 있다. 애플리케이션 기능이나 데이터베이스 제약조건을 통해 무결성을 강화할 수 있다.
영역(도메인) 내 값 제한(Limiting values within the domain): 해당 컬럼에 입력되는 값은 미리 정의된 영역(도메인) 내에 속해야 한다. 예를 들어, 날짜 형식이 올바르지 않으면 유효하지 않은 데이터로 간주된다.
유효성 검증(Validation of values): 애플리케이션 기능을 통해 값의 유효성을 검사하여, 입력된 값이 규정된 영역에 맞는지를 확인한다.
데이터베이스 제약조건(Database constraints): 제약조건을 선언하여 특정 컬럼에 값의 무결성을 강화할 수 있다. 예를 들어, 값이 특정 범위 내에 있을 때만 허용하거나, 값이 필수 입력 사항일 때 NULL 값을 허용하지 않도록 할 수 있다.
예시:
날짜 형식 제한(Date format restriction): 예를 들어, '주문일자' 컬럼의 값이 '20050230'이라면, 이는 잘못된 날짜 형식으로 유효하지 않으며 허용되지 않는다.
상태값 제한(Status value restriction): 근무상태 값이 '1:정상', '2:휴직', '3:퇴직'이라는 비즈니스 규칙을 설정하고, 이 외의 값인 '4'나 NULL 값은 존재할 수 없도록 하는 예시이다.
필수 입력값 제한(Mandatory input restriction): 상품 테이블에서 상품명이 필수로 입력되어야 한다면, 이 컬럼에는 NULL 값이 존재할 수 없다.
영역 무결성 구현 방법⬇️
아래의 3가지 방법은 데이터베이스의 무결성을 보장하기 위한 기본적인 제약조건들이다.
데이터 타입(크기) 제약조건: 입력되는 값의 데이터 타입이나 크기를 제한하여 유효하지 않은 데이터 입력을 방지한다.
NOT NULL 제약조건: 필드에 빈 값(NULL)이 들어가는 것을 방지하여 반드시 값이 존재하도록 강제한다.
유효 값(CHECK) 제약조건: 특정 조건을 만족하는 값만 입력될 수 있도록 제한한다.
영역 무결성 제약조건 1 - 데이터 타입(크기) 정의⬇️
💡요약: 데이터 타입은 컬럼에 저장되는 값의 형식과 범위를 제한하기 때문에 도메인 무결성을 위한 기초단위이다. 특히, 시간 데이터의 경우, 상황에 따라 문자형으로 처리하고 유효성을 프로그램 내에서 확인하는 것이 더 효과적인 방법이다.
데이터 타입의 역할: 데이터 타입은 컬럼에 저장되는 값의 형식과 범위를 제한하여 도메인 무결성을 보장한다.
- 예를 들어,
DATE타입으로 정의된 컬럼에는 잘못된 날짜(예: 2월 30일)를 입력할 수 없다.
- 예를 들어,
문자 데이터 타입의 활용:
시간 데이터 타입의 경우, 비교 연산이 어려워 변환이 필요하다.
이럴 때는 컬럼을 문자 데이터 타입으로 정의한 후, 프로그램에서 유효 날짜를 확인하는 방식이 더 효율적일 수 있이다.
조회 조건이나 비교 연산이 많이 사용될 때 이런 방식이 유용하다.
영역 무결성 제약조건 1 - 데이터 타입(크기)의 결정⬇️
💡요약: 데이터 타입을 선택할 때는 최소한의 크기로 데이터를 저장할 수 있도록 하여 성능을 최적화하는 것이 중요하다. 저장 공간을 적게 차지하는 데이터는 페이지 로딩 수를 줄여 성능을 개선할 수 있으며, 대부분의 데이터는 문자와 숫자 형식이다.
최소한의 크기 선택(Minimal size): 데이터 타입을 선택할 때 기본 원칙은 최소한의 크기로 사용자의 데이터 요구를 만족시킬 수 있는 타입을 선정하는 것이.
성능 최적화(Performance Optimization): 저장되는 데이터의 크기가 작을수록 데이터베이스 블록(페이지)당 더 많은 데이터를 저장할 수 있다. 이로 인해 데이터 블록 로딩 수가 줄어들어 성능이 향상된다.
일반적인 데이터 형식(Common data type): 데이터베이스에 정의되는 데이터 형식 중 약 90% 이상은 문자와 숫자 데이터 형식이 차지한다.
영역 무결성 제약조건 1 - 데이터 타입(크기)의 결정기준(Data Type Selection Criteria)⬇️
💡요약: 데이터 타입을 선택할 때는 저장 공간을 최소화하고, 연산 가능 여부와 값의 형식을 고려해야 한다. 이는 성능 최적화와 데이터의 유효성 보장에 중요한 역할을 한다.
적은 바이트 수로 값을 표현 여부 (Whether the value can be represented with fewer bytes)
저장 공간이 적을수록 한 페이지에 많은 로우를 저장할 수 있음
예: '100000'은 문자열로는 6바이트, 정수형으로는 4바이트 차지
연산 가능한 값인지 여부 (Whether the value is capable of being calculated)
숫자나 날짜 타입은 다양한 연산이 가능
문자열은 연산이 제한되며, 숫자 연산을 위해서는 타입 변환 과정을 거쳐야 함
값의 형식 검사 (Validation of the value's format)
- 정수형, 날짜형 등을 지정할 경우 숫자나 날짜 타입의 데이터만으로 제약할 수 있음
영역 무결성 제약조건 1 - 문자열 타입(Character Data Types) ⬇️
💡요약: 오라클의 문자열 타입에는 고정 길이와 가변 길이를 지원하는 여러 데이터 타입이 있으며, 다국적 언어 지원을 위한 타입도 존재한다.
오라클 문자열 타입 (Oracle String Types)
CHAR: 고정 길이 문자 데이터를 저장, 최대 길이 2,000 bytes
- 지정된 길이보다 짧은 값을 할당하고 남은 공간은 공백으로 채움
VARCHAR, VARCHAR2: 가변 길이 문자 데이터를 저장, 최대 길이 4,000 bytes
- 실제 입력된 데이터만 저장
NCHAR, NVARCHAR2: 다국적 언어를 사용하여 고정길이 또는 가변 길이 문자 데이터를 저장
- 바이트 단위가 아닌 문자 단위로 컬럼 길이를 지정
CLOB, NCLOB: 최대 4GB 길이의 문자 데이터를 저장
- NCLOB 자료형은 NLS(다국적 언어) 데이터를 저장
유의사항(NOTE)
💡요약: 문자열 데이터 타입은 CHAR, VARCHAR, NVARCHAR 등으로 나뉘며, 각각 고정 길이, 가변 길이, 유니코드 지원 등의 특징을 가지고 있다. 데이터의 특성과 요구사항에 따라 적절한 데이터 타입을 선택하여 성능과 저장 효율성을 높일 수 있다.
CHAR, VARCHAR2 타입은 최대 문자의 수, 또는 바이트 수로 크기를 지정
CHAR 타입의 경우 입력 시 값이 컬럼의 크기보다 적으면 공백이 채워져 저장됨
따라서, 뒤에 공백이 포함된 문자열과 포함되지 않은 문자열이 같다고 판단함
예:
'S','S '가 모두 같다고 판단
VARCHAR2 타입은 공백이 있으면 이를 입력할 때 반영
- 예:
'S','S ','S '가 모두 다르다고 판단, 공백을 제거하고 비교해야 함
- 예:
NCHAR, NVARCHAR: 유니코드 가변 길이 문자 데이터 타입으로, 다양한 언어의 문자를 저장할 수 있다. 한글이나 한자 등은 2바이트로 저장되며, 영어와 숫자는 1바이트로 저장된다. 따라서,
NVARCHAR는 다국어 지원이 필요한 경우에 적합하다.CLOB데이터 타입은 매우 큰 텍스트 데이터를 다루기 때문에, LIKE 연산자를 사용한 패턴 매칭이 적용되지 않는다. 이는 성능상의 이유와 데이터의 크기 때문이다. CLOB의 경우 일반 문자열처럼 다루기 어려운 특성이 있다.대신 INSTR 함수를 사용하여 CLOB 내에서 특정 문자열의 위치를 검색할 수 있다.
CLOB대용량 문자 데이터를 저장할 수 있는 데이터 타입이다. 보통 매우 긴 텍스트, 문서, 로그 파일 등을 저장할 때 사용된다. CLOB는 일반적으로 4GB까지의 텍스트 데이터를 저장할 수 있다.
영역 무결성 제약조건 1 - 숫자 타입(Numeric Data Types)⬇️
💡요약: NUMBER 타입은 오라클에서 숫자를 저장할 때 사용하는 기본 데이터 타입으로, 정밀도와 스케일을 통해 숫자의 전체 자리수와 소수점 이하 자릿수를 정의할 수 있다. 각 값에 따라 데이터의 정확도와 범위를 설정할 수 있다.
NUMBER 타입:
오라클은 기본적으로 숫자 타입으로 NUMBER를 사용한다.
정밀도(Precision)와 스케일(Scale)을 사용하여 선언한다.
정밀도는 1 ~ 38까지 지정할 수 있으며, 스케일은 -84 ~ 127까지 지정 가능하다.
예시:
컬럼명 NUMBER: 정밀도와 스케일을 지정하지 않은 기본 숫자 타입.
컬럼명 NUMBER(정밀도, 스케일): 정밀도와 스케일을 명시적으로 지정한 숫자 타입.
컬럼명 NUMBER(정밀도): 정밀도만 지정하고 스케일은 생략한 경우.
컬럼명 NUMBER(*, 스케일): 정밀도를 지정하지 않고 스케일만 명시한 경우.
영역 무결성 제약조건 1 - 숫자 타입 예시(Example of Numeric Data Types)⬇️
💡요약: NUMBER 타입은 정밀도와 스케일에 따라 값이 저장되며, 지정된 범위를 초과할 경우 값이 반올림되거나 오류가 발생한다. 매우 작은 소수점 값들도 지정된 정밀도와 스케일에 맞게 정확하게 저장될 수 있다.
주어진 Actual Data(실제 데이터)가 Specified As(지정된 방식)과 Stored As(저장된 방식)에 따라 어떻게 다르게 저장되는지를 설명하고 있다.
NUMBER 타입에서 정밀도(Precision)와 스케일(Scale)을 지정하지 않은 경우, 실제 값이 그대로 저장된다.
정밀도와 스케일을 지정하면, 정밀도를 초과하거나 소수점 자리를 조정하여 값이 저장된다.
예를 들어,
NUMBER(3)로 지정하면 소수점 이하는 반올림되어124로 저장되었다.정밀도와 스케일을 초과할 경우, 값이 저장되지 않고 오류가 발생다 (exceeds precision).
NUMBER 타입에서 소수점 이하 값의 저장 방법을 보여주고 있다.
예를 들어,
.01234를NUMBER(4, 5)로 지정하면, 입력된 값이 그대로 저장된다.매우 작은 값들도 정밀도와 스케일에 따라 정확하게 저장된다.
지수 표현(1.2e-4 등) 역시 지정된 정밀도와 스케일에 맞춰 소수점 이하 자리수를 반영하여 저장된다.
영역 무결성 제약조건 1 - 날짜 시간 타입(Datetime & Data Types)⬇️
💡요약: DATE 타입은 날짜와 시간 데이터를 저장하는 데 사용되며, 다양한 날짜 시간 필드를 지원한다. 시간만 입력할 경우 기본적으로 해당 월의 1일로 설정되며, 날짜 연산에서는 덧셈과 뺄셈만 허용된다.
DATE 타입:
날짜와 시간에 대한 정보를 저장
시간만 입력하면 현재 월 1일이 입력됨
SYSDATE와 TO_DATE 함수를 이용하여 입력
날짜형은 곱셈이나 나눗셈 연산은 하지 못하고 덧셈과 뺄셈만 가능
💡요약: 아래 예시들은 오라클에서 날짜 데이터를 처리하는 방법을 보여주고 있다. TO_DATE 함수는 문자열을 날짜로 변환하고, TO_CHAR 함수는 날짜를 다시 문자열로 변환하는 데 사용되는데 이 함수들을 통해 날짜 데이터의 변환과 형식 지정을 자유롭게 할 수 있다.
DATE 타입 예시 (DATE Type Example):
SELECT TO_DATE('2009', 'YYYY') FROM DUAL;- 2009년이라는 연도를 문자열에서 날짜로 변환하는 예시이다. 결과는 기본적으로 2009년 8월 1일로 설정된다. TO_DATE 함수는 문자열을 날짜 형식으로 변환하는 함수이다.
SELECT TO_CHAR(TO_DATE('01-01-2009', 'MM-DD-YYYY'), 'MM-DD-YYYY') FROM DUAL;- 문자열로 입력된 날짜(01-01-2009)를 TO_DATE 함수를 사용하여 날짜로 변환한 후, TO_CHAR 함수를 사용하여 다시 지정된 형식(MM-DD-YYYY)으로 문자열로 변환하는 예시이다. 결과는 09/01/01로 출력되었다.
영역 무결성 제약조건 1 - 기타 (Miscellaneous)⬇️
💡요약: ROWID는 오라클 데이터베이스에서 각 행의 고유 식별자로 사용되며, 테이블 내 행의 논리적 주소를 나타낸다. 쿼리를 통해 ROWID와 함께 행에 대한 다른 정보를 조회할 수 있다.
ROWID:
데이터베이스 내에서 각 행에 대해 고유하게 부여되는 식별자이다.
(Unique identifier for a row in a table)테이블 내의 한 행에 대한 논리적인 주소값을 가지는 자료형
(A data type that holds the logical address of a row within a table)논리적 주소를 나타내며, 물리적 주소와는 무관하다. 이는 오라클이 내부적으로 관리하며, 사용자가 직접 변경할 수 없다.
(Does not store the physical location of a specific row, but is assigned by Oracle and cannot be changed by input or modification)
ROWID 예시 (ROWID Example):
SELECT ROWID, employee_id, last_name FROM employees;위 쿼리는 각 행에 대한 ROWID, employee_id, 그리고 last_name을 조회하는 예시이다.
ROWID는 각 행에 대한 고유 식별자로, 각 행의 논리적 주소를 나타낸다.
영역 무결성 - 테이블 설계시 고려사항⬇️
💡요약: 테이블 설계 시, 데이터의 길이와 사용 빈도에 따라 수직 분할을 고려해야 하며, 특히 성능 최적화를 위해 트랜잭션과 조인 빈도에 유의해야 한다.
수직 분할 (Vertical Partitioning):
컬럼 데이터 길이가 블록 크기를 초과(길이 합 1블록 사이즈보다 큰 경우)하면 속도 저하를 방지하기 위해 수직 분할을 고려해야 한다.
(Consider vertical partitioning if the total length of the column data exceeds the block size):- 속도 저하 현상을 유발할 수 있으므로 이를 방지하기 위한 조치이다.
컬럼 길이가 길고 컬럼 사용 빈도가 차이가 크거나 특정 사용자 그룹이 특정 컬럼만 사용하는 경우, 수직 분할을 통해 성능을 최적화할 수 있다.
(If there is a significant difference in column lengths or usage frequency between columns, or if different user groups mainly use specific columns separately):- 이 경우 성능을 최적화하기 위해 수직 분할을 사용할 수 있다.
수직 분할을 고려할 때에는 분할되는 테이블이 하나의 트랜잭션에 의해 동시에 처리되는 경우나 조인이 빈번히 발생되는 경우는 피해야 한다.
(When considering vertical partitioning, ensure that the partitioned tables are not frequently involved in the same transaction or require frequent joins):- 이는 성능 저하를 방지하기 위한 중요한 고려사항이다.
영역 무결성 제약조건 2 - 널(NULL)값⬇️
💡요약: NULL 값은 데이터베이스에서 정보의 부재를 나타내는 특수 값으로, 공백이나 0과는 다르다. 숫자 컬럼에서 NULL을 방지하기 위해 NOT NULL 제약조건을 설정하고 기본값을 지정하는 것이 일반적이다.
데이터베이스에서 NULL은 **아직 알려지지 않은(모르는) 값(Unknown value)**이거나, 해당 없음(Inapplicable) 등의 이유로 정보 부재를 명시적으로 표시하기 위해 사용하는 특수한 데이터 값이다.
- **공백(Blank)**이나 **영(Zero)**과는 분명히 다른 개념이다.
숫자 타입의 컬럼은 계산에 자주 사용되는데, NULL 값이 존재하면 연산이 불가능하며 에러가 발생할 수 있다.
이를 방지하기 위해, 숫자 타입의 컬럼은 NOT NULL 제약조건을 부여하고 기본값으로 0을 정의하기도 한다.
영역 무결성 제약조건 2 - NOT NULL⬇️
💡요약: NOT NULL 제약조건은 컬럼이 반드시 값을 가져야 할 때 사용된다. 테이블 설계 시, 검색 빈도가 높은 NOT NULL 컬럼을 테이블의 앞쪽에 배치하는 것이 성능 향상에 도움을 줄 수 있다.
널 값을 가질 수 없는 컬럼은 NOT NULL이라는 제약 조건을 별도로 명시해서 사용하는 제약이다.
컬럼의 순서는 일반적으로 테이블 내의 중요도에 따라 정해진다.
검색에 자주 사용되는 컬럼을 앞으로 배치하는 것이 성능 최적화에 유리하다.
NOT NULL 컬럼은 주로 테이블의 앞부분에 배치하고, NULL을 허용하는 컬럼은 뒤쪽에 배치하는 것이 일반적이다.
영역 무결성 제약조건 2 - NOT NULL 제약조건 동작⬇️
💡요약: 위 이미지는 NOT NULL 제약 조건이 적용된 테이블에 NULL 값을 삽입하려고 할 때 발생하는 오류를 보여주고 있다. col1은 NOT NULL로 정의되어 있어 NULL 값을 허용하지 않기 때문에, 값이 입력되지 않아 오류가 발생한 것이다.
CREATE TABLE null_test 구문에서
col1컬럼은 NOT NULL로 설정되었다.첫 번째
INSERT INTO문은col1과col2에 값을 정상적으로 삽입하고 있다.두 번째
INSERT INTO문에서,col1에 값을 제공하지 않고col2와col3에만 값을 넣으려 했기 때문에, ORA-01400: NULL을 삽입할 수 없습니다라는 오류가 발생했다. 이는col1에 NULL 값을 넣으려고 했기 때문이다.
영역 무결성 제약조건 3 - CHECK 제약조건의 도메인 정의 ⬇️
💡요약: 도메인은 데이터베이스에서 각 컬럼이 가질 수 있는 값의 범위를 정의하며, 이를 통해 데이터의 유효성을 보장하는 역할을 한다. CHECK 제약조건은 도메인에서 설정한 값의 범위를 따르는 것이다. 각 도메인 그룹에 따라 적절한 데이터 타입이 지정되며, 이를 통해 데이터의 일관성과 무결성을 유지할 수 있게된다.
데이터베이스 설계 과정에서 도메인을 먼저 정의한다.
- 도메인은 특정 값이 가질 수 있는 범위를 나타낸다.
현실에서 사용되는 도메인 이름과 값의 범위를 정의하고, 컬럼 타입 정의 시 이를 참조한다.
- 즉, 도메인은 각 컬럼이 허용할 수 있는 값의 범위를 설정하며, 이러한 범위를 기반으로 데이터 입력을 제한한다.
표에서는 여러 도메인 그룹과 그에 해당하는 도메인명, 데이터 타입, 설명이 포함되어 있다. 예를 들어;
전화번호는
VARCHAR2(13)으로 정의되고,금액은
NUMBER(13)으로 정의되며,날짜는 일자, 월, 연도 등으로 나누어 각각
VARCHAR2타입으로 정의된다.
영역 무결성 제약조건 3 - CHECK 제약조건 지정방법⬇️
💡요약: CHECK 제약조건은 컬럼에 삽입되는 데이터의 유효성을 검사하여, 부적합한 데이터의 삽입을 방지하는 중요한 기능이다. TRUE 또는 FALSE 값을 반환하는 다양한 조건을 통해 복잡한 검증 로직을 설정할 수 있다.
컬럼 유효값에 대한 제약조건으로 CHECK를 사용한다.
- 즉, 특정 컬럼에 들어가는 값이 유효한지 여부를 확인하기 위해 사용하는 제약 조건이다.
삽입(수정)되는 데이터를 검사해서 해당되는 영역의 데이터이면 진행시키고, 그렇지 않으면 삽입(수정) 작업을 취소하는 역할을 수행한다.
- 이 조건을 통해 유효하지 않은 데이터의 삽입을 방지할 수 있다.
TRUE 혹은 FALSE 값을 도출하는 어떤 조건도 포함될 수 있음.
- CHECK 제약조건은 단순히 값이 범위에 있는지 여부뿐만 아니라, 여러 가지 복잡한 논리 조건도 설정할 수 있다.
영역 무결성 제약조건 3 - CHECK 제약조건 동작⬇️
CREATE TABLE emp_test (
empid NUMBER(6,0) NOT NULL CHECK (empid > 0),
empname VARCHAR2(30) NOT NULL,
address VARCHAR2(100) NULL CHECK (address LIKE 'S%'),
grade NUMBER(10,2) NULL,
gender CHAR(2 CHAR) NULL,
regdate DATE NULL,
CONSTRAINT check_gender CHECK (gender IN ('남자', '여자'))
);
지정 방법
테이블 정의 시 컬럼 단위 또는 테이블 단위로 CHECK 제약을 설정할 수 있다.
예시 테이블
emp_test는 여러 컬럼에 대해 CHECK 제약을 적용하고 있다:empid는
0보다 큰 값만 허용 (CHECK(empid > 0)).address는
S로 시작하는 값만 허용 (CHECK(address like 'S%')).gender 컬럼에 대한 제약 조건은 테이블 레벨에서 설정되며, 값이
'남자'또는'여자'만 허용된다 (CHECK(gender IN ('남자', '여자'))).
영역 무결성 제약조건 3 - CHECK 제약조건 동작2 ⬇️
CHECK 제약 조건을 위반했을 때 발생하는 오류를 해결해보자
emp_test테이블에 대해 CHECK 제약 조건이 정의되어 있다.gender컬럼에'남자'또는'여자'만 허용하는 제약 조건이 있다.
다음 세 가지
INSERT문을 실행하려고 했습니다:첫 번째와 두 번째
INSERT문은 정상적으로 실행됩니다.세 번째
INSERT문에서 오류가 발생했다:INSERT INTO emp_test (empid, empname, address, grade, gender, regdate) VALUES (103, '이나라', 'PUSAN', NULL, '여자', NULL);
발생한 오류 메시지:
ORA-02290: "체크 제약조건(HR.SYS_C008499)이 위배되었습니다."
이는 삽입된 데이터가 CHECK 제약 조건을 위반했음을 의미한다.
오류 원인:
gender컬럼에'여자'가 들어가 있는 상황이므로 이는 제약 조건에 부합하다.그러나 다른 컬럼의
NULL값이 제약 조건을 위반하고 있을 가능성이 높다. 예를 들어,address가S로 시작하는지 여부를 확인해야 합니다. 현재PUSAN은S로 시작하지 않으므로, 이는 제약 조건을 위반하는 값이다.
해결 방법:
address컬럼에 들어가는 값이S로 시작하도록 수정해야 합니다. 예를 들어:sqlCopy codeINSERT INTO emp_test (empid, empname, address, grade, gender, regdate) VALUES (103, '이나라', 'SEOUL', NULL, '여자', NULL);
이렇게 하면 CHECK 제약 조건을 만족하고 오류가 발생하지 않을 것이다.
영역 무결성의 기본값을 설정하는 메커니즘 -DEFAULT 속성 ⬇️
💡요약: DEFAULT 속성은 특정 컬럼에 값이 제공되지 않을 때, 자동으로 기본값을 설정해주는 기능이다. 이를 통해 입력되지 않은 값들에 대해 데이터베이스가 자동으로 적절한 값을 할당할 수 있으며, 이를 위해 문자, 숫자, 또는 스칼라 함수 등을 사용할 수 있다.
참고로, DEFAULT는 제약조건이 아니다. DEFAULT는 컬럼에 값이 입력되지 않았을 때 자동으로 기본값을 삽입하는 속성일 뿐, 값의 유효성을 검사하거나 제한하는 역할을 하지 않기 때문이다. 제약조건(constraint)은 값의 유효성을 확인하거나 데이터의 일관성을 유지하기 위한 조건을 강제하는 것인데, DEFAULT는 그런 기능을 하지 않기 때문에 제약조건에 속하지 않는다.
예시:
CREATE TABLE emp_test (
empid NUMBER(6) DEFAULT 100
);
이 경우, empid에 값이 제공되지 않으면 기본값인 100이 자동으로 삽입되게 된다.
DEFAULT는 데이터 제약조건에 포함되지는 않지만, 컬럼 단위로 설정되는 속성이다.
- 즉, 컬럼의 값이 지정되지 않았을 때 자동으로 미리 정의된 기본값을 채워주는 역할을 한다.
데이터를 삽입할 때 컬럼의 값을 지정하지 않으면, 미리 정의된 값이 자동으로 들어가는 메커니즘이다.
스칼라 값(문자, 숫자)과 스칼라 함수를 이용해 기본값을 설정할 수 있다..
- 예를 들어,
SYSDATE와 같은 함수를 사용하여 기본값으로 현재 날짜를 설정할 수 있다.
- 예를 들어,
영역 무결성의 기본값을 설정하는 메커니즘 -DEFAULT 속성 구현 ⬇️
💡요약: 아래 예시는 테이블 생성 시 DEFAULT 값을 지정하여, 데이터가 입력되지 않은 경우 자동으로 기본값을 할당하는 방법을 보여주고 있다. 이를 통해 데이터베이스는 NULL 대신 적절한 기본값을 자동으로 채울 수 있게된다.
empid:
NUMBER(6,0)로 정의되며 NOT NULL이므로 값이 반드시 입력되어야 한다. 또한,CHECK(empid > 0)로 empid는 0보다 큰 값만 허용된다.empname:
VARCHAR2(30)로 정의되고 NOT NULL 제약조건이 적용되어, 반드시 값이 있어야 한다.address:
VARCHAR2(100)로 정의되며,NULL값을 허용하고S로 시작하는 값만 허용하는 CHECK(address LIKE 'S%') 제약조건이 적용되었다.grade:
NUMBER(10,2)로 정의되며, 기본값으로 3.9가 설정되었다. 즉, 값이 입력되지 않으면3.9로 채워지게 된다. 이 컬럼에는 NOT NULL 제약조건이 적용되어 있다.gender:
CHAR(2 CHAR)로 정의되고, 기본값으로 **'남자'**가 설정되었다. 즉, 값이 없을 경우 기본적으로'남자'로 설정된다.regdate:
DATE로 정의되고, 기본값으로 SYSDATE가 설정된다. 즉, 값이 없을 때 현재 날짜가 자동으로 입력된다.
마지막으로:
- CHECK 제약조건이
gender컬럼에 대해 적용되어,'남자'또는'여자'값만 허용된다.
4️⃣ 참조 무결성 (Referential Integrity)
💡KEYWORD: 테이블과 테이블 사이의 관계 정의, 외래키(Foreign Key)제약조건, ON DELETE 옵션
참조 무결성의 정의 ⬇️
💡요약: 참조 무결성은 두 테이블 간의 관계를 관리하고, 데이터를 입력, 수정, 삭제할 때 정합(Consistency)을 유지하는 중요한 제약조건이다. 이를 통해 자식 테이블의 외래 키는 부모 테이블의 기본 키를 반드시 참조하게 된다.
테이블 사이의 관계 규칙을 정의하기 위한 제약조건이다. (A constraint used to define the relationship rules between tables.)
두 테이블이 서로 관련되어 있을 때, 데이터가 입력, 수정, 삭제될 때 두 테이블의 로우 사이의 **정합성(consistency)**과 **무결성(integrity)**을 유지하는 데 사용된다.
구체적인 동작(Specific Operation)
**참조하는 테이블(자식 테이블)**에 데이터가 삽입될 때는 **항상 참조되는 테이블(부모 테이블)**에 있는 값으로 들어가야 한다.
예를 들어, 자식 테이블의 외래 키(Foreign Key)는 반드시 부모 테이블의 기본 키(Primary Key)에 존재하는 값이어야 한다.
외래키 제약조건을 통해 구현된다. (Implemented through foreign key constraints)
- 외래 키는 참조 무결성을 보장하는 중요한 제약조건으로, 두 테이블 간의 관계를 정의하고 일관성을 유지한다.
참조 무결성 구현 ~ 외래키 (Foreign Key) 제약조건 지정⬇️
**💡요약: 외래키(Foreign Key)**는 두 테이블 간의 관계를 정의하는 제약조건이다. 자식 테이블의 컬럼은 부모 테이블의 특정 컬럼 값을 참조해야 한다. 외래키는 데이터 무결성을 보장하며, 데이터의 참조 무결성을 유지한다.
CREATE TABLE emp_test (
empid NUMBER(6,0) NOT NULL CHECK (empid > 0),
empname VARCHAR2(30) NOT NULL,
address VARCHAR2(100) NULL CHECK (address LIKE 'S%'),
grade NUMBER(10,2) DEFAULT 3.9 NOT NULL,
gender CHAR(2 CHAR) DEFAULT '남자' NULL,
regdate DATE DEFAULT SYSDATE NULL,
department_id NUMBER(4,0), #이부분이 추가됨
CONSTRAINT check_gender CHECK (gender IN ('남자', '여자')),
CONSTRAINT fk_deptno FOREIGN KEY (department_id)
REFERENCES departments(department_id)
);
emp_test 테이블에서
department_id컬럼이 추가되었다.외래키 제약조건:
외래키는 department_id 컬럼이 departments 테이블의 department_id 컬럼을 참조하도록 설정되었다.
FOREIGN KEY (department_id)는REFERENCES departments(department_id)를 통해 부모 테이블을 지정하고 있다.
이 제약조건을 통해 emp_test 테이블에서
department_id는 반드시 departments 테이블의 department_id에 존재하는 값이어야만 입력이 허용되게 된다.
참조 무결성 구현 ~ 외래키 (Foreign Key) 제약조건 추가⬇️
💡요약: 외래키(Foreign Key) 제약조건은 테이블을 생성한 후에도 ALTER TABLE 명령을 통해 추가할 수 있다. 이를 통해 자식 테이블의 컬럼이 부모 테이블의 컬럼을 참조하게 만들어, 데이터베이스의 무결성을 유지할 수 있다.
테이블 생성 후에도 컬럼과 외래키 제약조건을 추가할 수 있다.
ALTER TABLE 문을 사용하여 컬럼 추가:
ALTER TABLE emp_test ADD department_id NUMBER(4,0);emp_test테이블에 department_id라는 새로운 컬럼을 추가하였다. 이 컬럼의 데이터 타입은NUMBER(4,0)이다.
외래키 제약조건 추가:
ALTER TABLE emp_test ADD CONSTRAINT fk_deptno FOREIGN KEY (department_id) REFERENCES departments(department_id);이 구문은
emp_test테이블의 department_id 컬럼에 외래키(Foreign Key) 제약조건을 추가하는 명령문이다.이 외래키 제약조건은 departments 테이블의 department_id 컬럼을 참조하도록 설정되어 있다.
참조 무결성 구현 ~ 외래키 (Foreign Key) 제약조건 동작 #1⬇️
💡중요 포인트: 외래키(Foreign Key) 제약조건은 자식 테이블의 값이 부모 테이블에 존재하는 값을 참조하도록 강제한다.
ON DELETE 옵션은 부모 테이블의 데이터가 삭제되었을 때 자식 테이블의 데이터를 자동으로 삭제할 것인지, 아니면 해당 외래키 값을 NULL로 변경할 것인지 결정하는 중요한 옵션입니다. CASCADE는 자식 데이터를 함께 삭제하고, SET NULL은 자식 데이터의 외래키 값을 NULL로 변경하여 데이터를 보존합니다.
- emp_test 테이블을 생성할 때, department_id 컬럼에 외래키 제약조건을 정의하였다. 이 외래키는 departments 테이블의 department_id를 참조하도록 설정되어 있다.
참조 무결성 구현 ~ 외래키 (Foreign Key) 제약조건 동작 #2⬇️
💡중요 포인트: 외래키 제약조건을 설정하면, 자식 테이블의 컬럼 값이 부모 테이블에 존재하는 값을 아래와 같이 SELECT를 통해 참조해야 한다.
departments 테이블에 있는 데이터를 확인한다.
여기서 department_id 컬럼의 값들이 출력되며, 외래키 제약조건은 이 값들을 참조하게 된다.
참조 무결성 구현 ~ 외래키 (Foreign Key) 제약조건 동작 #3⬇️
💡중요 포인트: 부모 테이블에 존재하지 않는 값을 삽입하려고 하면 **무결성 제약조건 위반 오류(ORA-02291)**가 발생한다. 데이터를 삽입할 때 외래키 조건을 만족하도록 부모 테이블의 값을 확인해야 한다.
INSERT INTO emp_test (empid, empname, address, grade, gender, regdate, department_id)
VALUES (103, '이수아', 'SEOUL', DEFAULT, '여자', DEFAULT, 35);
department_id로 35 값을 사용하여 데이터를 삽입하려고 했으나, ORA-02291 오류가 발생했다.오류 메시지: 무결성 제약조건(RF.FK_DEPTNO)이 위배되었습니다. 부모 키가 없습니다.
- 이 오류는
department_id = 35값이 departments 테이블에 존재하지 않기 때문에 발생한 것이다. 외래키는 반드시 참조하는 테이블(부모 테이블)에 존재하는 값을 가져야 한다.
- 이 오류는
참조 무결성 구현 ~ 유의 사항⬇️
💡중요 포인트: 부모 테이블이 존재하고, 외래키가 부모 테이블의 기본키 또는 UNIQUE 컬럼을 참조해야 한다. 부모-자식 관계에서 자식 테이블이 부모 테이블의 데이터를 참조하고 있으면 부모 테이블의 데이터를 삭제할 수 없다. 외래키 관계에서는 데이터를 입력할 때 항상 부모 테이블의 데이터가 먼저 입력되어야 한다.
외래키를 만들기 전에 반드시 부모 테이블이 먼저 생성되어 있어야 함:
- 외래키가 부모 테이블을 참조하므로, 외래키 제약조건을 설정하기 전에 부모 테이블이 존재해야 한다.
참조되는 부모 테이블의 컬럼은 반드시 기본키 또는 UNIQUE 이어야 함:
- 외래키는 부모 테이블의 기본키(Primary Key)나 UNIQUE 제약조건이 설정된 컬럼을 참조해야 한다. 이는 참조 무결성을 보장하기 위함이다.
한 개 이상의 컬럼으로 외래키를 생성할 수 있음 (32개까지 가능):
- 복합 외래키를 생성할 수 있으며, 최대 32개의 컬럼까지 외래키로 지정할 수 있다.
자식 테이블에 존재하는 값을 부모 테이블에서 삭제할 수 없음:
자식 테이블이 부모 테이블의 값을 참조하고 있으면, 부모 테이블에서 해당 값을 삭제하려고 할 때 오류가 발생하게 된다.
예를 들어,
emp_test테이블에서 사용된department_id가 10 또는 30인 값을departments테이블에서 삭제하려고 하면 오류가 발생할 것이다. 이때는 먼저 자식 테이블(emp_test)의 데이터를 삭제한 후 부모 테이블의 데이터를 삭제해야 한다.
입력 시에는 부모 테이블의 데이터를 먼저 입력하고 자식 테이블의 데이터를 입력:
- 자식 테이블이 부모 테이블의 데이터를 참조하므로, 항상 부모 테이블에 먼저 데이터를 입력한 후, 자식 테이블에 데이터를 입력해야 한다.
참조 무결성 구현 ~ ON DELETE 옵션⬇️
💡요약: ON DELETE 옵션은 부모 테이블의 데이터가 삭제되었을 때 자식 테이블의 데이터를 자동으로 삭제할 것인지, 아니면 해당 외래키 값을 NULL로 변경할 것인지 결정하는 중요한 옵션이다. CASCADE는 자식 데이터를 함께 삭제하고, SET NULL은 자식 데이터의 외래키 값을 NULL로 변경하여 데이터를 보존한다.
ON DELETE 옵션 설명
참조된 부모 테이블의 데이터가 삭제되었을 때:
- 부모 테이블의 데이터를 삭제하면 자식 테이블에서 참조 무결성을 위반할 가능성이 있다. 이를 방지하기 위해 ON DELETE 옵션을 사용한다.
자식 테이블의 데이터를 자동으로 삭제하거나, 값을 NULL로 수정하는 두 가지 방법을 사용할 수 있다:
ON DELETE CASCADE:
부모 테이블의 데이터를 삭제하면 자식 테이블의 관련된 데이터도 자동으로 삭제되게 된다.
이 방식은 부모-자식 관계의 데이터가 함께 관리될 때 유용하다.
ALTER TABLE emp_test
ADD CONSTRAINT fk_deptno FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON DELETE CASCADE;
ON DELETE SET NULL:
부모 테이블의 데이터를 삭제하면 자식 테이블의 해당 컬럼 값을 NULL로 설정하게 된다.
자식 테이블의 데이터를 보존하고 싶을 때 사용된다.
sqlCopy codeALTER TABLE emp_test
ADD CONSTRAINT fk_deptno FOREIGN KEY (department_id)
REFERENCES departments(department_id)
ON DELETE SET NULL;
중요 포인트:
ON DELETE CASCADE: 부모 테이블의 데이터를 삭제할 때 자식 테이블의 관련 데이터도 함께 삭제된다.
ON DELETE SET NULL: 부모 테이블의 데이터를 삭제할 때 자식 테이블의 외래키 값을 NULL로 설정한다.
두 가지 방식 모두 참조 무결성을 유지하면서, 부모-자식 테이블 간의 데이터를 어떻게 관리할지를 결정해야 한다.
학습 정리 - 데이터 무결성
