能否设置一个外键同时引用两张不同数据表的主键？

Great question! Let's break this down clearly for your multi-service business scenario:

Short Answer #

No, standard relational database systems (like PostgreSQL, MySQL, SQL Server) don’t support a single foreign key column that directly references primary keys from two separate tables. Foreign key constraints are built to enforce a relationship between one column (or set of columns) and the primary/unique key of one specific table—they can’t split their reference across two distinct datasets.

Better Solutions for Your Use Case #

Since you offer two distinct service types (room bookings and home repairs), here are two robust, scalable approaches to model this correctly:

1. Supertype-Subtype Table Design (Highly Recommended)

This is the cleanest way to handle shared service data while rock-solid integrity. Create a base "parent" table for all services, then have child tables for each specific service type:

• Service (Supertype Table)

  CREATE TABLE Service (
      Service_ID INT PRIMARY KEY,
      Service_Type VARCHAR(50) NOT NULL CHECK (Service_Type IN ('ROOM_BOOKING', 'HOME_REPAIR')),
      Cost DECIMAL(10,2) NOT NULL
  );
  

• RoomBookingService (Subtype Table)

  CREATE TABLE RoomBookingService (
      Service_ID INT PRIMARY KEY REFERENCES Service(Service_ID),
      Start_Date DATE NOT NULL,
      End_Date DATE NOT NULL,
      Room_ID INT NOT NULL
  );
  

• HomeRepairmentService (Subtype Table)

  CREATE TABLE HomeRepairmentService (
      Service_ID INT PRIMARY KEY REFERENCES Service(Service_ID),
      Repair_Type VARCHAR(50) NOT NULL,
      Staff_ID INT NOT NULL,
      Date_Of_Repair DATE NOT NULL
  );
  

• Invoice Table
  Now you can safely link invoices to the unified Service table:

  CREATE TABLE Invoice (
      Invoice_ID INT PRIMARY KEY,
      Service_ID INT NOT NULL REFERENCES Service(Service_ID),
      Customer_ID INT NOT NULL
  );
  

This design keeps shared attributes (like Service_ID and Cost) in one place, enforces that each service belongs to exactly one type, and lets your Invoice table reference a single, unambiguous source.

2. Check Constraints + Triggers (Workaround, Not Ideal)

If you absolutely can’t restructure your existing tables, you can use a combination of check constraints and triggers to mimic the behavior—but this is fragile and harder to maintain:

• Remove any direct foreign key from Invoice.Service_ID
• Add a BEFORE INSERT and BEFORE UPDATE trigger on the Invoice table. The trigger will check if the Service_ID exists in either RoomBookingService or HomeRepairmentService. If it doesn’t exist in either, the trigger throws an error to block the change.
• You can add a check constraint to align with the logic, but triggers are required to perform cross-table validation.

The downside here is that triggers add complexity, can introduce concurrency issues, and don’t provide the same automatic integrity guarantees as proper foreign keys.

Why Direct Dual References Aren’t Allowed #

Relational databases rely on foreign keys to enforce referential integrity—they need a clear, single source to validate against. A foreign key pointing to two tables would create ambiguity: how would the database know which table to check when inserting or updating a record? This breaks the core rules that foreign keys are designed to uphold.

内容的提问来源于stack exchange，提问作者Weiji Cai