Collations in a FK relationship

Published 16 March 11 09:48 PM | MartinBell

Anyone who studied the Powershell script in my previous post may have seen the following code to remove foreign keys that reference an index that has a column with the database collation I am changing:

        $list = @{}
            foreach ($tbl in $db.Tables) {
                foreach ($fk in $tbl.ForeignKeys) {
                    foreach($fkcol in $fk.Columns) {
#                   
# Columns in a Foreign Key must have the same collation as those they are referencing
#
                        $col = $tbl.Columns[$fkcol.Name];
                        if ( $col.Collation -eq $db.Collation ) {
                            if(-not $list.Contains($fk)) {
                                $list.Add($fk, $tbl.Name);
                                $Scripter.Script($fk);
                           } 
                        }
                    }
                }
            } ;

If I don’t drop the foreign key I won’t be able drop the index itself.

As the comment shows, I use the fact that the collations of the columns in the foreign key must be the same as the ones in the index. To prove this this is the case I wrote the following SQL script:

CREATE TABLE t1 ( col1 varchar(10) COLLATE Latin1_General_CS_AI NOT NULL,
      col2 varchar(10) COLLATe Latin1_General_100_CS_AI NOT NULL,
      CONSTRAINT pk_T1 PRIMARY KEY (col1) )
GO

ALTER
TABLE t1 ADD CONSTRAINT FK_T1_Col2_Col1 FOREIGN KEY (col2) REFERENCES t1 ( col1 )
GO

Trying to create the Foreign Key will give the following error:

Msg 1757, Level 16, State 0, Line 1
Column 't1.col1' is not of same collation as referencing column 't1.col2' in foreign key 'FK_T1_Col2_Col1'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Filed under: ,

Comments

# Martin Bell (martinbell) posts Collations in a FK relationship | sqlmashup said on March 18, 2011 09:09 PM:

Pingback from  Martin Bell (martinbell) posts Collations in a FK relationship | sqlmashup

This Blog

SQL Blogs

Syndication