CODEWITHSUNDEEP
sqlsql-serverreferential-integrity
dotnetnoob
dotnetnoob

Reputation: 11330

Best way to ensure referential integrity

I'm a SQL noob, and whilst I'm aware of the major tools available, I'm not experienced enough to know the best tool for certain situations.

As an example, I current have a group of tables where referential integrity is needed. Each table does not have all the necessary columns itself to be able to constrain the data, so I have at least 3 options open to me.

  1. Create other table/tables that connect the data together - apart from duplicated data, this leaves multiple files to keep synced.

  2. Create a trigger - not too difficult, but how trustworthy is a trigger? And is it scalable?

  3. Create a function - not something I've done before, but I came across an example showing how it could be used to constrain data stored across multiple tables.

Given what I'm trying to do - maintain integrity by joining data, what should I consider, and are all 3 methods suited to what I'm trying to do?

Here an example using a bridge table to link missing table:

enter image description here

Upvotes: 2

Views: 766

Answers (1)

benjamin moskovits
benjamin moskovits

Reputation: 5458

Using foreign keys are the best (and fastest and lightest footstep) way to guarantee data consistency. If you want a table of States to guarantee that only valid state spellings (and states that you do business in) are added to sales orders (so when you search for all sales to New Jersey you only have to search for one spelling) to your other tables there is no easier way then to use FKs.

Upvotes: 1

Related Questions