CODEWITHSUNDEEP
sqlsql-server
Arun Surendran
Arun Surendran

Reputation: 13

Master child table with foreign key and primary key

I am designing a SQL Server database. For the following cases, which is the correct approach?

First approach:

First approach using foreign key also in primary key

Second approach:

Second approach using foreign key only as unique key

Upvotes: 1

Views: 763

Answers (2)

S3S
S3S

Reputation: 25112

This is how I'd do it if you want a user to only be part of a single user type:

create table UserTypes (User_Type_Id int identity(1,1) primary key
                        ,UserDescription varchar(256))

create table Users (UserId int identity (1,1) primary key
                    ,User_Type_Id int foreign key references UserTypes (User_Type_Id)
                    ,FirstName varchar(64)
                    ,LastName varchar(64)
                    ,Email varchar(256))

Some comments...

  • This uses the IDENTITY property which auto increments in this example from 1 to 2+ billion
  • I would split the name into first and last, so you don't have to split them later and cause headaches with name normalization
  • I would avoid using reserved words like DESCRIPTION and USER_ID which are used by SQL Server and thus would need to be enclosed in brackets.

If you want a user to be part of multiple user types, then perhaps:

create table UserTypes (User_Type_Id int identity(1,1) primary key
                        ,UserDescription varchar(256))

create table Users (UserId int identity (1,1) not null
                    ,User_Type_Id int foreign key references UserTypes (User_Type_Id) not null
                    ,FirstName varchar(64)
                    ,LastName varchar(64)
                    ,Email varchar(256))

alter table Users 
add constraint PK_UserID_UserType PRIMARY KEY CLUSTERED(UserId, User_Type_Id)

Upvotes: 1

dfundako
dfundako

Reputation: 8314

Without knowing anything about your project, I'm going to assume the first one is wrong. First, you can't have two primary keys on a table. A primary key, or a unique clustered index, organizes the physical order of the table. You can't organize it in two ways. You can PK two columns into a composite key no problem. Secondly, even if you changed the user type ID to unique instead of a PK, that means only 1 user could exist with each type ID. As soon as you tried to make another user of the same type id, you would violate that unique constraint.

The 2nd model looks better. It assumes that there cannot be the same person with the same role/user type. But the typeID in the user table should be a FK instead of a PK.

Upvotes: 0

Related Questions