sql怎么设置外键

sql server中建立外键约束有3中方式：enterprise manager中，tables,design table，设置table的properties,可以建立constraint, reference key;enterprise manager中，diagrams, new diagrams,建立两个表的关系；直接用transact sql语句。

1、三个方法都需要先建立数据表。

1）创建表author :

create table [dbo][author] (
[id] [bigint] not null ,
[authorname] [char] (10) null ,
[address] [char] (480)  null ,
[introduction] [ntext]  null
)
2）创建表mybbs:

reate table [dbo][mybbs] (
[id] [bigint] identity (1, 1) not null ,
[authorid] [bigint] not null ,
[title] [char] (40)  null ,
[date_of_created] [datetime] null ,
[abstract] [char] (480)  null ,
[content] [ntext]  null
)
2、设置表mybbs中的authorid为外键，参照author表的id字段，直接使用transact sql语句，过程如下：

1）增加表mybbs(authorid)的外键约束fk_mybbs_author，表mybbs中的authorid受表author中的主键id约束：

begin transaction
alter table dbomybbs add constraint fk_mybbs_author
foreign key (authorid)
references  dboauthor([id]) on update cascade on delete cascade
2）删除外键约束fk_mybbs_author：
--alter table dbomybbs drop constraint fk_mybbs_author
--rollback
commit transaction
上面on update cascade，on delete cascade两个选项，指明以后author表的id字段有delete,update *** 作时，mybbs表中的id也会被级联删除或更新。如果没有选中，是不可以对author表中已被mybbs表关联的id进行update或者delete *** 作的。

：

SQL的主键和外键的作用：

1、插入非空值时，如果主键表中没有这个值，则不能插入。

2、更新时，不能改为主键表中没有的值。

3、删除主键表记录时，你可以在建外键时选定外键记录一起级联删除还是拒绝删除。

4、更新主键记录时，同样有级联更新和拒绝执行的选择。

简而言之，SQL的主键和外键就是起约束作用。

给外键起不同的外键名
create table test_zhujian(id int constraint PK_id primary key)
create table test_waijian (id int constraint FK_id1 foreign key references test_zhujian(id))
create table test_waijian2 (id int constraint FK_id foreign key references test_zhujian(id))

可以在创建表的时候创建，也可以在创建表之后创建。
创建表时创建：
create table student
(id int primary key,
name char(4),
dept char(9)
sex char(4))
create table grade
(id int ,
grade int
constraint id_fk foreign key (id) references student (id)
)
或创建了两表之后再建
alter table grade
add constraint id_fk foreign key (id) references student (id)
呵呵，希望能帮助你。

1表中经常有一个列或多列的组合，其值能唯一地标识表中的每一行。这样的一列或多列称为表的主键。
2、外键 (FK) 是用于建立和加强两个表数据之间的链接的一列或多列。通过将保存表中主键值的一列或多列添加到另一个表中，可创建两个表之间的链接。这个列就成为第二个表的外键
Reports表的主键 Sno,Tno,Cno
Sno关联StudentsSno，
Tno关联TeachersTno,
Cno关联CoursesCno

问题背景

在一个多表查询的sql中正常情况下产生的数据都是唯一的，但因为数据库中存在错误(某张表中存在相同的外键ID)导致我这边查询出来的数据就会有重复的问题

下面结果集中UserID：15834存在多个

查询Sql如下：

SELECT FROM (SELECT ROW_NUMBER() OVER ( ORDER BY TUSERID asc )AS Row
,TUSERID
,TCreateTimeFROM UserInfo TLEFT JOIN DiseaseInfo i ON iUserID=TUserID
) TT WHERE TTRow between 0 AND 20 ORDER BY UserID DESC

解决方法：

参考下面新的解决方案

在网络上了解到MSSql中通过关键字“PARTITION BY”可以将查询结果集进行分区处理,然后在查询结果集时就可以过滤掉重复的记录了(如果有指定分区字段则区ID相同)

通过更改后的Sql,在Over中添加PARTITION BY TUSERID以UserID进行分区,然后在查询结果集时通过DISTINCT ROW ,过滤掉重复的分区ID号

SELECT DISTINCT ROW ,FROM (SELECT ROW_NUMBER() OVER (PARTITION BY TUSERID ORDER BY TUSERID asc )AS Row
,TUSERID
,TCreateTimeFROM UserInfo TLEFT JOIN DiseaseInfo i ON iUserID=TUserID
) TT WHERE TTRow between 0 AND 12 ORDER BY UserID DESC

查询时未过滤重复分区IDDISTINCT ROW ,下面的结果集跟上面的结果集不同(Row是进行过分区的所有有重复Row)

 

在查询结果集时过滤掉重复的分区ID号 DISTINCT ROW ,

新解决方案：

由于在Sqlserver中如果多表联合查询中除非所有的字段都完全相同否则在使用DISTINCT 用进行去重时还是会当成两个不同的数据集进行处理,因此DISTINCT会失效即

如下面的结果集，虽然 USERID和其他字段内容相同但HID是不相同的所以无法使用DISTINCT进行去重

出现这种问题是因为数据库设计的错误(正常情况下关联表HospitalInfo中只可能存在一条ClinicInfo表对应的记录)

Sql语句：

SELECT FROM (SELECT ROW_NUMBER() OVER ( order by TUSERID asc )AS Row
,TUSERID
,LEFT(TPatient_Tel1,5)+'00000000' AS Tel
,TCreateTime
,hHName
,hHID
fromUserInfo TLEFT JOIN ClinicInfo c ON cUserID=TUserID AND CDisabled=1LEFT JOIN HospitalInfo H ON HHID=cVisitHospital WHERE TDisabled=1AND tUserID>=17867 AND TUserID<=17875--(TPatient_Tel1 like '%13800000000%')) TT WHERETTRow between 0and20

可以看到上面的结果集中Row是有重复的，其他Row为2的是跟第一个是重复的

因为数据库涉及到其他业务和人员因此我只能提交该问题给相关的技术，但在该问题解决前不能影响到我这边也出现此问题

于是在原sql基础上进行处理,虽然HospitalInfo表中不重复记录但表的自增ID是不可能重复的那我只需要最新的一条记录即可

如果通过DISTINCT过进行去重则就无法成功，因为数据存在差别，可以看到第一条和最后一条数据还是重复的

SELECT DISTINCT row,FROM (SELECT ROW_NUMBER() OVER ( partition by TUSERID order by TUSERID asc )AS Row
,TUSERID
,LEFT(TPatient_Tel1,5)+'00000000' AS Tel
,TCreateTime
,hHName
,hHID
fromUserInfo TLEFT JOIN ClinicInfo c ON cUserID=TUserID AND CDisabled=1LEFT JOIN HospitalInfo H ON HHID=cVisitHospital WHERE TDisabled=1AND tUserID>=17867 AND TUserID<=17875--(TPatient_Tel1 like '%13800000000%')) TT WHERE--row=1 ANDTTRow between 0 and 20

更改后的Sql

SELECT FROM (--partition by TUSERID 以UserID对结果集进行分区SELECT ROW_NUMBER() OVER ( partition by TUSERID order by TUSERID asc )AS Row
,TUSERID
,LEFT(TPatient_Tel1,5)+'00000000' AS Tel
,TCreateTime
,hHName
,hHID
fromUserInfo TLEFT JOIN ClinicInfo c ON cUserID=TUserID AND CDisabled=1LEFT JOIN HospitalInfo H ON HHID=cVisitHospital WHERE TDisabled=1AND tUserID>=17867 AND TUserID<=17875--(TPatient_Tel1 like '%13800000000%')) TT WHERE--因为之前已经以UserID对结果集进行分区,所以如果存在重复的字段则row的值会不相同--row=1 ANDTTRow between 0 and 20

USERID=17867相同经过分区后会存在不同的Row值

在对结果集再次过滤时添加条件 : row=1,已经将重复记录中旧的数据过滤掉了 (HID:78)

根据新的解决方案解决了重复的问题，但又出现的新的问题即Row分区后都是重复的，而我再进行分页的时候就无效了(因为此时结果集中的Row都是为1)

解决方案：在结果集再加一层查询并加上ID号然后再对结果集进行分页处理

-- 新增一层查询解决过滤掉重复数据后无法分页的问题SELECT FROM (SELECT ROW_NUMBER() OVER (ORDER BY userid) AS RowNum,FROM (--partition by TUSERID 以UserID对结果集进行分区SELECT ROW_NUMBER() OVER ( partition by TUSERID order by TUSERID asc )AS Row
,TUSERID
,LEFT(TPatient_Tel1,5)+'00000000' AS Tel
,TCreateTime
,hHName
,hHID
fromUserInfo TLEFT JOIN ClinicInfo c ON cUserID=TUserID AND CDisabled=1LEFT JOIN HospitalInfo H ON HHID=cVisitHospital WHERE TDisabled=1AND tUserID>=17867 AND TUserID<=20875--(TPatient_Tel1 like '%13800000000%')) TT
)AS TWHERE--过滤重复数据Row=1--对结果进行分页AND RowNum between 13 and 24

参考：

MSDN: OVER 子句 (Transact-SQL)

stackoverflow sql query distinct with Row_Number

SQL Trick: row_number() is to SELECT what dense_rank() is to SELECT DISTINCT

---