외래 키

이 주제에서는 Spanner의 외래 키와 외래 키가 데이터베이스 솔루션에서 참조 무결성을 적용하는 데 사용될 수 있는 방법을 설명합니다.

개요

외래 키를 사용하면 테이블 간의 관계를 정의할 수 있습니다. Spanner를 사용하면 이러한 관계의 데이터 무결성을 유지할 수 있습니다.

전자상거래 비즈니스의 주요 개발자라고 가정해보겠습니다. 고객 주문을 처리할 데이터베이스를 설계하고 있습니다. 데이터베이스에 각 주문, 고객, 제품에 대한 정보를 저장해야 합니다. 그림 1은 애플리케이션의 데이터베이스 기본 구조를 보여줍니다.

그림 1. 주문 처리 데이터베이스 다이어그램

Customers 테이블은 고객 데이터를 저장하도록, Orders 테이블은 모든 주문을 추적하도록, Products 테이블은 고객이 주문할 수 있는 각 제품에 대한 정보를 저장하도록 정의합니다.

그림 1은 다음과 같이 실제 관계에 매핑되는 테이블 간의 링크도 보여줍니다.

• 고객이 주문함

• 제품을 주문했음

데이터베이스가 Google 시스템의 주문이 유효한지 확인하도록 다음 규칙을 적용해야 합니다.

• 존재하지 않는 고객의 주문을 만들 수 없습니다.

• 고객이 제공되지 않는 제품을 주문할 수 없습니다.

이러한 규칙 또는 제약조건을 적용할 때는 데이터의 참조 무결성을 유지합니다. 데이터베이스가 참조 무결성을 유지할 때는 부적합한 링크 또는 데이터 간 참조를 일으키는 부적합한 데이터를 추가하려는 모든 시도가 실패합니다. 참조 무결성은 사용자 오류를 방지합니다. Spanner는 외래 키를 통해 참조 무결성을 적용합니다.

외래 키를 사용하여 참조 무결성 적용

그림 2에 표시된 것처럼 설계에 추가 세부정보가 추가된 주문 처리 예시를 다시 살펴 보겠습니다.

그림 2. 외래 키가 포함된 데이터베이스 스키마 다이어그램

이 설계에서는 이제 각 테이블의 열 이름과 유형을 보여줍니다. Orders 테이블은 또한 두 가지 외래 키 관계를 정의합니다. FK_CustomerOrder는 Orders의 모든 행에 유효한 CustomerID가 있는지 확인합니다. FK_ProductOrder 외래 키는 Orders 테이블의 모든 ProductID 값이 유효한지 확인합니다. 다음 표에서는 이러한 제약조건을 다시 여기에서 적용하려는 실제 규칙에 매핑합니다.

외래 키 이름	제약조건	실제 상황 설명
FK_CustomerOrder	Orders의 모든 행에 유효한 CustomerID가 있는지 확인합니다.	유효한 고객이 주문했습니다.
FK_ProductOrder	Orders의 모든 행에 유효한 ProductID가 있는지 확인합니다.	유효한 제품을 주문했습니다.

Spanner에서는 Customers 및 Products 테이블에 CustomerID 또는 ProductID가 없는 행을 Orders 테이블에 삽입하거나 업데이트하려고 시도하는 모든 트랜잭션이 실패합니다. 또한 Orders 테이블의 ID를 무효화하는 Customers 및 Products 테이블의 행을 업데이트하거나 삭제하려고 시도하는 트랜잭션도 실패합니다. Spanner가 제약조건을 검증하는 방법에 대한 자세한 내용은 아래의 트랜잭션 제약조건 검증을 참조하세요.

외래 키 정의

외래 키는 DDL을 사용하여 Spanner 데이터베이스에서 생성 및 삭제됩니다. CREATE TABLE 문을 사용하여 새 테이블에 외래 키가 추가됩니다. 마찬가지로 ALTER TABLE 문을 사용하여 기존 테이블에 외래 키를 추가하거나 삭제할 수 있습니다. 다음은 외래 키로 새 테이블을 만드는 예시입니다.

GoogleSQL

CREATE TABLE Orders (
  OrderID INT64 NOT NULL,
  CustomerID INT64 NOT NULL,
  Quantity INT64 NOT NULL,
  ProductID INT64 NOT NULL,
  CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID)
) PRIMARY KEY (OrderID);

PostgreSQL

CREATE TABLE Orders (
  OrderID BIGINT NOT NULL,
  CustomerID BIGINT NOT NULL,
  Quantity BIGINT NOT NULL,
  ProductID BIGINT NOT NULL,
  CONSTRAINT FK_CustomerOrder FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
  PRIMARY KEY (OrderID)
);

외래 키를 만들고 관리하는 방법에 대한 예시를 더 확인하려면 외부 키 관계 만들기 및 관리를 참조하세요.

참고: Google Cloud 콘솔을 사용하여 외래 키를 추가 및 삭제할 수도 있습니다. 테이블 생성 인터페이스에서 텍스트로 편집을 선택하고 DDL을 입력하여 테이블의 외래 키를 관리하세요.

다음은 Spanner의 외래 키 특성 목록입니다.

• 외래 키를 정의하는 테이블은 참조 테이블이고 외래 키 열은 참조 열입니다.

• 외래 키는 참조된 테이블의 참조된 열을 참조합니다.

• 위의 예시에서와 같이 각 외래 키 제약조건의 이름을 지정할 수 있습니다. 이름을 지정하지 않으면 Spanner에서 이름이 자동으로 생성합니다. Spanner의 INFORMATION_SCHEMA에서 생성된 이름을 쿼리할 수 있습니다. 제약조건 이름은 테이블과 색인의 이름을 통해 스키마로 범위가 제한되며 스키마 내에서 고유해야 합니다.

• 참조 열과 참조되는 열의 개수는 동일해야 합니다. 순서가 중요합니다. 첫 번째 참조 열은 첫 번째 참조된 열을, 두 번째 참조 열은 두 번째 참조된 열을 참조하는 방식입니다.

• 참조 열과 참조된 열은 동일한 유형이어야 합니다. 또한 열에서 색인을 생성할 수 있어야 합니다.

• 열에서 allow_commit_timestamp=true 옵션으로 외래 키를 만들 수 없습니다.

• 배열 구조의 열은 지원되지 않습니다.

• JSON 열은 지원되지 않습니다.

• 외래 키는 동일한 테이블의 열을 참조할 수 있습니다('자체 참조' 외래 키). 테이블의 EmployeeId 열을 참조하는 ManagerId 열이 있는 Employee 테이블이 한 예시입니다.

• 외래 키는 두 테이블이 직접 또는 간접적으로 서로를 참조하는 테이블 간의 순환 관계를 형성할 수도 있습니다. 외래 키를 만들기 전에 참조된 테이블이 있어야 하므로 ALTER TABLE 문을 사용하여 최소한 외래 키 중 하나 이상을 추가해야 합니다.

• 참조된 키는 고유해야 합니다. Spanner는 외래 키의 참조된 열이 참조된 테이블의 기본 키 열과 일치하는 경우 참조된 테이블의 PRIMARY KEY를 사용합니다. Spanner가 참조된 테이블의 기본 키를 사용할 수 없으면 참조된 열 위에 UNIQUE NULL_FILTERED INDEX를 만듭니다.

• Spanner는 참조 테이블의 기본 키를 사용할 수도 있지만 이는 일반적이지 않습니다. 그렇지 않은 경우 Spanner는 참조 열 위에 NULL_FILTERED INDEX를 만듭니다.

• 외래 키는 개발자가 만든 보조 색인을 사용하지 않고 자체 지원 색인을 만듭니다. 이러한 지원 색인은 명시적 force_index 지시문을 비롯한 쿼리 평가에 사용될 수 있습니다. Spanner의 INFORMATION_SCHEMA에서 지원 색인 이름을 쿼리할 수 있습니다. 자세한 내용은 지원 색인을 참조하세요.

외래 키 작업

외래 키 작업을 사용하면 참조하는 열이 삭제되거나 업데이트될 때 제한된 열에 수행할 작업을 제어할 수 있습니다. Spanner는 ON DELETE CASCADE 작업의 사용을 지원합니다. 외래 키 ON CADELETEADE 작업을 사용하면 참조된 외래 키가 포함된 행을 삭제할 때 해당 키를 참조하는 모든 행도 동일한 트랜잭션에서 삭제됩니다.

DDL을 사용하여 데이터베이스를 만들 때 작업과 함께 외래 키를 추가할 수 있습니다. CREATE TABLE 문을 사용하여 작업이 있는 외래 키를 새 테이블에 추가합니다. 마찬가지로 ALTER TABLE 문을 사용하여 기존 테이블에 외래 키 작업을 추가하거나 외래 키 작업을 삭제할 수 있습니다. 다음은 외래 키 작업으로 새 테이블을 만드는 방법의 예입니다.

GoogleSQL

CREATE TABLE Customers (
  CustomerId INT64 NOT NULL,
  CustomerName STRING(MAX) NOT NULL,
) PRIMARY KEY(CustomerId);

CREATE TABLE ShoppingCarts (
  CartId INT64 NOT NULL,
  CustomerId INT64 NOT NULL,
  CustomerName STRING(MAX) NOT NULL,
  CONSTRAINT FKShoppingCartsCustomers FOREIGN KEY(CustomerId, CustomerName)
    REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE,
) PRIMARY KEY(CartId);

PostgreSQL

CREATE TABLE Customers (
  CustomerId bigint NOT NULL,
  CustomerName character varying(1024) NOT NULL,
  PRIMARY KEY(CustomerId)
);

CREATE TABLE ShoppingCarts (
  CartId bigint NOT NULL,
  CustomerId bigint NOT NULL,
  CustomerName character varying(1024) NOT NULL,
  PRIMARY KEY(CartId),
  CONSTRAINT fkshoppingcartscustomers FOREIGN KEY (CustomerId, CustomerName)
    REFERENCES Customers(CustomerId, CustomerName) ON DELETE CASCADE
);

다음은 Spanner의 외래 키 작업 특성 목록입니다.

• 외래 키 작업은 ON DELETE CASCADE 또는 ON DELETE NO ACTION입니다.

• INFORMATION_SCHEMA를 쿼리하여 작업이 있는 외래 키 제약조건을 찾을 수 있습니다.

• 기존 외래 키 제약조건에 외래 키 작업을 추가할 수 없습니다. 작업이 포함된 새 외래 키 제약조건을 추가해야 합니다.

장기 실행 스키마 변경사항

기존 테이블에 외래 키를 추가하거나 외래 키가 있는 새 테이블을 만들면 장기 실행 작업이 발생할 수 있습니다. 새 테이블의 경우 장기 실행 작업이 완료될 때까지 테이블에 쓸 수 없습니다.

외래 키가 있는 새 테이블의 경우 Spanner는 각 외래 키에 필요한 참조된 색인을 백필해야 합니다.

외래 키가 있는 기존 테이블의 경우 Spanner는 필요에 따라 참조 색인과 참조된 색인을 백필해야 합니다. 또한 Spanner는 테이블의 기존 데이터를 검증하여 외래 키의 참조 무결성 제약조건을 준수하는지 확인합니다. 데이터가 잘못된 경우 스키마 변경이 실패합니다.

기존 제약조건에 외래 키 작업을 추가할 수 없습니다. 다음 단계를 따르시기 바랍니다.

1. 작업이 포함된 새 제약 조건을 추가합니다.
2. 조치를 취하지 않고 이전 제약 조건을 삭제합니다.

이렇게 하면 Long-running Alter Constraint Operation 문제가 방지됩니다. ON DELETE CASCADE 작업으로 새 외래 키를 만든 후에는 두 제약 조건의 순 효과가 DELETE CASCADE입니다. 제약조건을 삭제하면 다른 외래 키 제약조건에서 색인이 사용되지 않는 경우 외래 키 지원 색인이 삭제될 수 있습니다. 나중에 사용자가 작업을 통해 동일한 외래 키 제약조건을 추가할 경우 색인 백필, 고유 색인 제약조건 검증, 외래 키 참조 제약조건 검증과 같은 장기 실행 작업이 필요할 수 있습니다.

참고: 이전 외래 키 제약조건을 삭제하지 않으면 추가 외래 키 제약조건을 검증하기 때문에 오버헤드가 발생합니다.

UNIQUE 제약조건 위반으로 인해 참조된 색인을 만들 수 없으면 위 스키마 변경 중 하나가 실패할 수 있습니다.

외래 키 생성 상태를 확인하려면 INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.SPANNER_STATE를 쿼리할 수 있습니다.

트랜잭션의 제약조건 유효성 검사

Spanner는 트랜잭션이 커밋될 때 또는 쓰기 효과가 트랜잭션의 후속 작업에 표시될 때 외래 키 제약조건의 유효성을 검사합니다.

참조 열에 삽입된 값은 참조된 테이블과 참조된 열의 값과 일치합니다. 참조 값이 NULL인 행은 확인되지 않습니다. 즉, 이러한 행을 참조 테이블에 추가할 수 있습니다.

Spanner는 DML 문 또는 API를 통해 데이터를 업데이트하려고 할 때 적용 가능한 모든 외래 키 참조 제약조건의 유효성을 검사합니다. 제약조건이 잘못된 경우 대기 중인 모든 변경사항이 롤백됩니다.

유효성 검사는 각 DML 문 바로 뒤에서 수행됩니다. 예를 들어 참조 행을 삽입하기 전에 참조된 행을 삽입해야 합니다. 변형 API를 사용하면 트랜잭션이 커밋될 때까지 변형이 버퍼링됩니다. 외래 키 유효성 검사는 트랜잭션이 커밋될 때까지 지연됩니다. 이 경우 참조 행을 먼저 삽입할 수 있습니다.

각 트랜잭션은 외래 키 제약조건에 영향을 주는 수정 사항이 있는지 평가됩니다. 이 평가에는 서버에 대한 추가 요청이 필요할 수 있습니다. 또한 트랜잭션 수정을 평가하고 색인을 유지하기 위해 지원 색인에 추가 처리 시간이 필요합니다. 또한 추가 스토리지가 각 색인에 필요합니다.

지원 색인

외래 키는 사용자가 만든 색인을 사용하지 않고 자체 지원 색인을 만듭니다.

Spanner는 각 외래 키에 보조 지원 색인을 최대 두 개까지 만들 수 있습니다. 하나는 참조 열용이고 다른 하나는 참조된 열용입니다. 그러나 일반적으로 외래 키는 참조된 테이블의 기본 키를 참조하므로 참조된 테이블의 두 번째 색인은 일반적으로 필요하지 않습니다.

참조된 테이블의 지원 색인은 UNIQUE NULL_FILTERED 색인입니다. 기존 데이터가 색인의 고유성 제약조건을 위반하면 외래 키 생성이 실패합니다. 참조 테이블의 지원 색인은 NULL_FILTERED입니다.

외래 키 두 개 이상에서 동일한 지원 색인이 필요한 경우 Spanner는 모두에 대해 단일 색인을 만듭니다. 지원 색인을 사용하는 외래 키가 삭제되면 지원 색인도 삭제됩니다. 사용자는 지원 색인을 변경 또는 삭제할 수 없습니다.

Spanner는 각 데이터베이스의 정보 스키마를 사용하여 지원 색인에 대한 메타데이터를 저장합니다. true의 SPANNER_IS_MANAGED 값이 있는 INFORMATION_SCHEMA.INDEXES 내 행에서 지원 색인을 기술합니다.

그렇지 않으면 정보 스키마를 직접 호출하는 SQL 쿼리 외부에서 Google Cloud 콘솔은 데이터베이스의 지원 색인에 대해 어떠한 정보도 표시하지 않습니다.

장기 실행 단계식 삭제 작업

참조 테이블에서 행을 삭제하면 Spanner는 삭제된 행을 참조하는 참조 테이블의 모든 행을 삭제해야 합니다. 이로 인해 단일 삭제 작업으로 수천 개의 다른 삭제 작업이 발생하는 연쇄 효과가 발생할 수 있습니다. 테이블에 단계식 삭제 작업이 포함된 외래 키 제약조건을 추가하거나 단계식 삭제 작업이 포함된 외래 키 제약조건이 있는 테이블을 만들면 삭제 작업이 느려질 수 있습니다.

외래 키 단계식 삭제 변형 한도 초과

외래 키 단계식 삭제를 사용하여 다수의 레코드를 삭제하면 성능에 영향을 줄 수 있습니다. 이는 삭제된 각 레코드가 외래 키를 사용하는 레코드와 관련된 모든 레코드의 삭제를 트리거하기 때문입니다. 트랜잭션의 변형 수가 80,000을 초과하면 트랜잭션이 실패합니다.

외래 키 단계식 삭제를 사용하여 다수의 레코드를 삭제해야 하는 경우, 상위 테이블에서 행을 삭제하기 전에 하위 테이블에서 행을 명시적으로 삭제해야 합니다. 이렇게 하면 변형 한도 때문에 트랜잭션이 실패하지 않습니다.

외래 키와 테이블 인터리브 처리 비교

Spanner의 테이블 인터리브 처리는 하위 테이블의 기본 키에 상위 테이블의 기본 키 열이 포함된 여러 상하 관계에 적합합니다. 하위 행과 상위 행을 동일한 위치에 배치하면 성능이 크게 향상될 수 있습니다.

외래 키는 보다 일반적인 상하 솔루션이며 추가 사용 사례를 해결합니다. 기본 키 열로 제한되지 않으며 테이블에는 여러 외래 키 관계가 일부 관계의 상위 요소와 다른 관계의 하위 요소 모두로 포함될 수 있습니다. 그러나 외래 키 관계가 스토리지 레이어에서 테이블의 동일한 위치를 암시하지 않습니다.

이 주제의 앞부분에서 설명한 주문 처리 스키마를 사용하여 예시를 살펴 보겠습니다. Orders 테이블이 다음과 같이 정의되었다고 가정해 보겠습니다.

그림 3. 외래 키가 포함된 데이터베이스 스키마 다이어그램

그림 3의 설계에는 몇 가지 제한사항이 있습니다. 예를 들어 현재 각 주문에는 주문 상품 하나만 포함될 수 있습니다.

고객이 주문당 제품을 두 개 이상 주문할 수 있도록 해 달라고 요청했다고 가정해 보겠습니다. 고객이 주문한 각 제품의 항목이 포함된 OrderItems 테이블을 도입하여 설계를 보정할 수 있습니다. Orders와 OrderItems 간의 새로운 일대다 관계를 나타내는 다른 외래 키를 도입할 수 있습니다. 하지만 주문과 각 주문된 상품에 대해 쿼리를 실행하려 하면 많은 시간이 걸립니다. 이 데이터를 같은 위치에 배치하면 성능이 향상되므로 Spanner의 테이블 인터리브 처리 기능을 사용하여 상하 관계를 만듭니다.

다음은 Orders로 인터리브 처리된 OrderItems 테이블을 정의하는 방법입니다.

GoogleSQL

CREATE TABLE OrderItems (
  OrderID INT64 NOT NULL,
  ProductID INT64 NOT NULL,
  Quantity INT64 NOT NULL,
  FOREIGN KEY (ProductID) REFERENCES Products (ProductID)
) PRIMARY KEY (OrderID, ProductID),
  INTERLEAVE IN PARENT Orders ON DELETE CASCADE;

PostgreSQL

CREATE TABLE OrderItems (
  OrderID BIGINT NOT NULL,
  ProductID BIGINT NOT NULL,
  Quantity BIGINT NOT NULL,
  FOREIGN KEY (ProductID) REFERENCES Products (ProductID),
  PRIMARY KEY (OrderID, ProductID)
) INTERLEAVE IN PARENT Orders ON DELETE CASCADE;

그림 4는 Orders로 인터리브 처리된 새 테이블 OrderItems를 도입하여 업데이트된 데이터베이스 스키마를 시각적으로 보여줍니다. 또한 이 두 테이블 간의 일대다 관계도 확인할 수 있습니다.

그림 4. 인터리브 처리된 OrderItems 테이블 추가

이 구성에서는 각 주문에 여러 OrderItems 항목을 포함할 수 있으며 각 주문에 대한 OrderItems 항목은 인터리브 처리되므로 주문과 같은 위치에 배치됩니다. 이러한 방식으로 Orders 및 OrderItems를 물리적으로 인터리브 처리하면 테이블을 사전에 조인하고 디스크 액세스를 최소화하면서 관련 행에 효과적으로 액세스할 수 있습니다. 예를 들어 Spanner는 로컬에서 기본 키로 조인을 수행하여 디스크 액세스와 네트워크 트래픽을 최소화할 수 있습니다.

트랜잭션의 변형 수가 80,000을 초과하면 트랜잭션이 실패합니다. 이러한 대규모 단계식 삭제는 '상위 인터리브 처리된' 관계가 있는 테이블에는 잘 작동하지만 외래 키 관계가 있는 테이블에는 작동하지 않습니다. 외래 키 관계가 있고 다수의 행을 삭제해야 하는 경우 먼저 하위 테이블에서 행을 명시적으로 삭제해야 합니다.

다른 테이블과의 외래 키 관계가 있는 사용자 테이블이 있고 참조된 테이블에서 행을 삭제하여 수백만 개의 행 삭제가 트리거되는 경우 '상위 요소에 인터리브 처리됨'이 포함된 단계식 삭제 작업이 있는 스키마를 설계해야 합니다.

참고: 상위/하위 관계를 인터리브 처리된 테이블 또는 외래 키 중 하나로 표시하도록 선택하는 것이 좋습니다. 둘 다 사용하면 중복되며 제약조건을 확인하고 지원 색인을 유지하기 위해 추가 스토리지 및 컴퓨팅 리소스가 사용될 수 있습니다.

비교표

다음 표에서는 외래 키와 테이블 인터리브 처리를 비교하는 방법을 요약합니다. 이 정보를 사용하여 설계한 적합한 것을 결정할 수 있습니다.

상하 관계 유형	테이블 인터리브 처리	외래 키
기본 키 사용 가능	예	예
기본 키가 아닌 열 사용 가능	아니요	예
지원되는 상위 수	0 .. 1	0 .. N
상위 및 하위 데이터를 함께 저장	예	아니요
캐스캐이드 삭제 지원	예	예
Null 일치 모드	모든 참조 값이 참조된 값과 다르면 전달됩니다. Null 값은 Null 값과 다릅니다. Null 값은 Null이 아닌 값과 다릅니다.	참조 값이 Null이면 전달됩니다. 모든 참조 값이 Null이 아니면 참조된 테이블에 참조 값과 동일한 값이 있는 행이 포함됩니다. 일치하는 행이 없으면 실패합니다.
적용 시기	변형 API 사용 시 작업 당 DML 사용 시 문당	변형 API 사용 시 트랜잭션당 DML 사용 시 문당
쉽게 삭제 가능	아니요. 전체 하위 테이블을 삭제하지 않는 한 테이블 인터리브 처리는 생성된 후에 삭제될 수 없습니다.	예

다음 단계

• 외래 키 관계 만들기 및 관리 방법 알아보기

• 정보 스키마 자세히 알아보기