外键到复合键
我有一个问题,我需要将单个外键引用到另一个表中的复合键.
I have a problem i need to reference a single foreign key to a composite key in another table.
我的数据库结构如下:
CREATE TABLE available_trip (
trip_code integer not null,
date datetime not null,
primary key(trip_code, date),
FOREIGN KEY (trip_code) REFERENCES trip (trip_code)
);
CREATE TABLE booking (
available_trip_code integer not null,
customer_code integer not null,
date datetime not null,
deposit float not null,
total_price float not null,
has_paid float not null,
description_en nvarchar(12) null,
finance_type_code nvarchar(12) not null,
primary key(available_trip_code, customer_code, date),
FOREIGN KEY (available_trip_code) REFERENCES available_trip (trip_code, date),
FOREIGN KEY (customer_code) REFERENCES customer (customer_code),
FOREIGN KEY (finance_type_code) REFERENCES finance_type (finance_type_code)
);
我的问题是:我如何让 booking.available_trip_code
引用 available_trip.trip_code
和 available_trip.date
?
my question is: how do I let booking.available_trip_code
reference to available_trip.trip_code
and available_trip.date
?
推荐答案
如果您引用复合主键,您的外键也需要包含所有这些列 - 所以您需要类似:
If you reference a composite primary key, your foreign key also needs to contain all those columns - so you need something like:
FOREIGN KEY (available_trip_code, date)
REFERENCES available_trip (trip_code, date)
如果您的表中还没有所有这些列,则需要添加它们.
If you don't already have all those columns present in your table, then you'll need to add them.
相关文章