首页 文章

sql server 2008中引用的表中没有主键或候选键

提问于
浏览 次
0

ClassCode表

CREATE TABLE [dbo].[ClassCode](
[SchoolCode] [nvarchar](10) NOT NULL,
[ClassCode] [nvarchar](4) NOT NULL,
[ClassName] [nvarchar](50) NOT NULL,
[ClassRange] [int] NOT NULL,
[ClassDuration] numeric(38,2) NOT NULL,
[UserID] [nvarchar](30) NULL,
[RecordDate] [smalldatetime] NULL,  
 CONSTRAINT [PK_ClassCode] PRIMARY KEY CLUSTERED 
(
    [SchoolCode] ASC,
    [ClassCode] ASC,
    [ClassRange]
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

考试声明表

CREATE TABLE [dbo].[ExamDeclaration](
[SchoolCode] [nvarchar](10) NOT NULL,
[ClassCode] [nvarchar](4) NOT NULL,
[ExamCode] [nvarchar](4) NOT NULL,
[RegistationFess] numeric(38,2) NOT NULL,
[RegistatinStartDate] [date] NOT NULL,
[RegistatinEndDate] [date] NOT NULL,
[ExamStartDate] [date] NOT NULL,
[UserID] [nvarchar](30) NULL,
[RecordDate] [smalldatetime] NULL
 CONSTRAINT [PK_ExamDeclaration] PRIMARY KEY CLUSTERED 
(
    [SchoolCode] ASC,
    [ClassCode] ASC,
    [ExamCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


GO

ALTER TABLE [dbo].[ExamDeclaration]  WITH CHECK ADD  CONSTRAINT [FK_ExamDeclaration_ClassCode] FOREIGN KEY([ClassCode])
REFERENCES [dbo].[ClassCode] ([ClassCode])
GO

ALTER TABLE [dbo].[ExamDeclaration] CHECK CONSTRAINT [FK_ExamDeclaration_ClassCode]
GO

我正在尝试设置外键ClassCode

但得到了这个错误

消息1776，级别16，状态0，行2在引用的表'dbo.ClassCode'中没有与外键'FK_ExamDeclaration_ClassCode'中的引用列列表匹配的主键或候选键 . 消息1750，级别16，状态0，行2无法创建约束 . 查看以前的错误 . 消息4917，级别16，状态0，行2约束'FK_ExamDeclaration_ClassCode'不存在 . 消息4916，级别16，状态0，行2无法启用或禁用约束 . 查看以前的错误 .

这两个表有什么问题？

谢谢..

sql-server

1 回答

  • 0

    您的外键必须与主键完全匹配！如果您声明主键 PK(X int,Y int, Z int) ，那么FK必须声明如下： FK (a int, b int, c int) references MyTable(X,Y,Z) .

    在您的情况下，您的Pk是（SchoolCode，ClassCode，ClassRange） . 这意味着您应该像这样声明FK：

    ALTER TABLE [dbo].[ExamDeclaration] WITH CHECK ADD CONSTRAINT [FK_ExamDeclaration_ClassCode] FOREIGN KEY(SchoolCode, ClassCode, ClassRange) REFERENCES [dbo].[ClassCode] (SchoolCode, ClassCode, ClassRange) GO

    修改你的表ExamDeclaration，添加'ClassRange'列 .

    回复于

相关问题