Results 1 to 10 of 10
  1. #1
    Join Date
    Jan 2011
    Posts
    2

    Unanswered: Error when 2 same key names on 2 same column names in different tabels

    Problem:

    We are using 2 times the same primary and the same column name, ie.

    - ORD_KEY on ord_nr in table A

    - ORD_Key on ord_nr in table B

    This results that the first table is working fine but when accessing the second table it gives a system error. Then we tried to put the tables in 2 different databases but this gives the same result.

    Anyone knows what the problem can be?

    Otherwise we have to change our source code as we are migrating from Topspeed to MSSQL. In Topspeed a prefix before the column name resolves this problem. Is there a possibility to use prefixes or namespaces in MSSQL?

    We are using Server 2008 Enterprise x64.

  2. #2
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by Stoffelke View Post
    Problem:

    We are using 2 times the same primary and the same column name, ie.

    - ORD_KEY on ord_nr in table A

    - ORD_Key on ord_nr in table B

    This results that the first table is working fine but when accessing the second table it gives a system error. Then we tried to put the tables in 2 different databases but this gives the same result.

    Anyone knows what the problem can be?

    Otherwise we have to change our source code as we are migrating from Topspeed to MSSQL. In Topspeed a prefix before the column name resolves this problem. Is there a possibility to use prefixes or namespaces in MSSQL?

    We are using Server 2008 Enterprise x64.
    How'd you manage that trick?

    USE [SomeDatabase]
    GO
    /****** Object: Table [dbo].[Table_1] Script Date: 01/14/2011 11:00:03 ******/
    CREATE TABLE [dbo].[Table_1](
    [ord_nbr] [int] NOT NULL,
    [description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CONSTRAINT [ORD_KEY] PRIMARY KEY CLUSTERED
    (
    [ord_nbr] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object: Table [dbo].[Table_2] Script Date: 01/14/2011 11:00:03 ******/
    CREATE TABLE [dbo].[Table_2](
    [ord_nbr] [int] NOT NULL,
    [description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CONSTRAINT [ORD_KEY] PRIMARY KEY CLUSTERED
    (
    [ord_nbr] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF

    When I execute the code above this is the response (error) message from SQL Server:

    Msg 2714, Level 16, State 4, Line 1
    There is already an object named 'ORD_KEY' in the database.
    Msg 1750, Level 16, State 0, Line 1
    Could not create constraint. See previous errors.

    -- I would suggest a unique name for the primary key.

  3. #3
    Join Date
    Jan 2011
    Posts
    2
    Thanks for the response.
    Yes we will have to use different keys.
    We are currently using Topspeed and here we did it with a prefix to the table. This is not possible in MSSQL.
    Problem is that we will have to review all our code for it.

  4. #4
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    I perfer to use names for keys that are easily identified with the table. PK_SALES_ORD_ID , PK_ORDERS_ORD_ID, etc. The tables SALES and ORDERS could have a primary key which is ORD_ID. For clarity though, SALES_ORD_ID and ORDER_ORD_ID could be used, and so the index names would be PK_SALES_ORD_ID and PK_ORDERS_ORD_ID.

  5. #5
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by Stoffelke View Post
    Thanks for the response.
    Yes we will have to use different keys.
    We are currently using Topspeed and here we did it with a prefix to the table. This is not possible in MSSQL.
    Problem is that we will have to review all our code for it.
    I think there is some confusion here. It is possible to have the same name for a primary key in multiple tables. People do this all the time with ID. The index name for the primary key must be different.

    Run the code snippet below on a test box. The primary key is ORD_NBR for both tables, but the name is different for each table (constraint).

    GO
    /****** Object: Table [dbo].[Table_1] Script Date: 01/14/2011 11:00:03 ******/
    CREATE TABLE [dbo].[Table_1](
    [ord_nbr] [int] NOT NULL,
    [description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CONSTRAINT [T1_ORD_NBR] PRIMARY KEY CLUSTERED
    (
    [ord_nbr] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]

    GO
    SET ANSI_PADDING OFF
    GO
    /****** Object: Table [dbo].[Table_2] Script Date: 01/14/2011 11:00:03 ******/
    CREATE TABLE [dbo].[Table_2](
    [ord_nbr] [int] NOT NULL,
    [description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    CONSTRAINT [T2_ORD_NBR] PRIMARY KEY CLUSTERED
    (
    [ord_nbr] ASC
    ) ON [PRIMARY]
    ) ON [PRIMARY]
    Last edited by corncrowe; 01-14-11 at 10:07.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Stoffelke View Post
    Problem:

    We are using 2 times the same primary and the same column name, ie.

    - ORD_KEY on ord_nr in table A

    - ORD_Key on ord_nr in table B

    This results that the first table is working fine but when accessing the second table it gives a system error. Then we tried to put the tables in 2 different databases but this gives the same result.

    Anyone knows what the problem can be?
    There is nothing in SQL Server that will prevent you from doing this. I suspect the issue is in your middle-tier, possibly relating to the use of an ORM.
    One possible work-around would be to use an alias for one of the column names, or to reference the columns by their fully qualified names: TableA.ORD_KEY ans TableB.ORD_Key.
    But I suspect you are going to run into the same issue in other places, as it is customary to repeat the use of column names in different tables when referring to the same concept.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by blindman View Post
    There is nothing in SQL Server that will prevent you from doing this. I suspect the issue is in your middle-tier, possibly relating to the use of an ORM.
    One possible work-around would be to use an alias for one of the column names, or to reference the columns by their fully qualified names: TableA.ORD_KEY ans TableB.ORD_Key.
    But I suspect you are going to run into the same issue in other places, as it is customary to repeat the use of column names in different tables when referring to the same concept.
    I don't believe you can create a constraint with the same name in SQL.

    - ORD_KEY on ord_nr in table A

    - ORD_Key on ord_nr in table B

    My example above throws an error. The problem isn't with primary key name, but with the contraint name.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, I did not understand that the OP's issue was with the constraint name.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by blindman View Post
    Yes, I did not understand that the OP's issue was with the constraint name.
    No Problemo.....

    P.S. Getting closer to 100 posts. Maybe next year?

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •