May 23, 2007 at 10:20 am
I want to truncate a bunch of tables, and bcp in replacement data from another server. The primary keys and foreign keys are causing me no end of grief and whichever order I seem to drop/add them in. Any tips?
May 23, 2007 at 10:56 am
I'd be a little concerned if I were you. The key constraints are there to preserve data integrity. I'd recommend you fix your data first so that it doesn't violate the constraints. If that can't be done, you can drop them and re-add the constraints using the WITH NOCHECK clause, but that can lead to problems later when you expect strong data integrity.
May 23, 2007 at 11:32 am
If you are doing a complete refresh of the data you need to complete the drop and add in the following order:
Starting with the lowest child table, start deleting records moving up the chain (you may need to go across children before going up to each of the parent tables)
Once all data is deleted, you start adding data in the reverse order starting with the top parent table and working your way down and across.
Hope this helps!
May 23, 2007 at 4:30 pm
Its going to take forever! There are like 25 tables in place just for me to update this 1 tables data!
Oh well.
May 23, 2007 at 7:31 pm
The quick way: script out your foreign keys, drop them, truncate the tables, reload the data, replace the foreign keys.
May 23, 2007 at 9:32 pm
It is better to bcp the data into staging tables, and then update rows that already exist that have changed, and insert the new rows.
May 24, 2007 at 8:03 am
The Embaradarro tools allow you to disable the foreign key. It'll take 5 sec's with the tool.
May 24, 2007 at 10:30 am
It may be frustrating and taking seemingly forever, but usually when the system balks at what you are trying to do, it is trying to tell you something important.
The first thing I would do is generate a diagram with those 25 tables and take the time to trace out the referential flow (a picture being worth... a lot). I can almost guarantee you will learn something you didn't know and should know.
I'm guessing, of course, as you have not provided any information as to the messages you've been getting. 😉
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
May 24, 2007 at 1:02 pm
I do this all the time to get production data into dev tables. Follow Lynn's advice and you won't be sorry.
Greg
Greg
May 24, 2007 at 2:05 pm
Hi,
You can disable first all FK (constraints) and after you populate with respect to constraints, your tables, you should enable them. Here is the code:
SET QUOTED_IDENTIFIER OFF
GO
-- Disable
EXECUTE sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT ALL"
GO
-- Here you manipulate your data
-- Enable
EXECUTE sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL"
GO
![Whistling](images/emotions/whistling.gif)
May 24, 2007 at 2:07 pm
I think it's pretty easy!
May 24, 2007 at 7:23 pm
If you are planning on doing this in a production database where there are live transactions happening, dropping or disabling the foreign key constraints is a very bad idea. You can end up causing massive damage to your data, and may not know it till much later with a huge effort to try to fix your data.
The safe way to do it is what I suggested earlier. BCP the data into staging tables, and then insert the new rows, and update the rows that have changed. You will have to apply the inserts and updates in the correct order. You can use the script on the link below to determine the correct order. Start with tables at reference level 0, and then do higher levels in order from lowest to highest.
Find Table Reference Levels:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=72957
May 25, 2007 at 2:24 am
maybe this script can help you a bit :
I used it to convert primary keys to be clustered.
So just exclude the drop/create primary key part and off you go ..
- TEST IT - TEST IT - TEST IT -
create procedure Spc_ALZDBA_PK2Clustered
@ParentTbName varchar(128)
, @ParentTbOwner varchar(128) = NULL
as
begin
SET nocount on
create table #tmpPKeys(
TABLE_QUALIFIER sysname,
TABLE_OWNER sysname not null,
TABLE_NAME sysname not null,
COLUMN_NAME sysname not null,
KEY_SEQ smallint not null,
PK_NAME sysname null )
Create index ix#tmpPKeys on #tmpPKeys (TABLE_QUALIFIER, TABLE_OWNER,TABLE_NAME, KEY_SEQ)
-- Get PK-info
insert into #tmpPKeys
exec sp_pkeys @table_name = @ParentTbName
, @table_owner = @ParentTbOwner
-- [ , [ @table_qualifier = ] 'qualifier' ] -- DBName
create table #tmpFKeys
(PKTABLE_QUALIFIER sysname not null,
PKTABLE_OWNER sysname not null,
PKTABLE_NAME sysname not null,
PKCOLUMN_NAME sysname not null,
FKTABLE_QUALIFIER sysname not null,
FKTABLE_OWNER sysname not null,
FKTABLE_NAME sysname not null,
FKCOLUMN_NAME sysname not null,
KEY_SEQ smallint not null,
UPDATE_RULE smallint not null,
DELETE_RULE smallint not null,
FK_NAME sysname not null,
PK_NAME sysname not null,
DEFERRABILITY int not null)
Create index #tmpFKeys on #tmpFKeys (FK_NAME, KEY_SEQ)
-- Get FK-info (all dependant objects)
insert into #tmpFKeys
exec sp_fkeys @pktable_name = @ParentTbName
, @pktable_owner = @ParentTbOwner
-- [ , [ @pktable_qualifier = ] 'pktable_qualifier' ]
-- { , [ @fktable_name = ] 'fktable_name' }
-- [ , [ @fktable_owner = ] 'fktable_owner' ]
-- [ , [ @fktable_qualifier = ] 'fktable_qualifier' ]
print 'Begin transaction trxAlter_' + @ParentTbName
print ' '
print '-- Drop Referencing constraints'
select 'Alter Table [' + FKTABLE_OWNER + '].[' + FKTABLE_NAME + '] drop constraint [' + FK_NAME + ']' + char(13) + 'GO '
from #tmpFKeys
where Key_SEQ = 1
order by FKTABLE_OWNER, FKTABLE_NAME, FK_NAME
print '-- drop PK-constraint'
select 'Alter Table [' + TABLE_OWNER + '].[' + TABLE_NAME + '] drop constraint [' + PK_NAME + ']' + char(13) + 'GO '
from #tmpPKeys
where Key_SEQ = 1
order by TABLE_OWNER, TABLE_NAME
print '-- Create PK_Constraint'
select 'Alter Table [' + P1.TABLE_OWNER + '].[' + P1.TABLE_NAME + '] WITH NOCHECK add constraint ' + P1.PK_NAME + ' PRIMARY KEY CLUSTERED ( '+
P1.COLUMN_NAME
+ case when P2.COLUMN_NAME is null then '' else ', ' + P2.COLUMN_NAME end
+ case when P3.COLUMN_NAME is null then '' else ', ' + P3.COLUMN_NAME end
+ case when P4.COLUMN_NAME is null then '' else ', ' + P4.COLUMN_NAME end
+ case when P5.COLUMN_NAME is null then '' else ', ' + P5.COLUMN_NAME end
+ case when P6.COLUMN_NAME is null then '' else ', ' + P6.COLUMN_NAME end
+ case when P7.COLUMN_NAME is null then '' else ', ' + P7.COLUMN_NAME end
+ case when P8.COLUMN_NAME is null then '' else ', ' + P8.COLUMN_NAME end
+ case when P9.COLUMN_NAME is null then '' else ', ' + P9.COLUMN_NAME end
+ case when P10.COLUMN_NAME is null then '' else ', ' + P10.COLUMN_NAME end
+ case when P11.COLUMN_NAME is null then '' else ', ' + P11.COLUMN_NAME end
+ case when P12.COLUMN_NAME is null then '' else ', ' + P12.COLUMN_NAME end
+ case when P13.COLUMN_NAME is null then '' else ', ' + P13.COLUMN_NAME end
+ case when P14.COLUMN_NAME is null then '' else ', ' + P14.COLUMN_NAME end
+ case when P15.COLUMN_NAME is null then '' else ', ' + P15.COLUMN_NAME end
+ case when P16.COLUMN_NAME is null then '' else ', ' + P16.COLUMN_NAME end
+ case when P17.COLUMN_NAME is null then '' else ', ' + P17.COLUMN_NAME end
+ case when P18.COLUMN_NAME is null then '' else ', ' + P18.COLUMN_NAME end
+ ' ) ' + char(13) + 'GO '
from #tmpPKeys P1
left join #tmpPKeys P2
on P1.TABLE_OWNER = P2.TABLE_OWNER and P1.TABLE_NAME = P2.TABLE_NAME and P1.KEY_SEQ = 1 and P2.KEY_SEQ = 2
left join #tmpPKeys P3
on P1.TABLE_OWNER = P3.TABLE_OWNER and P1.TABLE_NAME = P3.TABLE_NAME and P1.KEY_SEQ = 1 and P3.KEY_SEQ = 3
left join #tmpPKeys P4
on P1.TABLE_OWNER = P4.TABLE_OWNER and P1.TABLE_NAME = P4.TABLE_NAME and P1.KEY_SEQ = 1 and P4.KEY_SEQ = 4
left join #tmpPKeys P5
on P1.TABLE_OWNER = P5.TABLE_OWNER and P1.TABLE_NAME = P5.TABLE_NAME and P1.KEY_SEQ = 1 and P5.KEY_SEQ = 5
left join #tmpPKeys P6
on P1.TABLE_OWNER = P6.TABLE_OWNER and P1.TABLE_NAME = P6.TABLE_NAME and P1.KEY_SEQ = 1 and P6.KEY_SEQ = 6
left join #tmpPKeys P7
on P1.TABLE_OWNER = P7.TABLE_OWNER and P1.TABLE_NAME = P7.TABLE_NAME and P1.KEY_SEQ = 1 and P7.KEY_SEQ = 7
left join #tmpPKeys P8
on P1.TABLE_OWNER = P8.TABLE_OWNER and P1.TABLE_NAME = P8.TABLE_NAME and P1.KEY_SEQ = 1 and P8.KEY_SEQ = 8
left join #tmpPKeys P9
on P1.TABLE_OWNER = P9.TABLE_OWNER and P1.TABLE_NAME = P9.TABLE_NAME and P1.KEY_SEQ = 1 and P9.KEY_SEQ = 9
left join #tmpPKeys P10
on P1.TABLE_OWNER = P10.TABLE_OWNER and P1.TABLE_NAME = P10.TABLE_NAME and P1.KEY_SEQ = 1 and P10.KEY_SEQ = 10
left join #tmpPKeys P11
on P1.TABLE_OWNER = P11.TABLE_OWNER and P1.TABLE_NAME = P11.TABLE_NAME and P1.KEY_SEQ = 1 and P11.KEY_SEQ = 11
left join #tmpPKeys P12
on P1.TABLE_OWNER = P12.TABLE_OWNER and P1.TABLE_NAME = P12.TABLE_NAME and P1.KEY_SEQ = 1 and P12.KEY_SEQ = 12
left join #tmpPKeys P13
on P1.TABLE_OWNER = P13.TABLE_OWNER and P1.TABLE_NAME = P13.TABLE_NAME and P1.KEY_SEQ = 1 and P13.KEY_SEQ = 13
left join #tmpPKeys P14
on P1.TABLE_OWNER = P14.TABLE_OWNER and P1.TABLE_NAME = P14.TABLE_NAME and P1.KEY_SEQ = 1 and P14.KEY_SEQ = 14
left join #tmpPKeys P15
on P1.TABLE_OWNER = P15.TABLE_OWNER and P1.TABLE_NAME = P15.TABLE_NAME and P1.KEY_SEQ = 1 and P15.KEY_SEQ = 15
left join #tmpPKeys P16
on P1.TABLE_OWNER = P16.TABLE_OWNER and P1.TABLE_NAME = P16.TABLE_NAME and P1.KEY_SEQ = 1 and P16.KEY_SEQ = 16
left join #tmpPKeys P17
on P1.TABLE_OWNER = P17.TABLE_OWNER and P1.TABLE_NAME = P17.TABLE_NAME and P1.KEY_SEQ = 1 and P17.KEY_SEQ = 17
left join #tmpPKeys P18
on P1.TABLE_OWNER = P18.TABLE_OWNER and P1.TABLE_NAME = P18.TABLE_NAME and P1.KEY_SEQ = 1 and P18.KEY_SEQ = 18
where P1.KEY_SEQ = 1
order by P1.TABLE_OWNER, P1.TABLE_NAME
print '-- Create FK-Constraints'
print '-- keep FK column-ordinal equal to PK column-ordinal'
select 'Alter Table [' + FK1.FKTABLE_OWNER + '].[' + FK1.FKTABLE_NAME + '] WITH NOCHECK add constraint [' + FK1.FK_NAME + '] FOREIGN KEY ( '+
FK1.FKCOLUMN_NAME
+ case when FK2.FKCOLUMN_NAME is null then '' else ', ' + FK2.FKCOLUMN_NAME end
+ case when FK3.FKCOLUMN_NAME is null then '' else ', ' + FK3.FKCOLUMN_NAME end
+ case when FK4.FKCOLUMN_NAME is null then '' else ', ' + FK4.FKCOLUMN_NAME end
+ case when FK5.FKCOLUMN_NAME is null then '' else ', ' + FK5.FKCOLUMN_NAME end
+ case when FK6.FKCOLUMN_NAME is null then '' else ', ' + FK6.FKCOLUMN_NAME end
+ case when FK7.FKCOLUMN_NAME is null then '' else ', ' + FK7.FKCOLUMN_NAME end
+ case when FK8.FKCOLUMN_NAME is null then '' else ', ' + FK8.FKCOLUMN_NAME end
+ case when FK9.FKCOLUMN_NAME is null then '' else ', ' + FK9.FKCOLUMN_NAME end
+ case when FK10.FKCOLUMN_NAME is null then '' else ', ' + FK10.FKCOLUMN_NAME end
+ case when FK11.FKCOLUMN_NAME is null then '' else ', ' + FK11.FKCOLUMN_NAME end
+ case when FK12.FKCOLUMN_NAME is null then '' else ', ' + FK12.FKCOLUMN_NAME end
+ case when FK13.FKCOLUMN_NAME is null then '' else ', ' + FK13.FKCOLUMN_NAME end
+ case when FK14.FKCOLUMN_NAME is null then '' else ', ' + FK14.FKCOLUMN_NAME end
+ case when FK15.FKCOLUMN_NAME is null then '' else ', ' + FK15.FKCOLUMN_NAME end
+ case when FK16.FKCOLUMN_NAME is null then '' else ', ' + FK16.FKCOLUMN_NAME end
+ case when FK17.FKCOLUMN_NAME is null then '' else ', ' + FK17.FKCOLUMN_NAME end
+ case when FK18.FKCOLUMN_NAME is null then '' else ', ' + FK18.FKCOLUMN_NAME end
+ ' ) REFERENCES [' + FK1.PKTABLE_OWNER + '].[' + FK1.PKTABLE_NAME + '] ( ' +
FK1.PKCOLUMN_NAME
+ case when FK2.PKCOLUMN_NAME is null then '' else ', ' + FK2.PKCOLUMN_NAME end
+ case when FK3.PKCOLUMN_NAME is null then '' else ', ' + FK3.PKCOLUMN_NAME end
+ case when FK4.PKCOLUMN_NAME is null then '' else ', ' + FK4.PKCOLUMN_NAME end
+ case when FK5.PKCOLUMN_NAME is null then '' else ', ' + FK5.PKCOLUMN_NAME end
+ case when FK6.PKCOLUMN_NAME is null then '' else ', ' + FK6.PKCOLUMN_NAME end
+ case when FK7.PKCOLUMN_NAME is null then '' else ', ' + FK7.PKCOLUMN_NAME end
+ case when FK8.PKCOLUMN_NAME is null then '' else ', ' + FK8.PKCOLUMN_NAME end
+ case when FK9.PKCOLUMN_NAME is null then '' else ', ' + FK9.PKCOLUMN_NAME end
+ case when FK10.PKCOLUMN_NAME is null then '' else ', ' + FK10.PKCOLUMN_NAME end
+ case when FK11.PKCOLUMN_NAME is null then '' else ', ' + FK11.PKCOLUMN_NAME end
+ case when FK12.PKCOLUMN_NAME is null then '' else ', ' + FK12.PKCOLUMN_NAME end
+ case when FK13.PKCOLUMN_NAME is null then '' else ', ' + FK13.PKCOLUMN_NAME end
+ case when FK14.PKCOLUMN_NAME is null then '' else ', ' + FK14.PKCOLUMN_NAME end
+ case when FK15.PKCOLUMN_NAME is null then '' else ', ' + FK15.PKCOLUMN_NAME end
+ case when FK16.PKCOLUMN_NAME is null then '' else ', ' + FK16.PKCOLUMN_NAME end
+ case when FK17.PKCOLUMN_NAME is null then '' else ', ' + FK17.PKCOLUMN_NAME end
+ case when FK18.PKCOLUMN_NAME is null then '' else ', ' + FK18.PKCOLUMN_NAME end
+ ' ) ' + char(13) + 'GO '
from #tmpFKeys FK1
left join #tmpFKeys FK2
on FK1.FK_NAME = FK2.FK_NAME and FK1.KEY_SEQ = 1 and FK2.KEY_SEQ = 2
left join #tmpFKeys FK3
on FK1.FK_NAME = FK3.FK_NAME and FK1.KEY_SEQ = 1 and FK3.KEY_SEQ = 3
left join #tmpFKeys FK4
on FK1.FK_NAME = FK4.FK_NAME and FK1.KEY_SEQ = 1 and FK4.KEY_SEQ = 4
left join #tmpFKeys FK5
on FK1.FK_NAME = FK5.FK_NAME and FK1.KEY_SEQ = 1 and FK5.KEY_SEQ = 5
left join #tmpFKeys FK6
on FK1.FK_NAME = FK6.FK_NAME and FK1.KEY_SEQ = 1 and FK6.KEY_SEQ = 6
left join #tmpFKeys FK7
on FK1.FK_NAME = FK7.FK_NAME and FK1.KEY_SEQ = 1 and FK7.KEY_SEQ = 7
left join #tmpFKeys FK8
on FK1.FK_NAME = FK8.FK_NAME and FK1.KEY_SEQ = 1 and FK8.KEY_SEQ = 8
left join #tmpFKeys FK9
on FK1.FK_NAME = FK9.FK_NAME and FK1.KEY_SEQ = 1 and FK9.KEY_SEQ = 9
left join #tmpFKeys FK10
on FK1.FK_NAME = FK10.FK_NAME and FK1.KEY_SEQ = 1 and FK10.KEY_SEQ = 10
left join #tmpFKeys FK11
on FK1.FK_NAME = FK11.FK_NAME and FK1.KEY_SEQ = 1 and FK11.KEY_SEQ = 11
left join #tmpFKeys FK12
on FK1.FK_NAME = FK12.FK_NAME and FK1.KEY_SEQ = 1 and FK12.KEY_SEQ = 12
left join #tmpFKeys FK13
on FK1.FK_NAME = FK13.FK_NAME and FK1.KEY_SEQ = 1 and FK13.KEY_SEQ = 13
left join #tmpFKeys FK14
on FK1.FK_NAME = FK14.FK_NAME and FK1.KEY_SEQ = 1 and FK14.KEY_SEQ = 14
left join #tmpFKeys FK15
on FK1.FK_NAME = FK15.FK_NAME and FK1.KEY_SEQ = 1 and FK15.KEY_SEQ = 15
left join #tmpFKeys FK16
on FK1.FK_NAME = FK16.FK_NAME and FK1.KEY_SEQ = 1 and FK16.KEY_SEQ = 16
left join #tmpFKeys FK17
on FK1.FK_NAME = FK17.FK_NAME and FK1.KEY_SEQ = 1 and FK17.KEY_SEQ = 17
left join #tmpFKeys FK18
on FK1.FK_NAME = FK18.FK_NAME and FK1.KEY_SEQ = 1 and FK18.KEY_SEQ = 18
where FK1.KEY_SEQ = 1
order by FK1.FKTABLE_OWNER, FK1.FKTABLE_NAME, FK1.FK_NAME
print '-- Create FK-Indexes'
print '-- keep FK column-ordinal / order equal to PK column-ordinal / order (asc/desc)'
select 'Create index [XFK_' + FK1.FK_NAME + '] on [' + FK1.FKTABLE_OWNER + '].[' + FK1.FKTABLE_NAME + '] ( '+
FK1.FKCOLUMN_NAME
+ case when FK2.FKCOLUMN_NAME is null then '' else ', ' + FK2.FKCOLUMN_NAME end
+ case when FK3.FKCOLUMN_NAME is null then '' else ', ' + FK3.FKCOLUMN_NAME end
+ case when FK4.FKCOLUMN_NAME is null then '' else ', ' + FK4.FKCOLUMN_NAME end
+ case when FK5.FKCOLUMN_NAME is null then '' else ', ' + FK5.FKCOLUMN_NAME end
+ case when FK6.FKCOLUMN_NAME is null then '' else ', ' + FK6.FKCOLUMN_NAME end
+ case when FK7.FKCOLUMN_NAME is null then '' else ', ' + FK7.FKCOLUMN_NAME end
+ case when FK8.FKCOLUMN_NAME is null then '' else ', ' + FK8.FKCOLUMN_NAME end
+ case when FK9.FKCOLUMN_NAME is null then '' else ', ' + FK9.FKCOLUMN_NAME end
+ case when FK10.FKCOLUMN_NAME is null then '' else ', ' + FK10.FKCOLUMN_NAME end
+ case when FK11.FKCOLUMN_NAME is null then '' else ', ' + FK11.FKCOLUMN_NAME end
+ case when FK12.FKCOLUMN_NAME is null then '' else ', ' + FK12.FKCOLUMN_NAME end
+ case when FK13.FKCOLUMN_NAME is null then '' else ', ' + FK13.FKCOLUMN_NAME end
+ case when FK14.FKCOLUMN_NAME is null then '' else ', ' + FK14.FKCOLUMN_NAME end
+ case when FK15.FKCOLUMN_NAME is null then '' else ', ' + FK15.FKCOLUMN_NAME end
+ case when FK16.FKCOLUMN_NAME is null then '' else ', ' + FK16.FKCOLUMN_NAME end
+ case when FK17.FKCOLUMN_NAME is null then '' else ', ' + FK17.FKCOLUMN_NAME end
+ case when FK18.FKCOLUMN_NAME is null then '' else ', ' + FK18.FKCOLUMN_NAME end
+ ' ) ' + char(13) + 'GO '
from #tmpFKeys FK1
left join #tmpFKeys FK2
on FK1.FK_NAME = FK2.FK_NAME and FK1.KEY_SEQ = 1 and FK2.KEY_SEQ = 2
left join #tmpFKeys FK3
on FK1.FK_NAME = FK3.FK_NAME and FK1.KEY_SEQ = 1 and FK3.KEY_SEQ = 3
left join #tmpFKeys FK4
on FK1.FK_NAME = FK4.FK_NAME and FK1.KEY_SEQ = 1 and FK4.KEY_SEQ = 4
left join #tmpFKeys FK5
on FK1.FK_NAME = FK5.FK_NAME and FK1.KEY_SEQ = 1 and FK5.KEY_SEQ = 5
left join #tmpFKeys FK6
on FK1.FK_NAME = FK6.FK_NAME and FK1.KEY_SEQ = 1 and FK6.KEY_SEQ = 6
left join #tmpFKeys FK7
on FK1.FK_NAME = FK7.FK_NAME and FK1.KEY_SEQ = 1 and FK7.KEY_SEQ = 7
left join #tmpFKeys FK8
on FK1.FK_NAME = FK8.FK_NAME and FK1.KEY_SEQ = 1 and FK8.KEY_SEQ = 8
left join #tmpFKeys FK9
on FK1.FK_NAME = FK9.FK_NAME and FK1.KEY_SEQ = 1 and FK9.KEY_SEQ = 9
left join #tmpFKeys FK10
on FK1.FK_NAME = FK10.FK_NAME and FK1.KEY_SEQ = 1 and FK10.KEY_SEQ = 10
left join #tmpFKeys FK11
on FK1.FK_NAME = FK11.FK_NAME and FK1.KEY_SEQ = 1 and FK11.KEY_SEQ = 11
left join #tmpFKeys FK12
on FK1.FK_NAME = FK12.FK_NAME and FK1.KEY_SEQ = 1 and FK12.KEY_SEQ = 12
left join #tmpFKeys FK13
on FK1.FK_NAME = FK13.FK_NAME and FK1.KEY_SEQ = 1 and FK13.KEY_SEQ = 13
left join #tmpFKeys FK14
on FK1.FK_NAME = FK14.FK_NAME and FK1.KEY_SEQ = 1 and FK14.KEY_SEQ = 14
left join #tmpFKeys FK15
on FK1.FK_NAME = FK15.FK_NAME and FK1.KEY_SEQ = 1 and FK15.KEY_SEQ = 15
left join #tmpFKeys FK16
on FK1.FK_NAME = FK16.FK_NAME and FK1.KEY_SEQ = 1 and FK16.KEY_SEQ = 16
left join #tmpFKeys FK17
on FK1.FK_NAME = FK17.FK_NAME and FK1.KEY_SEQ = 1 and FK17.KEY_SEQ = 17
left join #tmpFKeys FK18
on FK1.FK_NAME = FK18.FK_NAME and FK1.KEY_SEQ = 1 and FK18.KEY_SEQ = 18
where FK1.KEY_SEQ = 1
order by FK1.FKTABLE_OWNER, FK1.FKTABLE_NAME, FK1.FK_NAME
print 'Commit transaction trxAlter_' + @ParentTbName
-- cleanup
drop table #tmpPKeys
drop table #tmpFKeys
--
end
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 25, 2007 at 7:48 am
PKs/FKs giving you grief? Seems like a fair trade-off for being able to have faith in one's data...and what's a database without meaning?
As another poster put it above, there's a reason those constraints are there.
If your DB contains information on your rock collection, by all means, drop away. If it's corporate financial information, well, you couldn't pay me to disable them.
May 25, 2007 at 9:09 am
The data, keys and what not are all fine on the data entry db. This is the reporting db where the data will not change. Just so you know.
Thanks for all the help.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply