如何在SQL中引用复合主键 [英] How to reference a composite primary key in SQL

问题描述

使用下面的代码创建了以下3个表。

  CREATE TABLE BUILDING（
 BUILDINGNO CHAR 2），
 BUILDINGWING VARCHAR2（15），
 BUILDINGLANE VARCHAR2（15），
 CONSTRAINT BUILDING_PK PRIMARY KEY（BUILDINGNO））; 
 
 CREATE TABLE ROOM（
 BUILDINGNO CHAR（2），
 ROOMNO CHAR（2），
 ROOMCAPACITY NUMBER（3），
 CONSTRAINT ROOM_PK PRIMARY KEY （BUILDINGNO，ROOMNO），
 CONSTRAINT ROOM_FK1 FOREIGN KEY（BUILDINGNO）建筑物（BUILDINGNO））; 
 
 CREATE TABLE SPEAKER（
 SPEAKERID CHAR（2），
 SPEAKERNAME VARCHAR2（20），
 SPEAKERADDRESS VARCHAR2（50），
 SPEAKERPHONE CHAR（12） ），
 CONSTRAINT SPEAKER_PK PRIMARY KEY（SPEAKERID））; 
  

我需要创建的第四个表格就是这样。

  CREATE TABLE CONFERENCES（
 SESSIONID CHAR（4），
 BUILDINGNO CHAR（2），
 ROOMNO CHAR（2），
 SPEAKERID CHAR（2），
 SESSIONDATE DATE，
 SESSIONPRICE NUMBER（4,2），
 CONSTRAINT SESSION_PK PRIMARY KEY（SESSIONID），
 CONSTRAINT SESSION_FK1 FOREIGN KEY（BUILDINGNO ）参考BUILDING（BUILDINGNO），
 CONSTRAINT SESSION_FK2 FOREIGN KEY（ROOMNO）REFERENCES ROOM（ROOMNO），
 CONSTRAINT SESSION_FK3 FOREIGN KEY（SPEAKERID）REFERENCE SPEAKER（SPEAKERID））; 
  

但是我知道FK2 ROOMNO（最后一行）的约束是不正确的， ROOM表是BUILDINGNO，ROOMNO。
这行代码是正确的吗？

解决方案

我们声明一个SQL FK（FOREIGN KEY）约束来说，列的列表的子值始终出现在其他地方作为形成SQL PK（PRIMARY KEY）或UNIQUE NOT NULL的列的列表的子值。只要不是其他声明所暗示的，就声明它。它必须引用声明的SQL PK（PRIMARY KEY）或UNIQUE NOT NULL中的列列表。所以你必须在被引用的表中声明，即使这已经被NOT NULL和一个小的包含的PK或UNIQUE NOT NULL所暗示。所以请注意，一个SQL PK是不一定是在唯一但不包含较小的唯一列集合的关系意义上的PK，即，不包含较小的超级键的超级键（superkey），即作为最小/不可缩小的超级键，即CK（候选键）。 >

在这里，您可能需要替换 buildingno & roomno FKs by one，（buildingno，roomno） to Room ：

$ b $ pre $ CONSTRAINT SESSION_FK12
FOREIGN KEY（BUILDINGNO，ROOMNO）参考ROOM（BUILDINGNO，ROOMNO）


可能适合您表格的含义 - 实际上，不给，所以我们不能知道，我们只能猜测。例如，如果 buildingno 也可以在房间中声明PK或UNIQUE NOT NULL，当 roomno IS NOT NULL 实际上是一致的，并暗示（buildingno，roomno）可以被声明为PK或UNIQUE NOT NULL，也许你的FK是正确的您的房间声明是不充分的。

当一列列的子值总是出现在别处作为子值获取称为IND（包含依赖）约束的列的列表。在SQL中无法声明非FK IND;我们必须通过触发器强制执行。 $ c>到建筑物，但是它暗示了我建议的FK和FK在 buildingno 上 Room 引用建筑物。

$ b 引用复合主键的一部分

I have created the following 3 tables using the following code.

CREATE TABLE BUILDING(
BUILDINGNO CHAR(2),
BUILDINGWING VARCHAR2(15),
BUILDINGLANE VARCHAR2(15),
CONSTRAINT BUILDING_PK PRIMARY KEY(BUILDINGNO));

CREATE TABLE ROOM(
BUILDINGNO CHAR(2),
ROOMNO CHAR(2),
ROOMCAPACITY NUMBER(3),
CONSTRAINT ROOM_PK PRIMARY KEY(BUILDINGNO,ROOMNO),
CONSTRAINT ROOM_FK1 FOREIGN KEY(BUILDINGNO) REFERENCES BUILDING(BUILDINGNO));

CREATE TABLE SPEAKER(
SPEAKERID CHAR(2),
SPEAKERNAME VARCHAR2(20),
SPEAKERADDRESS VARCHAR2(50),
SPEAKERPHONE CHAR(12),
CONSTRAINT SPEAKER_PK PRIMARY KEY(SPEAKERID));

The 4th table i need to create is something like this.

CREATE TABLE CONFERENCESESSION(
SESSIONID CHAR(4),
BUILDINGNO CHAR(2),
ROOMNO CHAR(2),
SPEAKERID CHAR(2),
SESSIONDATE DATE,
SESSIONPRICE NUMBER(4,2),
CONSTRAINT SESSION_PK PRIMARY KEY(SESSIONID),
CONSTRAINT SESSION_FK1 FOREIGN KEY(BUILDINGNO) REFERENCES BUILDING(BUILDINGNO),
CONSTRAINT SESSION_FK2 FOREIGN KEY(ROOMNO) REFERENCES ROOM(ROOMNO),
CONSTRAINT SESSION_FK3 FOREIGN KEY(SPEAKERID) REFERENCES SPEAKER(SPEAKERID));

However I know that the constraint for the FK2 ROOMNO (2nd last line) is incorrect as the PK in the ROOM table is BUILDINGNO,ROOMNO. What would be the correct code to use for this line?

解决方案

We declare an SQL FK (FOREIGN KEY) constraint to say that a subrow value for a list of columns always appears elsewhere as a subrow value for a list of columns that forms an SQL PK (PRIMARY KEY) or UNIQUE NOT NULL. Declare it whenever it isn't already implied by other declarations. It must reference the column list in a declared SQL PK (PRIMARY KEY) or UNIQUE NOT NULL. So you must declare that in the referenced table, even if that's already implied by NOT NULLs and a smaller contained PK or UNIQUE NOT NULL.

So note that an SQL PK is not necessarily a PK in the relational sense of being unique but not containing a smaller unique column set, ie being a superkey not containing a smaller superkey, ie being a minimal/irreducible superkey, ie being a CK (candidate key).

Here, you might need to replace the buildingno & roomno FKs by one, (buildingno, roomno) to Room:

CONSTRAINT SESSION_FK12
    FOREIGN KEY(BUILDINGNO,ROOMNO) REFERENCES ROOM(BUILDINGNO,ROOMNO)

That might be appropriate for the meanings of your tables--which in fact you don't give, so we can't know, we can only guess. Eg if buildingno could also be declared PK or UNIQUE NOT NULL in Room, which when roomno IS NOT NULL is actually consistent with and implies (buildingno, roomno) could be declared PK or UNIQUE NOT NULL, maybe your FK is right but your Room declarations are inadequate.

When a subrow value for a list of columns always appears elsewhere as a subrow value for a list of columns that is called an IND (inclusion dependency) constraint. There's no way to declare a non-FK IND in SQL; we must enforce by triggers. That also might be what you need for your design.

You could keep the FK from buildingno to Building, but it's implied by the FK I suggest and the FK in buildingno on Room referencing Building.

referencing part of the composite primary key

这篇关于如何在SQL中引用复合主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！