Referential Integrity practices for complex Database

I am a database Engineer for a company that has relatively large and complex
SQL Server databases.
The DB Engineers group seem to have different opinions about weather or not
we should enforce referential integrity to our database.(In fact we have
databases that are both ways). Both aspects seem to have pro's and cons.
I am trying to find out what are the common practices adopted by other
companies in similar situation. Do you enforce the referential integrity? If
so how do you deal with the heightened complexity of back end updates when
necessary? On the other hand if you did not, how do we enforce accuracy?
Your input in this is very much appreciated.Hi
Wow , I thought that DB Engineer would not has any doubt for this question.
In my company i have always been creating a primary key to every table and
foreign key to enforce the referential integrity. Benefits? your querie's
perfomance will be improved , it will prevent from unwanted deletion ,
create a diagram to see a whole picture of your database's relatioship
and......
Well , BOL has pretty good description about andvatages to enforce the
referential integrity.
"bluefish" <bluefish@.discussions.microsoft.com> wrote in message
news:72BAFD60-A99A-4CE9-83D1-C52C78BE8B38@.microsoft.com...
>I am a database Engineer for a company that has relatively large and
>complex
> SQL Server databases.
> The DB Engineers group seem to have different opinions about weather or
> not
> we should enforce referential integrity to our database.(In fact we have
> databases that are both ways). Both aspects seem to have pro's and cons.
> I am trying to find out what are the common practices adopted by other
> companies in similar situation. Do you enforce the referential integrity?
> If
> so how do you deal with the heightened complexity of back end updates when
> necessary? On the other hand if you did not, how do we enforce accuracy?
> Your input in this is very much appreciated.
>|||bluefish wrote:
> I am a database Engineer for a company that has relatively large and complex
> SQL Server databases.
> The DB Engineers group seem to have different opinions about weather or not
> we should enforce referential integrity to our database.(In fact we have
> databases that are both ways). Both aspects seem to have pro's and cons.
> I am trying to find out what are the common practices adopted by other
> companies in similar situation. Do you enforce the referential integrity? If
> so how do you deal with the heightened complexity of back end updates when
> necessary?
The purpose of referential integrity is to implement business rules and
ensure that your database accurately and consistently models the real
world. The fact that your engineers have to ask this question suggests
that either they don't understand your business or that they don't care
about data integrity.
> On the other hand if you did not, how do we enforce accuracy?
Better ask the people who designed your systems without any
integrity...
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--