|
Home > Archive > MS SQL Data Warehousing > February 2006 > SQL 2005 Creating FKs
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL 2005 Creating FKs
|
|
|
| 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,
| |
| Darren Gosbell 2006-02-21, 7:23 am |
| 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.
--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell
In article <1139812318.658199.156270@f14g2000cwb.googlegroups.com>,
enzat@spotlight.com.au says...
> 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,
>
>
|
|
|
|
|