SQL 表创建外键出错
IF EXISTS(SELECT * FROM master.dbo.sysdatabases WHERE name='Library_Management_System')
BEGIN
USE master
DROP DATABASE Library_Management_System
PRINT 'Library_Management_System 已存在,已被删除'
END
CREATE DATABASE Library_Management_System
GO
USE Library_Management_System
CREATE TABLE BooksBorrowHdr(
PK_BookNumber VARCHAR(20) PRIMARY KEY NOT NULL,
sTitles VARCHAR(20) NOT NULL,
nPrice NUMERIC(20),
sAuthors VARCHAR(20),
sPress VARCHAR(20),
sISBN VARCHAR(30),
sCategory VARCHAR(20)
)
INSERT INTO BooksBorrowHdr VALUES('A01001','SQL Sever 2000',115,'李代平','地质出版社','ISBN7 - 116 - 03384 -X/T•89','计算机')
CREATE TABLE BooksBorrowDtl(
PK_BookNumber VARCHAR(20) PRIMARY KEY NOT NULL,
sCardNumber VARCHAR(20),
tBorrowingDate DATETIME,
tReturnDate DATETIME,
sAdminCode VARCHAR(20)
)
INSERT INTO BooksBorrowDtl VALUES('A01001','A20100901',2010-10-10,2011-1-01-10,'Admin1')
INSERT INTO BooksBorrowDtl VALUES('A01002','A20100902',2010-10-10,2011-1-01-10,'Admin1')
INSERT INTO BooksBorrowDtl VALUES('A01003','A20100903',2010-10-10,2011-1-01-10,'Admin1')
CREATE TABLE ReadersBorrowHdr(
PK_CardNumber VARCHAR(20) PRIMARY KEY NOT NULL,
sReadersName VARCHAR(15),
nTel NUMERIC(12)
)
INSERT INTO ReadersBorrowHdr VALUES('A20100901','张三',13064139338)
INSERT INTO ReadersBorrowHdr VALUES('A20100902','李四',13578915487)
INSERT INTO ReadersBorrowHdr VALUES('A20100903','王五',13689745821)
CREATE TABLE CategoriesBooksHdr(
PK_Category VARCHAR(20) PRIMARY KEY NOT NULL,
nTotal NUMERIC(4),
sLocation VARCHAR(20)
)
INSERT INTO CategoriesBooksHdr VALUES('计算机',150,'第二层')
INSERT INTO CategoriesBooksHdr VALUES('数学',100,'第三层')
INSERT INTO CategoriesBooksHdr VALUES('英语',120,'第二层')
INSERT INTO CategoriesBooksHdr VALUES('历史',80,'第四层')
CREATE TABLE AdministratorBorrowHdr(
PK_AdminCode VARCHAR(20) PRIMARY KEY NOT NULL,
sAdminName VARCHAR(10),
nTel NUMERIC(13),
sAdimnPassword VARCHAR(16)
)
INSERT INTO AdministratorBorrowHdr VALUES('Admin1','甲',13578945786,'a123456')
INSERT INTO AdministratorBorrowHdr VALUES('Admin2','乙',13878854789,'c128746')
CREATE TABLE BorrowReturnDtl(
PK_Guid uniqueidentifier PRIMARY KEY NOT NULL DEFAULT newid(),
sBookNumber VARCHAR(20),
tBorrowDate DATETIME,
tReturnDate DATETIME,
sAdminCode VARCHAR(20),
sReaderName VARCHAR(20),
nExtended NUMERIC(3)
)
INSERT INTO BorrowReturnDtl(sBookNumber,tBorrowDate,tReturnDate,sAdminCode,sReaderName,nExtended)
VALUES('A01001',2010-10-10,2011-1-15,'Admin1','张三',5)
ALTER TABLE BooksBorrowHdr
ADD CONSTRAINT FK_BookNumber FOREIGN KEY(PK_BookNumber)
REFERENCES BooksBorrowDtl(PK_BookNumber)
ALTER TABLE ReadersBorrowHdr
ADD CONSTRAINT FK_CardNumber FOREIGN KEY(PK_CardNumber)
REFERENCES BooksBorrowDtl(sCardNumber)
ALTER TABLE AdministratorBorrowHdr
ADD CONSTRAINT FK_AdminCode FOREIGN KEY(PK_AdminCode)
REFERENCES BooksBorrowDtl(sAdminCode)
SELECT * FROM BooksBorrowHdr
SELECT * FROM BooksBorrowDtl
SELECT * FROM ReadersBorrowHdr
SELECT * FROM CategoriesBooksHdr
SELECT * FROM AdministratorBorrowHdr
SELECT * FROM BorrowReturnDtl
IF EXISTS(SELECT * FROM sysobjects WHERE name='BooksBorrowHdr'
AND name='BooksBorrowDtl' AND name='ReadersBorrowHdr'
AND name='CategoriesBooksHdr' AND name='AdministratorBorrowHdr'
AND name='BorrowReturnDtl')
BEGIN
DROP TABLE BooksBorrowHdr
DROP TABLE BooksBorrowDtl
DROP TABLE ReadersBorrowHdr
DROP TABLE CategoriesBooksHdr
DROP TABLE AdministratorBorrowHdr
DROP TABLE BorrowReturnDtl
END
请问一下各位我这段语句为什么创建外键时会出现以下错误:
消息 1776,级别 16,状态 0,第 1 行
在被引用表 'BooksBorrowDtl' 中没有与外键 'FK_CardNumber' 中的引用列列表匹配的主键或候选键。
消息 1750,级别 16,状态 0,第 1 行
无法创建约束。请参阅前面的错误消息。
消息 1776,级别 16,状态 0,第 1 行
在被引用表 'BooksBorrowDtl' 中没有与外键 'FK_AdminCode' 中的引用列列表匹配的主键或候选键。
消息 1750,级别 16,状态 0,第 1 行
无法创建约束。请参阅前面的错误消息。