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

2013-01-02 02:39:23 229 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

【2023】Java利用modbus4j实现工业设备Modbus TCP数据采集与解析本文详细介绍了在2023年使用Java和modbus4j库实现工业设备Modbus TCP数据采集与解析的完整实践。内容涵盖开发环境搭建、TCP连接建立、开关量与模拟量数据的读取与解析、批量读取性能优化、控制指令写入以及常见错误排查指南，为开发者构建稳定高效的工业物联网数据采集系统提供全面指导。阅读详情

我想让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 拥有的字段*/
)

...全文

海康工业相机触发模式详解：软触发 vs 外触发，在C#里如何选与用？本文深入解析海康工业相机的软触发与外触发模式，详细比较两者的特性差异、适用场景及C#实现方法。通过海康SDK配置指南和实战代码示例，帮助开发者掌握工业相机触发模式的选择与优化技巧，提升自动化检测系统的精确性和可靠性。阅读详情

「已注销」

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

發糞塗牆 2013-01-02

打赏
举报

引用 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……

谢谢大版指导

-晴天 2013-01-02

打赏
举报

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 
)

中国风 2013-01-02

打赏
举报

引用 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 ),……

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

中国风 2013-01-02

打赏
举报

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
*/

發糞塗牆 2013-01-02

打赏
举报

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


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 )
)

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

相关推荐