CODEWITHSUNDEEP
sql-serversql-server-2008database-design
001
001

Reputation: 65117

Database Design: OK? not to use foreign key?

Is it ok if i dont use a foreign key, when i could use it, i just dont use it?

For example, my COUNTRY_STATE table should have Country.Id as the foreign key, but i didn't do that instead have CountryCode. I get the states for each country by CountryCode. 

COUNTRY
-Id (PK)
-Code
-Name

COUNTRY_STATE
-Id (PK)
-Code
-Name
-CountryCode

Upvotes: 1

Views: 195

Answers (4)

brian
brian

Reputation: 3695

A foreign key that does not use nocheck will allow the query optimizer to simplify the plan. This will allow fewer reads which will improve query performance. This will have a dramatic effect on performance, when your row count gets high.

Upvotes: 0

user330315
user330315

Reputation:

A foreign key does not necessarily need to reference a primary key. It can also reference a unique constraint.

So if you want to keep the country_code in the country_state table, you can do that and still have a foreign key that ensure that only valid country codes are used:

ALTER TABLE country 
  ADD CONSTRAINT uq_code UNIQUE (code);

ALTER TABLE country_state 
    ADD CONSTRAINT fk_state_country 
    FOREIGN KEY (country_code) REFERENCES country (code);

Upvotes: 4

marcocamejo
marcocamejo

Reputation: 828

It is not OK, you are violating the Referencial Integrity property

Upvotes: 2

Joe Mancuso
Joe Mancuso

Reputation: 2129

There is nothing that guarantees that the country code will be unique, so you have introduced a possible one-to-many relationship there. That means that a country state could have more than one country...which is undesirable.

Upvotes: 0

Related Questions