SQL Server添加外键的误区

先建如下表：

Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表

误区：建好表后急于添加数据，再去添加外键约束。

use SqlDemo
--Select TABLE_NAME FROM SqlDemo.INFORMATION_SCHEMA.TABLES Where TABLE_TYPE='BASE TABLE' 
if exists(select * from Student)
drop table Student;
go

if exists(select * from Course)
drop table Course;
go

if exists(select * from SC)
drop table SC;
go

if exists(select * from Teacher)
drop table Teacher;
go
create table Student
(
  S int primary key,
  Sname varchar(100) NOT NULL,
  Sage int NOT NULL,
  Ssex varchar(100) NOT NULL
) ON [PRIMARY]
create table Course
(
  C varchar(100) primary key,
  Cname varchar(100) NOT NULL,
  T varchar(100) NOT NULL
) 
create table SC
(
  S varchar(100) NOT NULL primary key,
  C varchar(100) NOT NULL,
  Score int NOT NULL
) 
create table Teacher
(
  T varchar(100) primary key,
  Tname varchar(100) NOT NULL
)
-- Add data for each table below
use SqlDemo
insert into Student values(20070901,'张三',20,'男');
insert into Student values(20070902,'李四',20,'女');
insert into Student values(20070903,'王五',20,'男');
insert into Student values(20070904,'麻六',20,'女');
insert into Student values(20070905,'田七',20,'男');
insert into Student values(20070906,'三八',20,'女');
insert into Student values(20070907,'曹九',20,'男');
insert into Student values(20070908,'务十',20,'女');
select * from Student

insert into Teacher values('T1','张老师');
insert into Teacher values('T2','李老师');
insert into Teacher values('T3','王老师');
select * from Teacher;

insert into SC values('S1','数学',59);
insert into SC values('S2','英语',60);
insert into SC values('S3','数据结构',70);
insert into SC values('S4','计算机网络',78);
insert into SC values('S5','数据库',80);
insert into SC values('S6','汇编语言',90);
select * from SC

insert into Course values('C1','数学','张老师');
insert into Course values('C2','英语','李老师');
insert into Course values('C3','数据结构','王老师');
insert into Course values('C4','计算机网络','张老师');
insert into Course values('C5','数据库','李老师');
insert into Course values('C6','汇编语言','王老师');
select * from Course

-- create FK
USE SqlDemo
ALTER TABLE SC
ADD FOREIGN KEY(C)
REFERENCES  Course(C)

结果报错如下：

View Code

Msg 3726, Level 16, State 1, Line 3
Could not drop object 'Course' because it is referenced by a FOREIGN KEY constraint.
Msg 2714, Level 16, State 6, Line 8
There is already an object named 'Course' in the database.

错误分析：

下面给表添加的数据错了：

insert into SC values('S1','数学','张老师');
insert into SC values('S2','英语','李老师');
insert into SC values('S3','数据结构','王老师');
insert into SC values('S4','计算机网络','张老师');
insert into SC values('S5','数据库','李老师');
insert into SC values('S6','汇编语言','王老师');
select * from SC

insert into Course values('C1','数学','张老师');
insert into Course values('C2','英语','李老师');
insert into Course values('C3','数据结构','王老师');
insert into Course values('C4','计算机网络','张老师');
insert into Course values('C5','数据库','李老师');
insert into Course values('C6','汇编语言','王老师');

应该这样：

insert into SC values('S1','C1',59);
insert into SC values('S2','C2',60);
insert into SC values('S3','C3',70);
insert into SC values('S4','C4',78);
insert into SC values('S5','C5',80);
insert into SC values('S6','C6',90);
select * from SC

insert into Course values('C1','数学','张老师');
insert into Course values('C2','英语','李老师');
insert into Course values('C3','数据结构','王老师');
insert into Course values('C4','计算机网络','张老师');
insert into Course values('C5','数据库','李老师');
insert into Course values('C6','汇编语言','王老师');

insert into SC values('S1','C1','张老师');
insert into SC values('S2','C2','李老师');
insert into SC values('S3','C3','王老师');
insert into SC values('S4','C4','张老师');
insert into SC values('S5','C5','李老师');
insert into SC values('S6','C6','王老师');
select * from SC

insert into Course values('C1','数学','张老师');
insert into Course values('C2','英语','李老师');
insert into Course values('C3','数据结构','王老师');
insert into Course values('C4','计算机网络','张老师');
insert into Course values('C5','数据库','李老师');
insert into Course values('C6','汇编语言','王老师');

总结：以后遇到这样的问题，先建好表结构，再添加外键和其他约束，最后添加数据。

本例中如果先添加外键约束，再添加错误的数据是添加不进去了，添加的时候就会报错如下：

SQL Server添加外键的误区

转载于:https://www.cnblogs.com/8090sns/archive/2012/07/19/SQL%e5%a4%96%e9%94%ae.html

SQL Server添加外键的误区

相关推荐