외래 키 관계 만들기 및 관리

이 페이지에서는 데이터베이스에서 외래 키 관계를 관리하는 방법을 설명합니다.

외래 키를 사용하면 테이블 간의 관계를 정의할 수 있습니다. Cloud Spanner는 이러한 테이블 사이의 참조 무결성이 유지되도록 보장합니다. 다음 다이어그램은 이 가이드에서 사용되는 간단한 데이터베이스 스키마를 보여줍니다.

테이블 간의 외래 키 관계를 보여주는 데이터베이스 스키마 예시

그림 1. 주문 처리 애플리케이션 스키마 다이어그램

그림 1에 표시된 스키마에는 다음 3개의 테이블이 있습니다.

  • Customers 테이블에는 각 고객의 이름이 기록됩니다.
  • Orders 테이블은 요청된 모든 주문을 추적합니다.
  • Products 테이블은 모든 제품의 제품 정보를 저장합니다.

이러한 테이블 사이에는 두 개의 외래 키 관계가 있습니다.

  • Orders 테이블과 Customers 테이블 사이에 외래 키 관계가 정의되어 해당하는 고객이 없으면 주문을 만들 수 없습니다.

  • Orders 테이블과 Products 테이블 간의 외래 키 관계는 존재하지 않는 제품에 대한 주문을 생성할 수 없도록 합니다.

이 스키마를 참조로 사용하여 데이터베이스에서 이러한 제약조건을 관리하는 데 사용할 수 있는 데이터 정의 언어 (DDL) 문을 살펴보겠습니다.

새 테이블을 만들 때 외래 키 추가

간단한 제품 주문 데이터베이스에서 Customers 테이블이 이미 생성되었다고 가정해보세요. 고객이 요청한 주문에 대한 정보를 저장할 Orders 테이블이 필요합니다. 모든 주문이 유효하도록 하려면 시스템에서 Customers 테이블의 일치 항목을 포함하지 않는 행이 Orders 테이블에 삽입되지 않도록 해야 합니다.

다음은 Customers 테이블을 참조하는 외래 키 제약조건이 포함된 Orders 테이블의 CREATE TABLE DDL 문입니다.

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);

앞의 문에는 다음과 같이 설명할 수 있는 CONSTRAINT 절이 포함되어 있습니다.

  • CONSTRAINT 구문을 사용하면 선택한 이름을 사용하여 더 쉽게 삭제할 수 있도록 제약조건의 이름을 지정할 수 있습니다.

  • 제약조건 이름은 FK_CustomerOrder입니다. 제약조건 이름은 범위가 해당 스키마로 제한되고 스키마 내에서 고유해야 합니다.

  • 제약조건을 정의하는 Orders 테이블을 참조 테이블이라고 합니다. Customers 테이블은 참조되는 테이블입니다.

  • 참조 테이블의 참조 열은 CustomerID입니다. Customers 테이블의 CustomerID 필드를 참조합니다. Customers에 존재하지 않는 CustomerIDOrders에 행을 삽입하려고 하면 삽입이 실패합니다.

다음 스니펫은 대체 테이블 생성 문을 보여줍니다. 여기에서 외래 키 제약조건은 이름 없이 정의됩니다. 이 구문을 사용하면 Cloud Spanner가 이름을 생성합니다. 모든 외래 키 이름을 찾아보려면 외래 키 관계의 속성 보기를 참조하세요.

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

기존 테이블에 외래 키 추가

또한 존재하는 제품에 대해서만 주문이 생성되도록 해야 합니다. 다음과 같이 ALTER TABLE을 사용하여 또 다른 외래 키 제약조건을 orders 테이블에 추가합니다.

ALTER TABLE Orders
  ADD CONSTRAINT FK_ProductOrder FOREIGN KEY (ProductID) REFERENCES Products (ProductID);

Orders에서 참조 열이 ProductID이면 제품에서 ProductID 열을 참조합니다. Cloud Spanner에서 이러한 제약조건의 이름을 지정하는 경우 다음 구문을 사용합니다.

ALTER TABLE Orders
  ADD FOREIGN KEY (ProductID) REFERENCES Products (ProductID);

외래 키 관계에 대한 데이터 쿼리

SELECT * FROM Orders
  INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
  INNER JOIN Products ON Orders.ProductsID = Products.ProductID;

참조 무결성의 예시

외래 키 관계를 추가하는 주요 이유는 Cloud Spanner가 데이터의 참조 무결성을 유지할 수 있기 때문입니다. 외래 키 제약조건을 위반하는 방식으로 데이터를 수정하면 업데이트가 오류와 함께 실패합니다.

아래 그림 2의 데이터를 살펴보세요. orders 테이블에 표시된 것처럼 일부 고객이 제품을 주문했습니다. 설정된 외래 키로 인해 Orders 테이블에 삽입된 데이터는 참조 무결성을 갖는다는 것을 보장할 수 있습니다.

고객, 제품, 주문 테이블의 샘플 데이터

그림 2. 주문 데이터베이스의 샘플 데이터

참조 무결성을 위반하는 방식으로 데이터를 수정하려고 할 때 어떤 일이 발생하는지 살펴보겠습니다.

Customers에 존재하지 않는 CustomerID값을 가지고 있는 Orders 테이블에 행을 추가합니다.

앞의 다이어그램의 샘플 데이터를 사용해서 다음 수정을 시도하면 어떻게 될까요?

INSERT INTO Orders (OrderID, ProductID, Quantity, CustomerID)
  VALUES (19, 337876, 4, 447);

여기에서는 Customers 테이블에 존재하지 않는 CustomerID (447)로 Orders에 행을 삽입하도록 시도합니다. 이것이 허용되었다면 시스템에 잘못된 주문이 포함될 것입니다. 하지만 Orders 테이블에서 Customers 테이블로 정의한 외래 키 제약조건에 따라 이러한 잘못된 행 삽입이 방지되고, 제약조건이 FK_CustomerOrder라고 할 때 INSERT가 실패하고 다음과 같은 메시지가 표시됩니다.

Foreign key constraint `FK_CustomerOrder` is violated on table `Orders`. Cannot find referenced values in Customers(CustomerID).

고객이 외래 키 제약조건에 참조될 때 Customers 테이블에서 행 삭제를 시도

고객이 온라인 상점에서 구독을 취소하는 경우를 상상해보세요. 백엔드에서 고객을 삭제해야 하므로, 다음 작업을 시도합니다.

DELETE FROM Customers WHERE CustomerID = 721;

이 예시에서 Cloud Spanner는 외래 키 제약조건을 통해 여기에서 삭제하려는 고객 행을 참조하는 레코드가 아직 Orders 테이블에 있다는 것을 감지합니다. 이 경우에는 다음 오류가 표시됩니다.

Foreign key constraint violation when deleting or updating referenced row(s): referencing row(s) found in table `Orders`.

이 문제를 해결하기 위해서는 Orders에서 먼저 모든 참조 항목을 삭제해야 합니다. CustomerIDNULLABLE로 정의된 경우의 또 다른 해결 방법은 참조를 삭제하도록 CustomerID 필드를 NULL로 설정하는 것입니다. CASCADE DELETE는 외래 키에서 지원되지 않습니다.

외래 키 관계의 속성 보기

Cloud Spanner의 INFORMATION_SCHEMA에는 외래 키 및 해당 지원 색인에 대한 정보가 포함되어 있습니다. 다음은 정보 스키마를 쿼리하여 답변할 수 있는 몇 가지 질문의 예시입니다.

지원 색인에 대한 자세한 내용은 외래 키 백업 색인을 참조하세요.

내 데이터베이스에 어떤 제약조건이 정의 되어 있나요?

SELECT tc.CONSTRAINT_NAME, tc.TABLE_NAME, tc.CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as tc
WHERE tc.CONSTRAINT_TYPE = "FOREIGN KEY";

내 데이터베이스에 정의된 외래 키는 무엇인가요?

SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.SPANNER_STATE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;

외부 키 (지원 색인이라고도 함)의 보조 색인은 어떤 색인인가요?

외래 키 지원 색인은 Cloud Spanner에서 관리되므로 INDEXES 뷰에서 SPANNER_IS_MANAGED를 쿼리하면 모든 지원 색인이 반환됩니다.

SELECT i.TABLE_NAME, i.INDEX_NAME, i.INDEX_TYPE, i.INDEX_STATE,
  i.IS_UNIQUE, i.IS_NULL_FILTERED, i.SPANNER_IS_MANAGED
FROM INFORMATION_SCHEMA.INDEXES as i
WHERE SPANNER_IS_MANAGED is true;

자세한 내용은 정보 스키마를 참조하세요.

외래 키 관계 삭제

다음 DDL은 Orders 테이블에서 외래 키 제약조건을 삭제합니다.

ALTER TABLE Orders
  DROP CONSTRAINT FK_CustomerOrder;

외래 키 지원 색인은 제약조건 자체가 삭제될 때 자동으로 삭제됩니다.

보다 복잡한 외래 키 관계 지원

여러 열

외래 키는 여러 열을 참조할 수 있습니다. 열 목록은 테이블의 기본 키 또는 지원 색인에 해당하는 키를 만듭니다. 참조 테이블에는 참조되는 테이블 키의 외래 키가 포함됩니다.

다음 예시에서 외래 키 정의는 Cloud Spanner에 상단 테이블의 각 SongName 값이 노래 테이블에 일치하는 값이 있어야 한다고 알립니다. 각 SingerFirstNameSingerLastName 쌍의 값이 Singers 테이블의 FirstNameLastName 쌍과 일치해야 합니다.

CREATE TABLE TopHits (
  Rank INT64 NOT NULL,
  SongName STRING(MAX),
  SingerFirstName STRING(MAX),
  SingerLastName STRING(MAX),

  -- Song names must either be NULL or have matching values in Songs.
  FOREIGN KEY (SongName) REFERENCES Songs (SongName),

  -- Singer names must either be NULL or have matching values in Singers.
  FOREIGN KEY (SingerFirstName, SingerLastName)
  REFERENCES Singers (FirstName, LastName)

) PRIMARY KEY (Rank);

순환 참조

일부 경우에는 기존 이유 또는 비정규화로 인해 순환 종속성이 테이블에 포함될 수 있습니다. Cloud Spanner 외래 키는 순환 참조를 허용합니다. 외래 키가 참조하기 위해서는 참조되는 테이블이 미리 존재해야 하므로, ALTER TABLE 문으로 외래 키 중 하나를 추가해야 합니다. 예를 들면 다음과 같습니다.

  1. 외래 키 없이 TableA 만들기
  2. TableA의 외래 키 제약조건으로 TableB를 만듭니다.
  3. TableA에서 ALTER TABLE을 사용하여 TableB에 대한 외래 키 참조를 만듭니다.

자체 참조 테이블

순환 참조의 특수 유형 중 하나는 동일한 테이블을 참조하는 외래 키를 정의하는 테이블입니다. 예를 들어 다음 스니펫은 직원의 ManagerId가 직원임을 강제하는 외래 키를 보여줍니다.

CREATE TABLE Employees (
  EmployeeId INT64 NOT NULL,
  EmployeeName STRING(MAX) NOT NULL,
  ManagerId INT64,
  FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId)
) PRIMARY KEY (EmployeeId);

다음 단계