关于外键引用的问题，引用两个表作为外键

[LittleAlamo]

我想让tb_user_info(user_num)外键引用tb_student_info(stu_num)
或者tb_teacher_info(thr_num)？这种类型的外键怎么设置？

create table tb_student_info(
stu_num int not null primary key,
stu_name varchar(20) not null
/* student 拥有的 字段*/
)
create table tb_teacher_info(
thr_num int not null primary key,
thr_name varchar(20) not null
/* teacher 拥有的 字段*/
)
create table tb_user_info(
user_num int not null primary key,
user_nickname varchar(20) not null
/* user 拥有的 字段*/
)

[發糞塗牆]

据我了解不能实现“或者”的功能。

create table tb_user_info(
user_num int not null primary key,
user_nickname varchar(20) not NULL,
FOREIGN KEY (user_num) REFERENCES  tb_student_info(stu_num ),
FOREIGN KEY (user_num) REFERENCES  tb_teacher_info(thr_num )
)

[中国风]

tb_user_info.user_num 来自tb_student_info。stu_num或tb_teacher_info.thr_num 这类情况建议在程序端控制或在存储过程里判断 实现方法用自定义函数判断

use tempdb
go
Create table tb_student_info(stu_num int primary key)
Create table tb_teacher_info(thr_num int primary key) 

insert into tb_student_info values(1);
insert into tb_teacher_info values(2);
go
create function fn_Check_user_num(
@user_num int
)returns bit
as
begin
	if exists(select 1 from tb_student_info where stu_num=@user_num)
	or exists(select 1 from tb_teacher_info where thr_num=@user_num)
		return 1
	return 0
end

go

Create table tb_user_info(user_num int primary key check (dbo.fn_Check_user_num(user_num)=1)) 
  
go
insert into tb_user_info values(1)--OK
insert into tb_user_info values(2)--OK
insert into tb_user_info values(3)--ERROR
/*
消息 547，级别 16，状态 0，第 1 行
INSERT 语句与 CHECK 约束"CK__tb_user_i__user___4CA06362"冲突。该冲突发生于数据库"tempdb"，表"dbo.tb_user_info", column 'user_num'。
语句已终止。
*/
go
select * from tb_user_info
/*
user_num
1
2
*/

[中国风]

引用 1 楼 DBA_Huangzj 的回复:

据我了解不能实现“或者”的功能。SQL code?123456create table tb_user_info(user_num int not null primary key,user_nickname varchar(20) not NULL,FOREIGN KEY (user_num) REFERENCES tb_student_info(stu_num ),……

这样是不行的，语法可以这样用，是插入不了任何值

[-晴天]

USE [test]
GO
CREATE TABLE [dbo].[tb_user_info](
	[user_num] [int] NOT NULL PRIMARY KEY,
	[user_nickname] [varchar](20) NOT NULL,
	[stu_num] [int] NULL
CONSTRAINT
	FK_tb_user_info_tb_student_info_stu FOREIGN KEY
	(
	stu_num
	) REFERENCES dbo.tb_student_info
	(
	stu_num
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION ,
	[thr_num] [int] NULL
CONSTRAINT
	FK_tb_user_info_tb_teacher_info_thr FOREIGN KEY
	(
	thr_num
	) REFERENCES dbo.tb_teacher_info
	(
	thr_num
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
)

[發糞塗牆]

引用 3 楼 roy_88 的回复:

引用 1 楼 DBA_Huangzj 的回复: 据我了解不能实现“或者”的功能。SQL code?123456create table tb_user_info(user_num int not null primary key,user_nickname varchar(20) not NULL,FOREIGN KEY (user_num) REFERENCES tb_student_inf……

谢谢大版指导