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
--

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.
>sql

Referential constraint between two tables in two databases

Is it possible to define a referential constraint between two tables in two different databases (on two servers)? Or are there beter best practices methods/products to achieve this result.

i think not possible

Thank u

Baba

Please remember to click "Mark as Answer" on this post if it helped you.

|||

You should be able to set up the reference as a linked server (http://www.databasejournal.com/features/mssql/article.php/3085211,http://msdn2.microsoft.com/en-us/library/ms188279.aspx -- this is a sql server 2005 article, but earlier versions support linked servers as well)

I doubt that you can enforce the constraint declaritively, but I am quite sure you can do it using a trigger. Let us know if you need help with the triggers.

|||

And what can be used when databases are sitted on the same server?

|||

Then it's even easier because you don't need the linked server, you can simply reference the table on the other database by indluding the DB name, eg,

database2.dbo.sometable

Or (and this is the way I do it), create a view in database1 pointing to the table in database2

use database1
go
create view TableXyz as
select * from databse2.dbo.TableXyz
go
grant insert, update, select, delete on TableXyz to ...
go

Then, from within database1, all references to TableXyz really point to database2.dbo.TableXyz

I think the declaritive RI may work in this scenario but I'm not positive, but in any case a trigger will work

sql