CODEWITHSUNDEEP
sqldatabasedatabase-designrelational-databasessms
1110
1110

Reputation: 6829

How to set 1 to 0...1 relationship in SQL Server Management Studio

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

Upvotes: 2

Views: 8726

Answers (3)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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.

Upvotes: 8

SCV
SCV

Reputation: 416

I think you are should use foreign key here. Professor ID should be the foreign key in the user table, that will solve all your problem.

Just look up what foreign key is and how to write foreign key query

Upvotes: 0

Jonysuise
Jonysuise

Reputation: 1830

In the professors table you should create a ProfessorID and add the UserID as a FK nullable.

Upvotes: -1

Related Questions