引用表中没有主键或候选键 [英] There are no Primary or Candidate Keys in the referenced table

问题描述

错误:引用表中没有主键或候选键'dbo.Customers' 匹配外部引用列列表键'FK_Reservation_Customers_FrstNme 外键'

Error: There are no Primary or Candidate Keys in the referenced table 'dbo.Customers' that match the referencing column list in the foreign key 'FK_Reservation_Customers_FrstNme FOREIGN KEY'

DROP TABLE dbo.Customers;
DROP TABLE dbo.Staff;
DROP TABLE dbo.Rooms;
DROP TABLE dbo.Reservation;
GO
CREATE TABLE "Customers"(

    CustomerID int IDENTITY (1,1) NOT NULL,
    FirstName nvarchar(20) NULL,
    LastName nvarchar(20) NULL,
    StreetNo int NULL,
    City nvarchar(20) NULL,
    PostCode nvarchar(20) NULL,
    Email nvarchar(50) NULL,

    CONSTRAINT PK_Customers PRIMARY KEY
    (
        CustomerID
    )
)
CREATE TABLE "Staff"(

    StaffID nvarchar(20) NOT NULL,
    Pass nvarchar(20) NOT NULL,

    CONSTRAINT PK_Staff PRIMARY KEY
    (
        StaffID
    )
)
CREATE TABLE "Rooms"(

    RoomNo int NOT NULL,
    RoomType nvarchar(20) NULL,
    PricePerNight money NULL,
    MaximumOccupancy int NULL,
    No0fBeds int NULL,
    NoOfBathrooms int NULL,
    Entertainment bit NULL,
    RoomService bit NULL,
    Gym bit NULL,

    CONSTRAINT PK_Rooms PRIMARY KEY
    (
        RoomNo
    )
)
CREATE TABLE "Reservation"(

    ReservationID int IDENTITY (1,1) NOT NULL,
    CustomerID int NOT NULL,
    FirstName nvarchar(20) NULL,
    LastName nvarchar(20) NULL,
    RoomType nvarchar(20) NULL,
    RoomNo int NOT NULL,
    CheckInDate date NULL,
    CheckOutDate date NULL,

    CONSTRAINT PK_Reservation PRIMARY KEY
    (
        ReservationID
    ),
    CONSTRAINT FK_Reservation_Customers_CustID FOREIGN KEY
    (
        CustomerID
    )   
        REFERENCES dbo.Customers
        (
            CustomerID
        ),
    CONSTRAINT FK_Reservation_Customers_FrstNme FOREIGN KEY
    (
        FirstName
    )
        REFERENCES dbo.Customers
        (
            FirstName
        )
    )

有人可以告诉我这里发生了什么以及我如何解决它.我想制作外键的所有其他键都会出现同样的问题.除非我想引用主键.

Could someone please tell me whats happening here and how i can fix it. Same problem occurs with all the other keys i want to make a foreign key. Except if i want to reference a primary key.

推荐答案

如果要创建外键，它必须引用主键或具有唯一约束的字段.

If you want to create a foreign key, it must reference either the primary key, or a field with a unique constraint.

如果你想显示客户的名字，让外键引用CustomerID，然后用join来显示结果.

If you want to display the customer's name, make the foreign key reference the CustomerID, and display the results with a join.

这篇关于引用表中没有主键或候选键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！