Table constraints 1

[techipa]

Hello All,

I need to delete the data from tables and then insert the data from one server to another server. The FK constraints are defined on most of the tables.

Can anyone tell me how to get the list of tables in the sequence to delete and then insert the data which can avoid the FK constraint error.


Thanks
-techiPA

 

[ptheriault]

You can disable the Foreign Keys with this syntax.

[COLOR=blue]ALTER[/color] [COLOR=blue]TABLE[/color] [COLOR=blue]NOCHECK[/color] [COLOR=blue]CONSTRAINT[/color] <constraint_name>

You can use this to find FKs

[COLOR=blue]SELECT[/color] 
    FK_Table  = FK.TABLE_NAME, 
    FK_Column = CU.COLUMN_NAME, 
    PK_Table  = PK.TABLE_NAME, 
    PK_Column = PT.COLUMN_NAME, 
    Constraint_Name = C.CONSTRAINT_NAME 
[COLOR=blue]FROM[/color] 
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C 
    [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 
        [COLOR=blue]ON[/color] C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME 
    [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 
        [COLOR=blue]ON[/color] C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME 
    [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] 
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 
        [COLOR=blue]ON[/color] C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 
    [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] 
    ( 
        [COLOR=blue]SELECT[/color] 
            i1.TABLE_NAME, i2.COLUMN_NAME 
        [COLOR=blue]FROM[/color] 
            INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 
            [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] 
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 
            [COLOR=blue]ON[/color] i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME 
            [COLOR=blue]WHERE[/color] i1.CONSTRAINT_TYPE = [COLOR=red]'PRIMARY KEY'[/color] 
    ) PT 
    [COLOR=blue]ON[/color] PT.TABLE_NAME = PK.TABLE_NAME 
[COLOR=green]-- optional: 
[/color][COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] 
    1,2,3,4

If you want to limit it to specific tables, you can add any of the following immediately prior to the optional ORDER BY clause:

[COLOR=blue]WHERE[/color] PK.TABLE_NAME=[COLOR=red]'something'[/color] 
 
    [COLOR=blue]WHERE[/color] FK.TABLE_NAME=[COLOR=red]'something'[/color] 
 
    [COLOR=blue]WHERE[/color] PK.TABLE_NAME IN ([COLOR=red]'one_thing'[/color], [COLOR=red]'another'[/color]) 
 
    [COLOR=blue]WHERE[/color] FK.TABLE_NAME IN ([COLOR=red]'one_thing'[/color], [COLOR=red]'another'[/color])

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[nice95gle]

Something that Paul forgot to mention is that the ONCHECK clause should be used WITH EXTREME CAUTION!!!

I've seen alot of developers paint their selves into a corner disabling contraints.

Well Done is better than well said
- Ben Franklin

 

[nice95gle]

excuse me...NOCHECK not ONCHECK

Well Done is better than well said
- Ben Franklin

 

[ptheriault]

I completely agree with nice95gle. As a DBA I disable contraints but it's not something that I want developers doing.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[techipa]

Thanks all for the reply.

Paul - Can I run your above script (using WHERE FK.TABLE_NAME in (...) to get the list of objects in sequence to delete the data. After the data is deleted I have to insert the data back from different server in sequence.

 

[ptheriault]

Yes you can run the script with

[COLOR=blue]WHERE[/color] FK.TABLE_NAME in ([COLOR=red]'table1'[/color], [COLOR=red]'table2','etc...'[/color])

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[techipa]

Thanks Paul and Ben

 

[techipa]

Paul,

The user told to me that I do not have to delete the data from the tables. I add the data which does not exists on the on server from another server (linked server).

How do I check for for the existance of the data and then insert the data which does not exists?

Thanks in advace
-techiPA

 

[ptheriault]

Here is an example

Select a.Field_a,
       a.Field_b
From   MyTable a
       Left Join LinkTable LT
         On a.PK = LT.PK
Where  LT.PK is Null

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[techipa]

Thanks a lot Paul.

One more question I have and that is regarding the collation sequence.

The collation level of one column in a table is different than the column in another table. One of the SP has join on that column (text field). I get following error when I deploy the SP

Cannot resolve the collation conflict between "SQL_Latin1_General_CP437_BIN" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.

May I know how would I resolve this issue?

Your help is really appreciated,

Thanks,
techiPA

 

[ptheriault]

techiPA,
I hit the submit button to fast. That is just an example to find the data that doesn't exist. You will have to write an insert based on the results from that query.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

techipa, is there a reason why the collations are different. What I mean is... Would you prefer to have the collations match?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[techipa]

I figured it out Paul.

Thanks again

 

[techipa]

George,

We are migrating the databases from sql 2000 to sql 2005. The sql 2000 is case sensitive and 2005 is case insensitive.

The databases on 2000 were created as default collation and on 2005 they are case insensitive.

Looks like to resolve this issue I will have to create the temp table with the deafult colaltion on text fields and insert the data in temp table and then reference to the user table

Hope I answered your questions correctly

Thanks,
-techiPA

 

[ptheriault]

Techi,
Did you know you can chagen collation on the "fly" in your select?

[COLOR=blue]SELECT[/color] Field [COLOR=blue]collate[/color] SQL_Latin1_General_CP1_CI_AS
[COLOR=blue]FROM[/color] [COLOR=blue]table[/color]

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[techipa]

Thanks Paul.

It worked the way you suggested. I changed the collation in the query at the column join

Regards,
-techiPA

 

[ptheriault]

I'm glad I could help.


- Paul
- If at first you don't succeed, find out if the loser gets anything.