CODEWITHSUNDEEP
sql-serverforeign-keysprimary-key
Lukas Narus
Lukas Narus

Reputation: 113

Two connected tables in which same record has same Foreign Key

I have a SQL table Users with some users with PK's. I need to create second table UserInfo with same users from table Users. I am connect these tables with FK's. The difficulty is that I need to both table's users have SAME PRIMARY KEY. Like User "Peter" with (UserId 5) MUST have (UserInfoId 5) in UserInfo table . Is that possible and if it is, how can i do that?

Upvotes: 0

Views: 28

Answers (1)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112762

Either there is a one to one relationship between Users and UserInfo, in that case UserInfo.UserID is PRIMARY KEY and FOREIGN KEY at the same time.

CREATE TABLE UserInfo (
    UserID int PRIMARY KEY,
    Info varchar(max),
    CONSTRAINT FK_UserInfo_User FOREIGN KEY (UserID) REFERENCES Users(UserID)
        ON DELETE CASCADE
);

OR

You have a one to many relationship between Users and UserInfo, in that case UserInfo.UserID is a FOREIGN KEY and you need a separate PRIMARY KEY UserInfo.UserInfoID.

CREATE TABLE UserInfo (
    UserInfoID int  PRIMARY KEY,
    UserID int NOT NULL,
    Info varchar(max),
    CONSTRAINT FK_UserInfo_User FOREIGN KEY (UserID) REFERENCES Users(UserID)
        ON DELETE CASCADE
);

Upvotes: 1

Related Questions