我想让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 拥有的 字段*/
)mysql oracle sql 外键
或者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 拥有的 字段*/
)mysql oracle sql 外键
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 )
)
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
endgoCreate 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
*/
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
)