외래 키 관계 만들기 및 관리

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

외래 키는 관련 데이터 간의 연결을 설정하기 위해 테이블 간에 공유되는 열입니다. 외래 키를 사용할 경우 Spanner는 이 관계가 유지되도록 합니다.

다음 다이어그램은 테이블의 데이터가 다른 테이블의 데이터와 관련이 있는 간단한 데이터베이스 스키마를 보여줍니다.

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

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

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

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

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

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

이 주제에서는 이전 스키마를 예시로 사용하여 데이터베이스의 테이블 간의 관계를 관리하는 데 사용할 수 있는 데이터 정의 언어(DDL) CONSTRAINT 문을 설명합니다.

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

간단한 제품 주문 데이터베이스에서 Customers 테이블을 만들었다고 가정해 보겠습니다. 이제 고객이 주문한 주문에 대한 정보를 저장할 Orders 테이블이 필요합니다. 모든 주문이 유효하도록 하려면 Customers 테이블에 일치하는 항목이 없으면 시스템에서 Orders 테이블에 행을 삽입하지 않도록 해야 합니다. 따라서 두 테이블 간의 관계를 설정하려면 외래 키가 필요합니다. 한 가지 선택은 새 테이블에 CustomerID 열을 추가하고 이를 외래 키로 사용하여 Customers 테이블의 CustomerID 열과 관계를 만드는 것입니다.

외래 키로 새 테이블을 만들 때는 REFERENCE를 사용하여 다른 테이블과의 관계를 설정합니다. REFERENCE 문이 포함된 테이블을 참조 테이블이라고 합니다. REFERENCE 문에서 이름이 지정된 테이블은 참조된 테이블입니다. REFERENCE 문에서 이름이 지정된 열을 참조 열이라고 합니다.

다음 예시에서는 CREATE TABLE DDL 문을 사용하여 Customers 테이블의 CustomerID를 참조하는 외래 키 제약조건으로 Orders 테이블을 만드는 방법을 보여줍니다.

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

이전 문에는 다음과 같은 특성이 있는 CONSTRAINT 절이 포함되어 있습니다.

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

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

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

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

참고: 참조되는 테이블의 참조되는 열이 기본 키 열과 일치하는 경우도 있지만 필수는 아닙니다. 참조 테이블은 참조되는 테이블의 기본이 아닌 열을 참조할 수도 있습니다.

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

GoogleSQL

CREATE TABLE Orders (
  OrderID INT64 NOT NULL,
  CustomerID INT64 NOT NULL,
  ProductID INT64 NOT NULL,
  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,
  FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID),
  PRIMARY KEY (OrderID)
);

기존 테이블에 외래 키 추가

또한 고객이 존재하는 제품만 주문할 수 있도록 해야 합니다. 먼저 기존 제약조건을 삭제해야 합니다. 그런 다음 아래와 같이 ALTER TABLE을 사용하여 또 다른 외래 키 제약조건을 Orders 테이블에 추가할 수 있습니다.

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

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

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

새 테이블을 만들 때 삭제 작업이 있는 외래 키 추가

Orders 테이블이 필요한 제품 주문 데이터베이스에 Customers 테이블이 있는 이전 예시를 떠올려 보세요. Customers 테이블을 참조하는 외래 키 제약조건을 추가하려고 합니다. 하지만 나중에 고객 레코드를 삭제할 때 Spanner도 해당 고객의 모든 주문을 삭제하는지 확인하려고 합니다. 이 경우 외래 키 제약조건에 ON DELETE CASCADE 작업을 사용해야 합니다.

Orders 테이블의 다음 CREATE TABLE DDL 문에는 ON DELETE CASCADE 작업으로 Customers 테이블을 참조하는 외래 키 제약조건이 포함됩니다.

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) ON DELETE CASCADE
) PRIMARY KEY (OrderID);

PostgreSQL

CREATE TABLE Orders (
  OrderID BIGINT NOT NULL,
  CustomerID BIGINT NOT NULL,
  Quantity BIGINT NOT NULL,
  ProductID BIGINT NOT NULL,
  FOREIGN KEY (CustomerID)
    REFERENCES Customers (CustomerID) ON DELETE CASCADE,
  PRIMARY KEY (OrderID)
);

이전 문에는 ON DELETE CASCADE 절이 있는 외래 키 제약조건이 포함되어 있습니다. CustomerID 열은 Customers 테이블의 CustomerID 필드를 참조하는 외래 키입니다. 즉, Orders 테이블의 각 CustomerID 값이 Customers 테이블에도 있어야 합니다. 누군가 Customers 테이블에서 행 삭제를 시도하면 삭제된 CustomerID 값을 참조하는 Orders 테이블의 모든 행도 동일한 트랜잭션에서 삭제됩니다.

참고: 외래 키 작업을 지정하지 않으면 기본 작업은 NO ACTION입니다. 즉, 누군가가 Customers 테이블에서 행을 삭제하려고 하고 Orders 테이블에 삭제된 CustomerID 값을 참조하는 행이 있으면 외래 키 제약조건 위반 오류로 삭제 작업이 실패합니다.

삭제 작업이 있는 외래 키를 기존 테이블에 추가

또한 존재하는 제품에 대해서만 주문이 생성되도록 해야 합니다. 다음과 같이 ALTER TABLE을 사용하여 ON DELETE CASCADE 작업이 포함된 다른 외래 키 제약조건을 주문 테이블에 추가할 수 있습니다.

ALTER TABLE Orders
  ADD CONSTRAINT DB_ProductOrder FOREIGN KEY (ProductID)
    REFERENCES Products (ProductID) ON DELETE CASCADE;

Products 테이블에서 행을 삭제하면 삭제된 ProductID 값을 참조하는 Orders 테이블의 모든 행이 삭제됩니다.

참고: 기존 외래 키 제약조건에 외래 키 작업을 추가하거나 삭제할 수 없습니다. 작업이 있는 새 외래 키 제약조건을 추가해야 합니다. 자세한 내용은 장기 실행 스키마 변경사항을 참조하세요.

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

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

Spanner가 참조 무결성을 유지하는 방법 예시

외래 키 관계를 추가하는 주요 이유는 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 테이블에 추가한 외래 키 제약조건이 있으면 테이블이 보호됩니다. 제약조건이 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;
  

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

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

  이 문제를 해결하려면 먼저 Orders에서 참조 항목을 모두 삭제하세요. 또한 Spanner가 참조 항목 삭제를 처리하도록 ON DELETE CASCADE 작업으로 외래 키를 정의할 수도 있습니다.

외래 키 관계의 속성 보기

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;

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

외래 키 지원 색인은 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 = 'YES';

외래 키 제약조건으로 정의된 참조 작업은 무엇인가요?

SELECT rc.CONSTRAINT_NAME, rc.UNIQUE_CONSTRAINT_NAME, rc.DELETE_RULE,
  rc.UPDATE_RULE
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS as rc;

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

외래 키 관계 삭제

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

ALTER TABLE Orders
  DROP CONSTRAINT FK_CustomerOrder;

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

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

여러 열

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

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

GoogleSQL

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

PostgreSQL

CREATE TABLE TopHits (
  Rank BIGINT NOT NULL,
  SongName VARCHAR,
  SingerFirstName VARCHAR,
  SingerLastName VARCHAR,

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

순환 참조

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

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

자체 참조 테이블

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

GoogleSQL

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

PostgreSQL

CREATE TABLE Employees (
  EmployeeId BIGINT NOT NULL,
  EmployeeName VARCHAR NOT NULL,
  ManagerId BIGINT,
  FOREIGN KEY (ManagerId) REFERENCES Employees (EmployeeId),
  PRIMARY KEY (EmployeeId)
);

다음 단계

• Spanner에서 외래 키 지원 자세히 알아보기

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