Referential Integrity

[Paulo Weiler]

I've got a CONTACTS table that is used to store different
types of contacts and therefore relates to many different
tables (CUSTOMERS, SITES,etc). I can EITHER keep a
relationship with each of this tables, creating columns at
the CONTACTS table to store each foreign key, OR I can
keep two columns, REFERENCED_TABLE and
REFERENCED_TABLE_ID, valid for any related table and then
manage the referential integrity using triggers. What
solution do you think is the best ?
Many thanks, Paulo Weiler

[Jacco Schalkwijk]

Always use Foreign Keys for referential integrity, unless you really need
the added functionality that triggers can provide. Triggers are a lot slower
and a more difficult to maintain.

You can create a script (if you have a good naming convention) to generate
all the Foreign keys:

SELECT 'IF EXISTS(SELECT * FROM information_schema.constraints WHERE
constraint_name = FK_' + c.table_name + '_contacts)'
+ CHAR (13) +
'ALTER TABLE ' + c.table_name + ' DROP CONSTRAINT FK_' + c.table_name +
'_contacts'
+ CHAR (13) +
'ALTER TABLE ' + c.table_name + ' ADD CONSTRAINT FK_' + c.table_name +
'_contacts'
+ CHAR (13) +
'FOREIGN KEY (contact_id) REFERENCES contacts (contact_id)'
+ CHAR (13) +
'GO'
+ CHAR (13)
FROM information_schema.columns c
INNER JOIN information_schema.tables t
ON c.table_name = t.table_name
WHERE c.column_name = 'contact_id'
AND c.table_name <> 'contacts'
AND t.table_type = 'base table'

Run this in Query Analyzer with 'results in Text'.

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


"Paulo Weiler" <europaulo02@yahoo.com> wrote in message
news:0a3a01c34793$69421950$a101280a@phx.gbl...

> I've got a CONTACTS table that is used to store different
> types of contacts and therefore relates to many different
> tables (CUSTOMERS, SITES,etc). I can EITHER keep a
> relationship with each of this tables, creating columns at
> the CONTACTS table to store each foreign key, OR I can
> keep two columns, REFERENCED_TABLE and
> REFERENCED_TABLE_ID, valid for any related table and then
> manage the referential integrity using triggers. What
> solution do you think is the best ?
> Many thanks, Paulo Weiler

[weblover]

Hi

how to set the referential integrity between 2 tables using enterprise manager (microsoft SQL SERVER).. i tried and the tab doesn't allow me to choose. pls.help.

thanks
sree

[The ScareCrow]

You can do this in 2 ways.

1. Create a diagram for the db
Expand the db and double click the diagrams option

2. Open a table in design mode, right click on a column and select the option
required from the menu

Ken