locked
When I tried to create a composite primary key in a table an error is occurring ? RRS feed

  • Question

  • Hi,

        ALTER TABLE WorkOrder
        ADD CONSTRAINT Wo_system PRIMARY KEY (WorkOrderNumber,AssetCode)

       when i tried to execute the query above the following error is occurring :

    Msg 1779, Level 16, State 0, Line 1
    Table 'WorkOrder' already has a primary key defined on it.
    Msg 1750, Level 16, State 0, Line 1
    Could not create constraint. See previous errors.
       

    I had deleted the primary key WorkOrderNumber but its still creating the same error

    can some one give me a solution for this
    Thursday, March 26, 2015 3:34 PM

Answers

  • Verify again there is no primary key on workorder table using below query:

    SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_NAME = 'WorkOrder' AND CONSTRAINT_TYPE = 'PRIMARY KEY'

    If this returns primary key drop it and then create the new composite primary key.

    Thursday, March 26, 2015 6:11 PM
  • I cannot reproduce the error

    CREATE TABLE dbo.Table_1
    (
    col int NOT NULL
    )  ON [PRIMARY]
    GO
    ALTER TABLE dbo.Table_1 ADD CONSTRAINT
    PK_Table_1 PRIMARY KEY CLUSTERED 
    (
    col
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO


    ALTER TABLE dbo.Table_1 ADD CONSTRAINT
    PK_Table_1 PRIMARY KEY CLUSTERED 
    (
    col
    )
    --Msg 1779, Level 16, State 0, Line 1
    --Table 'Table_1' already has a primary key defined on it.
    --Msg 1750, Level 16, State 0, Line 1
    --Could not create constraint. See previous errors.

    ALTER TABLE dbo.Table_1 DROP CONSTRAINT
    PK_Table_1 

    GO
    ALTER TABLE dbo.Table_1 ADD CONSTRAINT
    PK_Table_1 PRIMARY KEY CLUSTERED 
    (
    col
    )

    ----Command(s) completed successfully.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, March 29, 2015 5:35 AM

All replies

  • Verify again there is no primary key on workorder table using below query:

    SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
    WHERE TABLE_NAME = 'WorkOrder' AND CONSTRAINT_TYPE = 'PRIMARY KEY'

    If this returns primary key drop it and then create the new composite primary key.

    Thursday, March 26, 2015 6:11 PM
  • I cannot reproduce the error

    CREATE TABLE dbo.Table_1
    (
    col int NOT NULL
    )  ON [PRIMARY]
    GO
    ALTER TABLE dbo.Table_1 ADD CONSTRAINT
    PK_Table_1 PRIMARY KEY CLUSTERED 
    (
    col
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    GO


    ALTER TABLE dbo.Table_1 ADD CONSTRAINT
    PK_Table_1 PRIMARY KEY CLUSTERED 
    (
    col
    )
    --Msg 1779, Level 16, State 0, Line 1
    --Table 'Table_1' already has a primary key defined on it.
    --Msg 1750, Level 16, State 0, Line 1
    --Could not create constraint. See previous errors.

    ALTER TABLE dbo.Table_1 DROP CONSTRAINT
    PK_Table_1 

    GO
    ALTER TABLE dbo.Table_1 ADD CONSTRAINT
    PK_Table_1 PRIMARY KEY CLUSTERED 
    (
    col
    )

    ----Command(s) completed successfully.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, March 29, 2015 5:35 AM