When I tried to create a composite primary key in a table an error is occurring ?
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
March 26th, 2015 3:34pm

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.

Free Windows Admin Tool Kit Click here and download it now
March 26th, 2015 6:11pm

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.

March 29th, 2015 1:39am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics