Should a foreign key be created on the parent table or child table?

Question

What's the difference? If I have these two tables:

CREATE TABLE Account (Id int NOT NULL)

CREATE TABLE Customer (AccountId int NOT NULL)

And I want a foreign key linking the two, which of the following should I do and why?

Option 1:

ALTER TABLE [dbo].[Customer]  WITH CHECK 
  ADD  CONSTRAINT [FK_Accounts_Customers] FOREIGN KEY([AccountId])
  REFERENCES [dbo].[Account] ([Id])

Option 2:

ALTER TABLE [dbo].[Account]  WITH CHECK 
  ADD  CONSTRAINT [FK_Accounts_Customers] FOREIGN KEY([Id])
  REFERENCES [dbo].[Customer] ([Id])

Solution 1

Depends on context. Does every customer have a client? Which one is the parent? It seems like an Account has multiple Customers, in which case the reference belongs on the Customer table.

Now, that said, please call the entities CustomerID and AccountID everywhere. It may seem redundant on the primary table but the name should be consistent throughout the model.

Solution 2

I would use a foreign key from the child to the parent. The tell tale question is: what happens if you need to delete one of the entities?

Comments

[entonio]

I usually put it on the one table that can't live without the other (when that applies). But it will depend on whatever makes your queries simpler, whatever makes more sense from a data conceptual standpoint... and those two whatevers may be opposite, even.

[scottm]

In this case, if I delete a customer, I don't want it to cascade to accounts, but if I delete an account, it should cascade to customers.

[Charleh]

One thousand percent agreement about the naming. You should not use ID as the field name for a PK - always prefix with the entity name. This is especially important when using ORMs that use convention based mapping as this is often how they expect the model to look. The downside is that you have to qualify your objects when writing joins, but a) you should do this anyway and b) with an ORM you probably wouldn't write much/any T-SQL

[Udo Klein]

So account can live without customer but customer can not live with account. Thus customer should point to account and not the other way round. --> Option 1

[Adir D]

@scottm you should write logic in your DELETE procedure that handles the cascade. I'm not a big fan of - and don't really trust - the ON CASCADE options currently available in SQL Server.

[konung]

If you were to use Rails & rails-inspired frameworks that is not true. In that case when id is a primary key it's just 'id' like customers.id - it's obvious enough. When referenced as a foreign_key you do accounts.customer_id like so .