如何在SQL Server Management Studio中设置1到0 ... 1关系 [英] How to set 1 to 0...1 relationship in SQL Server Management Studio
问题描述
我有表:
用户{UserId ...}
教授{UserId ...}
我将 UserId
设置为两个表中的PK并且做了1:1的关系。
但是,如果我尝试插入新用户,它不起作用,因为它需要插入教授
table too。
我想让一位用户在教授表中只能有1条记录,但我也想让它不会必须存在于教授表(我不想让所有用户教授:))。
如何设置1到(0 ... 1)关系SQL Server Management Studio?
我知道设置对NO的强制键约束不是解决方案:)
如果您有以下要求:
- a用户可以是教授 - 不是
- a教授总是一个用户
那么你是正确的,它是一个 1 :: 0..1
关系。在SQL中,可以这样实现:
CREATE TABLE用户
(UserId INT NOT NULL
,...
,PRIMARY KEY(UserId)
);
CREATE TABLE Professors
(UserId INT NOT NULL
,...
,PRIMARY KEY(UserId)
,FOREIGN KEY(UserId)
参考用户(UserId)
);
根据您所描述的内容,您可能已经以相反的顺序定义了外键约束。
I have tables:
Users{UserId ...}
Professors{UserId ...}
I set UserId
to be PK in both tables and made 1:1 relationship.
But if I try to insert new user it doesn't work as it requires insert in the Professor
table too.
I want to make that one user can have only 1 record in Professor table but I also want to make it that it doesn't have to exist in Professor table (I don't want to make all users professors :) ).
How to set 1 to (0...1) relationship in SQL Server Management Studio?
I know that set enforce key constraints to NO is not a solution :)
If you have these requirements:
- a User can be Professor - or not
- a Professor is always a User
then you are correct that it's a 1 :: 0..1
relationship. In SQL, it can be implemented like this:
CREATE TABLE Users
( UserId INT NOT NULL
, ...
, PRIMARY KEY (UserId)
) ;
CREATE TABLE Professors
( UserId INT NOT NULL
, ...
, PRIMARY KEY (UserId)
, FOREIGN KEY (UserId)
REFERENCES Users (UserId)
) ;
From what you describe, you probably have defined the foreign key constraint in reverse order.
这篇关于如何在SQL Server Management Studio中设置1到0 ... 1关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!