CODEWITHSUNDEEP
sql-serverdatabasedatabase-designdatabase-relations
Pouria Sharif
Pouria Sharif

Reputation: 166

SQL Server choosing foreign key from another foreign key or unique key

In my scenario I have a table tblCity that has two columns as foreign keys CompanyRef and BranchRef, also they together are unique.

And I'd add one unique key to use as primary key [ID]

And in other table called tblCustomer I need to use tblCity as foreign key.

My problem is that I really need that ID column or I should use two foreign keys as primary key? In second case I must use three column as foreign key (CompanyRef, BranchRef, CityRef) in tblCustomer or what?

Which one of these methods is right for my problem?

Upvotes: 0

Views: 46

Answers (2)

Alexei - check Codidact
Alexei - check Codidact

Reputation: 23078

So, just to make things clear a little bit in your question (I hope I got it right):

tblCity

CityId INT                           -- is part of the separate PK option
CompanyRef INT, FK -> tblCompany
BranchRef INT, FK -> tblBranch

tblCustomer

CustomerId INT                      -- not interesting here
CityRef INT FK -> tblCity           -- is part of the separate PK option
CompanyRef INT                      -- part of the alternative
BranchRef INT                       -- part of the alternative

I can't tell which one is best performance-wise (that's more a DBA question), but from a developer perspective, I would advice for having a single column PK for City:

City sounds like a quite generic concept. It might be needed in the future, so dragging two columns in each other table referencing it, means that each JOIN will be on those two columns.

The final solution could look like this:

tblCity

CityId INT PRIMARY KEY IDENTITY(1, 1),     
CompanyRef INT, FK -> tblCompany
BranchRef INT, FK -> tblBranch
UNIQUE (CompanyRef, BranchRef)      -- acts as a constraint, but also an index

tblCustomer

CustomerId INT                      
CityRef INT FK -> tblCity

Side note: Hungarian notation seems quite discouraged these days - see this very popular question and its answers.

Also, I would advice to keep the same column name for the same thing. E.g.

CompanyRef -> CompanyId (or whatever the PK is named)
BranchRef -> BranchId

Upvotes: 1

porat
porat

Reputation: 1

you need to creat relationship

base of what type of relationship you need to use

primary key and foreign key = one to money

primary key and primary key = one to one

foreign key and foreign key = many to many

Upvotes: 0

Related Questions