SQL 2005 Creating FKs

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

enzat - 13 Feb 2006 06:31 GMT

Hi,

I am in the middle of creating a data warehouse and wanted to add some
FK constraints.
However, I've found that when creating them via the 'relationships'
tab, something strange occurs!
I have a FACT table which references PKs in my Dimension tables, hence
my FKs.
Though, when I create the FKs in my FACT table, an identical entry is
automatically created in the dimension. My issue lies in truncating
the DIM (after truncating the FACT of course), even though my Dimension

isn't referenced by anything else.

Should I be created the FKs differently?
Can anyone suggest how to overcome this problem.

Thanks in advance,

Reply to this Message

Darren Gosbell - 21 Feb 2006 12:42 GMT

Truncate table is fast because it is minimally logged, but as a result
you cannot run it on any tables that are referenced by a foreign key
constraint. The truncate table statement merely checks for the existance
of constraints, it does not scan the table to see if clearing it would
actually violate the constraint or not.

You either have to drop the constraint prior to truncating, or use the
delete statement to clear the table.

Signature

Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

> Hi,
>
[quoted text clipped - 14 lines]
>
> Thanks in advance,

Reply to this Message