时间戳

首页 > 解决方案 > 建立强制一到可选一

sql - 建立强制一到可选一

问题描述

假设我有三张桌子：

我希望 Customer to Reservation 是 Mandatory-One 到 Optional-One（这意味着预订需要一位客户，而一位客户可以有零对一的预订）。

车辆也是如此：预订需要一辆车，而一辆车可以有零对一的预订。

我将如何创建这些表？

我尝试使用自己的 ID 列（C_ID、V_ID、R_ID）创建每个表作为 PK，然后在保留表中为 C_ID（非空）和 V_ID（非空）创建 FK 约束，但这似乎允许客户和车辆有多个预订。

正确的做法是对 Reservation 表中的 C_ID 和 V_ID（单独）应用唯一约束吗？

编辑：来自 Oracle SQL Developer 的模型选项卡屏幕截图 Oracle SQL Developer 的“模型”选项卡屏幕截图

标签： sqloracleoracle-sqldeveloper

解决方案

是的，使客户外键列在您的预订表中唯一。像这样：

CREATE TABLE customers
  ( customer_id NUMBER NOT NULL,
    CONSTRAINT customers_pk PRIMARY KEY ( customer_id ) );

CREATE TABLE reservations 
  ( reservation_id NUMBER NOT NULL,
    customer_id    NUMBER NOT NULL,
    CONSTRAINT reservations_pk PRIMARY KEY (reservation_id ),
    CONSTRAINT reservations_fk1 FOREIGN KEY ( customer_id ) REFERENCES customers ( customer_id ),
    CONSTRAINT reservations_u1 UNIQUE ( customer_id )
  );

-- Fails, customer does not exist        
insert into reservations ( reservation_id, customer_id ) VALUES ( 100, 10 );  

-- Fails, reservation requires customer
insert into reservations ( reservation_id, customer_id ) VALUES ( 100, null );  

-- Fine.. Customer does not need a reservation;
insert into customers ( customer_id ) VALUES ( 20 );   

-- Fine.  One reservation for one customer
insert into reservations ( reservation_id, customer_id ) VALUES ( 200, 20 );   

-- Fails -- cannot have more than one reservation for same customer    
insert into reservations ( reservation_id, customer_id ) VALUES ( 201, 20 );

推荐阅读