sql – 在引用的表中没有主键或候选键

前端之家收集整理的这篇文章主要介绍了sql – 在引用的表中没有主键或候选键前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。

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,CustomerID int NOT 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
        )
    )

有人可以告诉我这里发生了什么,我可以如何解决它.同样的问题发生在所有其他键我想要一个外键.除非我想引用一个主键.

解决方法

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

如果要显示客户名称,请使用外键引用CustomerID,并使用连接显示结果.

猜你在找的MsSQL相关文章