+ Reply to Thread
Results 1 to 6 of 6

Syntax ADD Foreign Key to a table (different collation on field)

  1. Syntax ADD Foreign Key to a table (different collation on field)

    How is the syntax to add a foreign key for a table on a field which has in
    the two tables different COLLATION
    TABLE1 -> FIELD1 (Latin1_General_CI_AS) type nvarchar(18)
    TABLE2 -> FIELD1 (Latin1_General_CS_AS) type nvarchar(18)
    The FIELD1 is unique in TABLE2

    ALTER TABLE [MyTable] WITH NOCHECK
    ADD CONSTRAINT FK_TABLE1_TABLE2 FOREIGN KEY(TABLE1_FIELD1)
    REFERENCES TABLE2(FIELD1)
    NOT FOR REPLICATION
    GO
    ALTER TABLE [dbo].[MRP_MyTable]
    CHECK CONSTRAINT [FK_TABLE1_TABLE2]

    Thanks


  2. Re: Syntax ADD Foreign Key to a table (different collation on field)

    On Jun 18, 1:41 pm, Xavier wrote:
    > How is the syntax to add a foreign key for a table on a field which has in
    > the two tables different COLLATION
    > TABLE1 -> FIELD1 (Latin1_General_CI_AS) type nvarchar(18)
    > TABLE2 -> FIELD1 (Latin1_General_CS_AS) type nvarchar(18)
    > The FIELD1 is unique in TABLE2
    >
    > ALTER TABLE [MyTable] WITH NOCHECK
    > ADD CONSTRAINT FK_TABLE1_TABLE2 FOREIGN KEY(TABLE1_FIELD1)
    > REFERENCES TABLE2(FIELD1)
    > NOT FOR REPLICATION
    > GO
    > ALTER TABLE [dbo].[MRP_MyTable]
    > CHECK CONSTRAINT [FK_TABLE1_TABLE2]
    >
    > Thanks


    Why would you want to do it? Column types in both tables should match
    exactly, and you don't want to mess with it.


  3. Re: Syntax ADD Foreign Key to a table (different collation on field)

    On 18 Jun, 19:41, Xavier wrote:
    > How is the syntax to add a foreign key for a table on a field which has in
    > the two tables different COLLATION
    > TABLE1 -> FIELD1 (Latin1_General_CI_AS) type nvarchar(18)
    > TABLE2 -> FIELD1 (Latin1_General_CS_AS) type nvarchar(18)
    > The FIELD1 is unique in TABLE2
    >
    > ALTER TABLE [MyTable] WITH NOCHECK
    > ADD CONSTRAINT FK_TABLE1_TABLE2 FOREIGN KEY(TABLE1_FIELD1)
    > REFERENCES TABLE2(FIELD1)
    > NOT FOR REPLICATION
    > GO
    > ALTER TABLE [dbo].[MRP_MyTable]
    > CHECK CONSTRAINT [FK_TABLE1_TABLE2]
    >
    > Thanks


    You can't do that. Do you require the constraint to be case-sensitive
    or not? Whichever it is the collations much match in both columns. I
    can't see what you'd hope to gain from having different collations.
    Could you explain what you are trying to achieve.

    --
    David Portas, SQL Server MVP

    Whenever possible please post enough code to reproduce your problem.
    Including CREATE TABLE and INSERT statements usually helps.
    State what version of SQL Server you are using and specify the content
    of any error messages.

    SQL Server Books Online:
    http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
    --


  4. Re: Syntax ADD Foreign Key to a table (different collation on fiel

    It is the problem that the TABLE2 is a replication from SAP over linked
    server. So the requirement was to change the collation of that table to
    Latin1_General_CS_AS like it is configured in SAP.

    The other tables are all Latin1_General_CI_AS. Now I have to create such a
    foreign key.
    Thanks,


  5. Re: Syntax ADD Foreign Key to a table (different collation on fiel

    > You can't do that. Do you require the constraint to be case-sensitive
    > or not? Whichever it is the collations much match in both columns. I
    > can't see what you'd hope to gain from having different collations.
    > Could you explain what you are trying to achieve.
    >

    No the constraint must not be case-sensitive.
    The data in each atble column are only in uppercase.
    But there are a lot of FK,SP, Functions in the database, and if i will
    change all tables to the same collation i have to delete this and all indexes
    and recreate all again.
    I did not know if there is a simple possibilitie to change the collation of
    all fields of the tables and tables to the default collation of the database?

    Thanks,

  6. Re: Syntax ADD Foreign Key to a table (different collation on fiel

    Xavier (Xavier@discussions.microsoft.com) writes:
    > No the constraint must not be case-sensitive.
    > The data in each atble column are only in uppercase. But there are a lot
    > of FK,SP, Functions in the database, and if i will change all tables to
    > the same collation i have to delete this and all indexes and recreate
    > all again.
    > I did not know if there is a simple possibilitie to change the collation
    > of all fields of the tables and tables to the default collation of the
    > database?


    To change the collation of all columns in a database takes some effort.
    I did it once, but I was lucky as there were no foriegn keys in that
    database. (It was a replica of a datbase from another RDBMS.)

    I would suggest that the simplest may be to script the database,
    run copy-replace in an editor to change the collation, and move data
    over.

    Then again, maybe you could get way with changing the collation of this
    column only?

    A last resort would be to do the referential check in a trigger.


    --
    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Books Online for SQL Server 2005 at
    http://www.microsoft.com/technet/pro...ads/books.mspx
    Books Online for SQL Server 2000 at
    http://www.microsoft.com/sql/prodinf...ons/books.mspx

+ Reply to Thread