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 requi

Solution 1:

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:

CREATETABLE Users
  ( UserId INTNOTNULL
  , ...
  , PRIMARY KEY (UserId)
  ) ;

CREATETABLE Professors
  ( UserId INTNOTNULL
  , ...
  , PRIMARY KEY (UserId)
  , FOREIGN KEY (UserId)
      REFERENCES Users (UserId)
  ) ;

From what you describe, you probably have defined the foreign key constraint in reverse order.

Solution 2:

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

Solution 3:

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