| Author |
Topic |
|
dshelton
Yak Posting Veteran
73 Posts |
 Posted - 2004-01-06 : 19:52:42
|
| Hi All,I am currently working on a database that has no foreign key's. The DBA that built the db believed that FK's are a performance hindrance and chose not to include them in the design.I have seen first hand what can happen with no FK's and would like to implement this on the database. However, if the application only allows selections from lists of existing records, theoretically speaking, there would not be a need for FK's. Should I push for FK's or let the application enforce the referential integrity?? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-06 : 20:03:26
|
| First of all, the DBA should be stripped of his/her title for thinking that referential integrity was not needed.Secondly, what happens if data is modified manually through Enterprise Manager or Query Analyzer? What about if the application has a bug in it and allows orphaned data to be created? Referential integrity prevents both of these. The performance hindrance is so minimal that it shouldn't even be considered.Tara |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
dshelton
Yak Posting Veteran
73 Posts |
Posted - 2004-01-06 : 21:15:31
|
| hehe, nice one.That's what I thought, overhead is well worth the benefit. Thanks heaps. |
|
|
|
Merkin
Funky Drop Bear Fearing SQL Dude!
4970 Posts |
Posted - 2004-01-06 : 21:37:39
|
Want me to come and visit ? I can bring my big stick  Damian |
|
|
|
dshelton
Yak Posting Veteran
73 Posts |
Posted - 2004-01-07 : 00:24:54
|
| that's alright, no need for violence yet. Keep it handy though. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-01-07 : 10:39:44
|
| Well.........What TYPE of database is itAre we talking about massive heap tables in a batch environement?Brett8-) |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-01-07 : 12:15:32
|
| >> However, if the application only allows selections from lists of existing recordsI wouldn't trust it.Also can you delete any of the reffered entries? If so and it's a multi-user system then it can cause problems.You can get away with it if all access is via SPs and the SPs enforce (correctly) the integrity.Or you could do it via triggers but would be better off with the RI.I have implemented systems like this but only if I am sure about who has access to the data (only me). It can make it easier to change data and violating the RI at intermediate steps but you will also need scripts to check that everything is OK afterwards.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
dshelton
Yak Posting Veteran
73 Posts |
Posted - 2004-01-07 : 18:02:46
|
| Some reference tables are only small (< 100), but there are others that have over 30000 records and they are going to get alot larger as this is a new db. |
|
|
|
dshelton
Yak Posting Veteran
73 Posts |
Posted - 2004-01-07 : 18:27:07
|
| We are not going to allow deletes at all so no worries there. Also, no one can directly access the db besides a select few and we are only using SP's to access the db.With the larger reference tables, do you's think it will be quicker to enforce the integrity in a proc or FK? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-01-07 : 18:30:14
|
| Use foreign keys! Use foreign keys for large tables, small tables, and anything in between.Tara |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-01-07 : 22:32:38
|
| Agreed. Another reason for using foreign keys is that any database diagramming/documenting software will pick them up and include them on the diagram, but they will not pick up triggers that enforce integrity. Same thing applies in modeling software, they never (or very rarely) will model integrity with a trigger. And if someone else ever has to look at your structure, a foreign key is a lot more clear than a trigger, and certainly better than relying on application logic. |
|
|
|
JohnDeere
Posting Yak Master
191 Posts |
Posted - 2004-01-08 : 00:06:54
|
| This implementation seems to be common among commercially developed packages. We are evaluating ERP systems and the majority of the packages we have reviewed implement RI at the application level instead of the database level. The vendors have a variety of reasons for this design. I keep hoping we will find system with the features we need plus a good RI implementation.Lance Harra |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-01-08 : 04:43:27
|
| If you have to ask the question then I would go for RI implemented in the structure.A lot of systems implement RI in the application because the need to work against other databases that don't have the facility in the structure. Also they implement user configurable entities in which the RI is data dependent rather than structure dependent.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
Foreign Keys, a necessity??