Microsoft SQL, внешний ключ не ссылается на таблицу, не может понять, что его вызывает

Question

Я потратил некоторое время, пытаясь это сделать, но не вижу ничего, что могло бы вызвать эту ошибку. Я думаю, может быть, что-то делать с "уникальным" заявлением.

Сообщение 1767, уровень 16, состояние 0, строка 40

Внешний ключ 'FK_Loan_ItemNo__0AD2A005' ссылается на недопустимую таблицу 'Item'.

Вот код, если кто-то хочет использовать его для воспроизведения проблемы:

CREATE DATABASE LibrarySystem10
GO
USE LibrarySystem10
GO


CREATE TABLE MemberType(
    MemberTypeNo int NOT NULL,
    Name varchar(50) NOT NULL,
    Description varchar(250) NOT NULL,
    MaxNumberLoans int NOT NULL,
    MaxLoanDuration int NOT NULL
    PRIMARY KEY (MemberTypeNo)
)
insert into MemberType values ('0','UnderGraduate','A student at a college or university who has not yet earned a bachelor''s or equivalent degree.','5','10')
insert into MemberType values ('1','PostGraduate','A student undertaking study after completing a first degree.','10','10')
insert into MemberType values ('2','Staff','Staff at the university','15','15')

CREATE TABLE Member(
    MemberNo int NOT NULL,
    MemberTypeNo int NOT NULL,
    FirstName varchar(150) NOT NULL,
    LastName varchar(150) NULL,
    DateOfBirth varchar (200) NULL,
    HouseNo int NOT NULL,
    Street varchar(50) NOT NULL,
    Suburb varchar(100) NOT NULL,
    PostCode int NOT NULL,
    EmailAddress varchar(250) NULL,
    HomePhoneNo varchar(250) NULL,
    MobileNo varchar(250) NULL,
    MembershipStartDate varchar (200) NOT NULL,
    MembershipEndDate varchar (200) NOT NULL,
    MembershipStatus varchar(100) NOT NULL,
    PinNo int NOT NULL
    PRIMARY KEY (MemberNo)
    FOREIGN KEY (MemberTypeNo)REFERENCES MemberType ON UPDATE CASCADE
) 
insert into member values ('0','0','Shane','Lindsay','15-11-1992','90','fake st','FauxTon','2250','[e-mail]','0243296356','0415657164','15-11-2010','15-11-2020','current','0105')
insert into member values ('1','0','Shaune','Lincoln','18-12-1992','92','faken st','FauxTone','2350','[e-mail]','0243253357','041565757','14-12-2010','14-12-2020','deferred','0123')
insert into member values ('2','0','Sarah','richards','08-08-1990','45','Small st','Hornsby','2279','[e-mail]','02432567154','0416451845','01-01-2012','01-01-2022','current','0123')


CREATE TABLE Loan(
    MemberNo int NOT NULL FOREIGN KEY(MemberNo) REFERENCES Member ON UPDATE CASCADE,
    ItemNo int NOT NULL FOREIGN KEY(ItemNo) REFERENCES Item ON UPDATE CASCADE,
    DateLoaned varchar (50) NOT NULL,
    DueDate varchar (50) NOT NULL,
    Status varchar(50) NOT NULL,
    FinesImposed bit NOT NULL DEFAULT '0' CHECK (finesImposed IN ('0','1')) ,
    Renewed bit NOT NULL DEFAULT '0' CHECK (Renewed IN ('0','1')),
    UNIQUE(MemberNo,ItemNo,DateLoaned)
) 
insert into Loan values ('0','0','10-10-2012','15-10-2012','loaned','0','0')
insert into Loan values ('1','0','12-10-2012','15-10-2012','loaned','0','1')

CREATE TABLE Item(
    ItemNo int NOT NULL,
    Title varchar(50) NOT NULL,
    Subject varchar(100) NULL,
    ISBN int NULL,
    PhysicalDescription varchar(150) NULL,
    Author varchar(75) NULL,
    PRIMARY KEY (ItemNo)
)
insert into Item values ('0','Book1','IT','0501425252','Big,42pages','John Doe')
insert into Item values ('1','Book2','IT','0501425253','Big,42pages','John Doe')


CREATE TABLE ItemCopy(
    ItemNo int NOT NULL,
    CallNumber varchar(50) NOT NULL,
    Condition varchar(50) NULL,
    UNIQUE(ItemNo,CallNumber),
    PRIMARY KEY (CallNumber)
) 
insert into ItemCopy values ('0','0','good')



CREATE TABLE Hold(
    HoldNo int NOT NULL,
    MemberNo int NOT NULL FOREIGN KEY(MemberNo) REFERENCES Member ON UPDATE CASCADE,
    ItemNo int NOT NULL FOREIGN KEY(ItemNo) REFERENCES Item ON UPDATE CASCADE,
    DateTimeHeld datetime NOT NULL,
    comments varchar(200) NULL,
    Status varchar(50) NOT NULL
    PRIMARY KEY (HoldNo)


)

CREATE TABLE Fine(
    FineNo int NOT NULL,
    MemberNo int NOT NULL FOREIGN KEY(MemberNo) REFERENCES Member ON UPDATE CASCADE,
    Description varchar(50) NULL,
    Amount int NOT NULL, 
    PRIMARY KEY (FineNo)
)


CREATE TABLE AudioRecording(
    Length varchar(50) NULL,
    BitRate varchar(50) NULL,
    Size varchar(50) NULL
)

CREATE TABLE ItemCollection(
    ItemNo int NULL,
    CollectionName varchar(75) NULL
    UNIQUE (ItemNo,CollectionName)
)

CREATE TABLE Collection(
    CollectionName varchar(75)NOT NULL
    PRIMARY KEY (CollectionName)
)

CREATE TABLE Book(
    PublisherInfo varchar(150) NULL,
    Edition int NULL,
    Notes varchar(250) NULL,
    Status varchar(50) NULL
)

CREATE TABLE Journal(
    Series int NULL,
    Notes varchar(250) NULL,
    OtherTitles varchar(150) NULL,
    PriorTitles varchar(250) NULL
)


SELECT m.FirstName, l.Status, l.DueDate
    FROM Member m, Loan l
    WHERE  m.MemberNo = '0' AND  l.MemberNo = m.MemberNo 

Answer 1

Заем ссылается на Предмет, но вы создаете Заем до того, как создаете Предмет. Сначала создайте таблицу предметов.