Dropping tables w/ constraints

[JeanNiBee]

Hi

Is there any way to truncate a table OR drop it if their are constraints defined in it, or, foreign key constraints 'pointing' to it?



-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[JeanNiBee]

Hi

Me again.

I wanted to explain why I want to drop a table with FK's pointing ot it.

I have a table (TABLE abc) and I backed it up using SELECT * into abc_bak FROM abc. I worked on abc, but realised I made a mistake so I want to 'drop'/'clear out' abc, and then copy/rename abc_bak to abc so all my old data (AND constraints) are valid.

But I can't delete abc, truncate abc nor drop abc.

Any clues?

Thanks.


-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[sybaseguru]

Please note that as far as I know in Sybase 12 and above (which are more ANSI compliant) you cannot use truncate table or drop table if another table has rows that reference it. You need to delete the rows from the child table, or truncate the child table, then truncate the primary table. Use the sp_helpconstraint to determine which tables reference the table you want to drop or truuncate. Use alter table to drop the constraints on the child table before issuing the drop table command on the primary table. So in summary use sp_helpconstraint <PRIMARY_TABLE> to find out which other table(s) refernces it. use &quot;alter table drop constraint&quot; on the tables listed from sp_helpconstraint to drop the constraint before dealing with the primary table.

Good luck

 

[JeanNiBee]

I was afraid of that.

See the 'xyz_bak' tabe I want to restore on top of 'xyz' has keys that will be pointed to by those child processes.

I was just hoping there was some way to 'turn off the constraint warning' drop the table/delete the info, restore my data and all data integrity will be back to normal.

Oh well I have another option to do this through UPDATE FROM and DELETE FROM. Plus a quick 'cursur based' sync.

I'll have to play around with it and I'll post my final solution here.

Thanks a ton.


-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects

 

[sybaseguru]

sorry I did not see your second message when I replied to it.

OK so you have table abc_back which has the valid data and abc needs to be corrected.

I assume that you have not modified the primay keys etc.

can you do a global update on abc like:

update abc
set col3 = b.col3,col4 = b.col4,...
from abc a, abc_backup b
where a.key1 = b.key1 and ...

This should do the trick hopefully

 

[JeanNiBee]

We're sharing a brain.

That's how I was going to address it. Plus I'll then do an insert into using an outer join to determine which records in xyz_bak do'nt exist in xyz.

Wish me luck.



-=-=-=-=-=-=-=-=-
For ease of reading, any posted CODE should be wrapped by [ignore][COLOR][/COLOR] and

[/ignore] tags.

Ex:

SELECT 1 from sysobjects