CODEWITHSUNDEEP
sql-serverdatabaseforeign-keys
hendoe
hendoe

Reputation: 163

Foreign key to table A and B, where A already have a foreign key to B

Suppose there is a table called Accounts:

CREATE TABLE Accounts
(
    [Id] int not null primary key identity(1,1)
    [Username] varchar(20) not null unique,
    [Password] varchar(20) not null
)

Then, there is another tabled called Characters. Each account can have N characters. So I can use a foreign key to link these characters.

CREATE TABLE Characters
(
    [AccountId] int not null foreign key references Accounts([Id]),
    [Id] int not null primary key identity(1,1),
    [Nickname] varchar(20) not null unique,
    [Level] int not null default 0,
)

Each character can have multiple equipments (inventory), so there is a Equipments table. Since each equipment is linked to a character, I should use foreign key again, and there comes the problem.

Me and my coworker were arguing about which foreign key to use. Since each character has a unique Id, I told him that we could use foreign key to that Id and that would be enough. As follows:

CREATE TABLE Equipments
(
    [CharId] int not null foreign key references Characters([Id]),
    [ItemId] int not null
)

He told me that we must use a foreign key to the character id AND the account id, as follows:

CREATE TABLE Equipments
(
    [AccountId] int not null foreign key references Accounts([Id]), /*is this necessary?*/
    [CharId] int not null foreign key references Characters([Id]),
    [ItemId] int not null
)

I'm not expert in Sql Server and in my opinion, the foreign key to the account id is completely unecessary but he keeps telling me that we must use it and it will help performance because the more foreign key you use, it will be better.

So, should I use foreign key to account id and character id or character id is good enough?

Upvotes: 0

Views: 457

Answers (3)

DVT
DVT

Reputation: 3127

As you said, there is a one-to-many relationship between Account and Character (and hence, a character cannot belong to more than one account). Similarly, as you described, each record in Equipments only corresponse to a unique record in Characters. The relation from Account to Equipments hence can be inferred, and so, there is no need to create an extra column in the Equipments table. Also, the data integrity is preserved just by the two foreign keys already created, so that should not be a problem when you go without the AccountId column in the Equipments table.

Regarding the performance argument, this is a case-by-case situation, and it depends on a lot of other things (number of records, business logic,...). Having unnecessary foreign key can even hurt performance since the database/server will need to maintain that foreign key while operate. Also, I found that if you do not have the key and when you find out that you need it, it is easier to add one in than to remove an existing one, especially when you have to create a whole new column for this one (this last piece is a mere personal opinion).

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You are correct, but for a more important reason.

If you include Accountid in the Equipments table, then you have a second relationship to the Accounts table. Perhaps this is allowed, but in all likelihood, you intend to have the Characters.AccountId be the account id for a row in Equipments.

You would then get the appropriate account id by using a join to the Equipments table.

Upvotes: 1

TigOldBitties
TigOldBitties

Reputation: 1337

You should use it only if you plan to interrogate equipment directly for an account which is faster than joining with account via char. Otherwise, no, you shouldn't use it.

Upvotes: 1

Related Questions