Key

💡 Key (기본키, 후보키, 슈퍼키 등등...) 에 대해 설명해 주세요.

키(key) 는 테이블(Relation)에서 특정 레코드(행)를 식별하거나 검색하기 위해 사용되는 식별자입니다. 각 레코드는 여러 개의 필드(열, column)로 구성되어 있는데, 이 중 하나의 열을 키로 지정하여 그 값을 사용해 해당 레코드를 식별하고 접근할 수 있습니다. 동시에 각 테이블 간의 관계를 말해주는 연결고리이기도 합니다.

 

 

키의 종류

 

• 슈퍼키(Super Key): 테이블 내의 행을 유일하게 식별할 수 있는 하나의 속성 또는 속성의 집합
  (ex. {학번}, {학번 + 이름}, {주민번호 + 학번}) - 유일성
• 복합키(Composite Key): 2개 이상의 속성(attribute)를 사용한 키
• 후보키(Candidate key): 행을 유일하게 식별할 수 있는 속성의 최소 집합. 기본키가 될 수 있는 후보이기 때문에 후보키라고 불립니다. (ex. 주민번호, 학번 등) - 유일성, 최소성
• 기본키(Primary key): 후보 키에서 선택된 키. NULL값이 들어갈 수 없으며, 기본키로 선택된 속성(Attribute)은 중복된 값이 들어갈 수가 없습니다. 한 테이블 내에 하나의 기본키가 정의됩니다. - Not Null, Unique
• 대체키(Alternate key): 기본키로 선택되지 않은 후보키
• 외래키(Foreign Key): 다른 테이블(Relation)의 기본 키(Primary key)를 참조하는 속성. 테이블(Relation)들 간의 참조관계를 나타내기 위해서 사용됩니다.

 

📑 꼬리질문

기본키는 수정이 가능한가요?

• 기본키는 기본적으로 유일성과 최소성을 만족해야하기 때문에 기본키 값은 수정해서는 안됩니다. 만약 기본키가 변경되면 해당 레코드를 참조하는 다른 테이블의 일관성 및 무결성을 해치는 문제가 발생할 수 있습니다.
• 그럼에도 기본키 값을 변경해야 한다면, (해당 기본키를 참조하고 있는 외래키가 있는 경우) 외래키 제약 조건을 일시적으로 비활성화 한 후, 참조된 모든 외래키 값을 수정하려는 값으로 업데이트 합니다. 이후, 기본키의 값도 업데이트하고, 외래키 제약 조건을 다시 활성화 합니다.

위 Customers 테이블에서 기본키인 특정 고객의 CustomerID를 변경해야 한다고 가정해보겠습니다. 
(ex. Customers의 기본키 CustomerID의 값 236을 456으로 변경하고 싶습니다.)
만약 이 고객의 관련된 주문 데이터가 있다면, 그 주문 데이터도 수정되어야 합니다. 

-- 1. 데이터 백업: 이 단계는 SQL 외부에서 수행됩니다.

-- 2. 트랜잭션 시작
BEGIN TRANSACTION;

-- 3. 외래키 제약 조건 해제
ALTER TABLE Orders NOCHECK CONSTRAINT FK_Orders_Customers;

-- 4. 외래키 값 업데이트
UPDATE Orders
SET CustomerID = 456
WHERE CustomerID = 236;

-- 5. 기본키 값 업데이트
UPDATE Customers
SET CustomerID = 456
WHERE CustomerID = 236;

-- 6. 외래키 제약 조건 복원
ALTER TABLE Orders WITH CHECK CHECK CONSTRAINT FK_Orders_Customers;

-- 7. 트랜잭션 커밋
COMMIT;

 

사실 MySQL의 경우, 기본키를 설정하지 않아도 테이블이 만들어집니다. 어떻게 이게 가능한 걸까요?

• MySQL의 경우 버전 8부터 세팅에 따라 Generated Invisible Primary Keys라는 특성을 통해 자동으로 보이지 않는 기본키를 생성하게 됩니다. 즉, 기본키를 명시적으로 정의하지 않아도 내부적으로 자동으로 기본키를 생성하여, InnoDB 스토리지 엔진이 클러스터링된 인덱스를 사용할 수 있도록 합니다.
  
  • Generated Invisible Primary Key(GIPKs)
    
    • 사용자가 명시적으로 기본키를 정의하지 않은 테이블을 생성할 때
    • 테이블의 모든 레코드에 대해 고유하게 식별할 수 있는 다른 인덱스가 없는 경우
• 또한, 기본키 없이 테이블을 생성하는 것은 DBMS의 동작 방식과 데이터베이스 디자인에 관련된 문제입니다.
  • 유연성 과 선택권: 데이터베이스 설계자에게 기본키를 어떻게 구성할지에 대한 유연성과 선택권을 제공합니다. 어떤 경우에는 테이블의 각 레코드를 고유하게 식별할 필요가 없거나(데이터 분석이나 로그 데이터 저장과 같은 용도), 기본 키 대신 다른 유니크한 인덱스나 조건을 사용하고자 할 수 있습니다.

 

외래키 값은 NULL이 들어올 수 있나요?

• 네, 참조 테이블의 외래키(Foreign Key) 값은 명시적으로 NOTNULL을 지정하지 않으면 일반적으로 NULL 값이 들어올 수 있습니다. 외래키는 (다른 or 자기 자신의) 테이블 기본키를 참조하는데, 이 때 해당 외래키 값이 참조하는 레코드가 없는 경우에는 NULL 값을 가질 수 있습니다.

 

어떤 칼럼의 정의에 UNIQUE 키워드가 붙는다고 가정해 봅시다.
이 칼럼을 활용한 쿼리의 성능은 그렇지 않은 것과 비교해서 어떻게 다를까요?

1. 검색 및 조회 성능
   • UNIQUE 키워드가 붙은 칼럼은 해당 값들이 중복되지 않도록 보장되며, 이때 UNIQUE index가 생성되게 됩니다.
   • 데이터 조회 시(자세히는 Where절에 조건이 들어오는 경우) DB 옵티마이저는 전체 테이블을 참조하는 Full Scan 방식이 아닌 인덱스를 참조하는 Index Unique Scan 방식을 사용해 조회 성능을 향상시킵니다.
2. 조인 성능
   • UNIQUE index가 있는 칼럼을 다른 테이블과 조인할 때 성능이 향상됩니다. 조인 연산은 인덱스를 기반으로 이루어지므로 중복된 값이 없는 경우 조인 결과를 빠르게 얻을 수 있습니다.
3. 삽입 및 업데이트 성능
   • UNIQUE 제약 조건이 있는 칼럼에 새로운 값을 삽입하거나 값이 변경되는 경우, 데이터베이스 시스템은 중복 여부를 확인해야 하므로 약간의 오버헤드가 발생할 수 있습니다. 하지만 이 오버헤드는 일반적으로 데이터 무결성의 이점에 비해 미미합니다.
4. 인덱스 관리 및 저장 공간
   • UNIQUE index는 중복된 값이 없는 경우에만 생성되므로 인덱스의 크기가 더 작아질 수 있습니다. 또한 중복된 값이 없기 때문에 인덱스의 관리도 간단해지며 저장 공간도 효율적으로 사용될 수 있습니다.

 

🐍 꼬꼬무

기본키를 다른 속성으로 변경 가능한가요?

• 해당 테이블의 기존 기본키 제약조건을 없앤 다음 다른 속성으로 기본키 제약조건을 지정하면 됩니다. 마찬가지로 데이터의 무결성을 위해 지양해야하는 방법이라고 생각합니다.

 

MySQL에서 PK를 명시적으로 정의하지 않아도, PK를 설정하게 하는 방법에는 어떤 것이 있을까요?

• MySQL의 경우 명시적으로 Primary Key를 지정하지 않으면 내부적으로 사용자에게 노출하지 않는 기본키(Generated Invisible Primary Key, GIPK) 를 생성해 활용합니다.
• 기본키가 아니더라도 UNIQUE 제약을 만족하는 모든 Column을 찾아서 기본 키의 역할을 수행하게 할 수 있습니다.

 

유일성, 최소성에 대해서 설명해주세요.

• 유일성 : 하나의 키 값으로 유일한 하나의 레코드를 찾아낼 수 있어야 한다.
• 최소성 : 키를 구성하는 속성들 중 꼭 필요한 최소한의 속성들로만 키를 구성해야한다.

 

개체 무결성, 참조 무결성, 도메인 무결성에 대해서 설명해주세요.

• 개체 무결성 : 기본 키는 Null 값, 중복값을 가질 수 없다. (NOTNULL, UNIQUE)
• 참조 무결성 : 참조 테이블의 외래키 값은 NULL이거나 피참조 테이블의 기본키 값과 동일해야 한다.
• 도메인 무결성 : 속성의 값이 속성에 정의된 도메인에 속한 값이어야 한다. (성별의 경우 ‘남’, ‘여’)

 

📚 Reference

MySQL Docs - create table gipks

티스토리 - [DataBase] 키(Key)의 개념 및 종류

깃허브 - Table 작성 시 PK를 무조건 사용해야 하는 이유

티스토리 - Oracle SQL - Index Unique Scan

티스토리 - [DB] 키의 개념과 종류

티스토리 - [DB] 📚 데이터베이스 키(KEY) 종류 🕵️ 정리

벨로그 - Unique 제약조건과 조회시 성능상의 이점

도서 - MySQL로 배우는 데이터베이스 개론과 실습

 

 

본 포스팅은 CS스터디에서 팀원들과 함께 정리한 내용으로 Github에서도 확인이 가능합니다💙

잘못된 내용이 있다면 친절한 댓글로 알려주시면 감사하겠습니다 😊