foreign keys and constraints to have ot not

[raghu3]

I am migrating an access db to SQLSERVER with enhancements: .net UI, brand new design, more functionality, more data etc.
I have the dbschema/layout from the users. They have given foreigh keys to every table. The current access will be used only to import in existing data. the data is not very large.
Should I have the every foreign key as refrential constraint or maintain the integrity via apps.
what are the pros and cons on having these setting at the db level ?
I am building this and will be maintaining it: would like to have an optimum solution, as some times the best is not feasable.

 

[vongrunt]

> what are the pros and cons on having these setting at the db level ?

Pros:

- Integrity rules are "closest to source" and absolute. No matter how data comes into database (app, bulk insert/DTS, whatever), rules always apply. With integrity rules established in app there are always chances a) someone will import data by not using an app, b) app programmers will make some "leaks" in integrity rules. Actually b) is fairly common in java/dotnet world if you ask me...
- During early stages of development RI rules in DB will immediately indicate something is wrong. With integrity maintained outside DB mistakes may remain unnoticed for months

Cons:

- if rules are too complex (too many business exceptions) total RI at DB level is not possible or feasible
- too "smart" rules (cascaded RI, complex triggers etc) may actually slow down server a bit.

Personally I prefer doing fundamental rules (FKeys, NULL/NOT NULL, important CHECKs) in database. Everything else depends on circumstances.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]