sql - 创建这些表: Could not create constraint or index: Datatype mismatch

Question

任何人都可以弄清楚为什么我会收到此错误并在此处发现我的错误吗？

我阅读了错误消息，但无法发现不同的数据类型？我错过了什么？

CREATE TABLE STAFF (
    StaffLastname NVARCHAR(100) NOT NULL,     
    StaffFirstName  NVARCHAR(100) NOT NULL, 
    StaffID NVARCHAR(100) NOT NULL,
    PRIMARY KEY (StaffID)   
);

CREATE TABLE WAREHOUSE(
    WarehouseLocation NVARCHAR(100) NOT NULL,
    PRIMARY KEY (WarehouseLocation) 
);

CREATE TABLE CUSTOMER (
    CustomerID NVARCHAR(50),        
    CustomerName  NVARCHAR(100) NOT NULL,
    CustomerAddress NVARCHAR(100) NOT NULL,
    CustomerCity  NVARCHAR(100) NOT NULL,
    PRIMARY KEY (CustomerID)    
);

CREATE TABLE COURSE (
    CourseID    NVARCHAR(25),
    CourseName  NVARCHAR(100) NOT NULL,
    PRIMARY KEY (CourseID)  
);

CREATE TABLE COURSE_OFFERING (
    WarehouseLocation NVARCHAR(100) NOT NULL,
    CourseID NVARCHAR(25) NOT NULL,
    CourseDate DATE CHECK (LEN(CourseDate) = 8),
    PRIMARY KEY (WarehouseLocation,CourseID,CourseDate), 
    FOREIGN KEY (CourseID) REFERENCES COURSE,
    FOREIGN KEY (WarehouseLocation) REFERENCES WAREHOUSE
);

CREATE TABLE  BOOKING (
    CustomerID NVARCHAR(50) NOT NULL,   
    WarehouseLocation NVARCHAR(100) NOT NULL,   
    CourseID NVARCHAR(25) NOT NULL, 
    CourseDate DATE CHECK (LEN(CourseDate) = 8),
    BookingDate DATE CHECK (LEN(BookingDate) = 8),
    PaymentAmount MONEY CHECK (PaymentAmount > 0) NOT NULL, 
    Review NVARCHAR(100) NULL,    
    PRIMARY KEY (CustomerID, WarehouseLocation, CourseID, CourseDate),  
    FOREIGN KEY (WarehouseLocation, CourseID, CourseDate) REFERENCES COURSE_OFFERING,
    FOREIGN KEY (CustomerID) REFERENCES CUSTOMER
);

CREATE TABLE STAFF_ALLOCATION (
    StaffID NVARCHAR(100) NOT NULL,
    CourseDate DATE CHECK (LEN(CourseDate) = 8),
    WarehouseLocation NVARCHAR(100) NOT NULL,
    CourseID NVARCHAR(25) NOT NULL,
    PRIMARY KEY (StaffID, CourseDate, CourseID, WarehouseLocation),
    FOREIGN KEY (StaffID) REFERENCES STAFF,
    FOREIGN KEY (CourseDate, WarehouseLocation, CourseID) REFERENCES COURSE_OFFERING
);

ERROR MSG - - -- - - - - - - - - - -

(1 row affected)Msg 1778, Level 16, State 0, Line 65 Column 'COURSE_OFFERING.WarehouseLocation' is not the same data type asreferencing column 'STAFF_ALLOCATION.CourseDate' in foreign key'FK__STAFF_ALLOCATION__5D60DB10'.

Msg 1750, Level 16, State 1, Line 65 Could not create constraint orindex. See previous errors.

Answer 1

在你的最后一个表中，你有一个外键，它有 3 列，数据类型的顺序如下:日期、NVARCHAR、NVARCHAR

它们引用了一个包含 3 列且数据类型顺序如下的表:

NVARCHAR、NVARCHAR、日期

如果您将最后一个表更改为:

CREATE TABLE STAFF_ALLOCATION (
StaffID NVARCHAR(100) NOT NULL,
CourseDate DATE CHECK (LEN(CourseDate) = 8),
WarehouseLocation NVARCHAR(100) NOT NULL,
CourseID NVARCHAR(25) NOT NULL,
PRIMARY KEY (StaffID, CourseDate, CourseID, WarehouseLocation),
FOREIGN KEY (StaffID) REFERENCES STAFF,
FOREIGN KEY (WarehouseLocation, CourseID, CourseDate) REFERENCES COURSE_OFFERING

一切都会好起来的 demo