Schema without FK relationship, is it OK

• mario17

  SSCertifiable

  Points: 6079

  More actions

  April 18, 2014 at 12:02 pm

  #285886

  Hi, ALL

  I Just checking a newly deisgned db at my new place, AND see there is NO FK contraints EXISTS FOR many tables, so

  you can enter:

  INSERT INTO tProdValues (Value, CategoryID) rows with any CategoryID (eg. = 99), even

  they don't exist in tCategoryTypes, is this OK?

  I thought that it alwayse shoud be SOME checking..

  Thanks

  Mario

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  April 18, 2014 at 12:33 pm

  #1707160

  mario17 (4/18/2014)

  ---

  Hi, ALL

  I Just checking a newly deisgned db at my new place, AND see there is NO FK contraints EXISTS FOR many tables, so

  you can enter:

  INSERT INTO tProdValues (Value, CategoryID) rows with any CategoryID (eg. = 99), even

  they don't exist in tCategoryTypes, is this OK?

  I thought that it alwayse shoud be SOME checking..

  Thanks

  Mario

  It is impossible to say if it is ok or not from the extremely vague information posted. I can say that it sounds like referential integrity (RI) is a bit lax in that system. Assuming you have a table for CategoryTypes it is generally best to make a foreign key reference to that when using that type in another table. It doesn't make it "wrong" but it certainly raises some red flags.

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

• Grant Fritchey

  SSC Guru

  Points: 398909

  More actions

  April 18, 2014 at 12:37 pm

  #1707161

  In general, I would consider this a very dangerous position. You may have dirty data in your systems and you won't know it.

  Now, you may be in one of those situations where the "application is handling it all." Good luck! It almost never succeeds.

  "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
  - Theodore Roosevelt

  Author of:
  SQL Server Execution Plans
  SQL Server Query Performance Tuning