Violation of Primary Key constraint

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Violation of Primary Key constraint

Author

Topic

mavericky
Posting Yak Master

117 Posts

Posted - 2011-08-15 : 16:51:23

Hi All,
Even though I am using max(scheduleid)+1 i get violation of PK constraint.

I get this error:
Violation of PRIMARY KEY constraint 'PK_Schedule'. Cannot insert duplicate key in object 'dbo.Schedule'.

I have this query:
INSERT INTO Schedule
SELECT (SELECT MAX(ScheduleID)+1 FROM Schedule),0, T1.PName,'Active',GETDATE(),'12/12/2011', T1.HEValue1, T1.HEValue2, T1.HEValue3, T1.HEValue4, T1.HEValue5,
T1.HEValue6, T1.HEValue7, T1.HEValue8, T1.HEValue9, T1.HEValue10, T1.HEValue11, T1.HEValue12, T1.HEValue13, T1.HEValue14, T1.HEValue15, T1.HEValue16,
T1.HEValue17, T1.HEValue18, T1.HEValue19, T1.HEValue20, T1.HEValue21, T1.HEValue22, T1.HEValue23, T1.HEValue24,NULL
FROM
(
SELECT DISTINCT P.Name AS PName,W.Name,A.LoadKW,
HEValue1 = SUM (case when AP.MaxAttributeValue = 1 then AP.UtilizationFactor*A.LoadKW else null End) ,
HEValue2 = sum(case when AP.MaxAttributeValue = 2 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue3 = sum(case when AP.MaxAttributeValue = 3 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue4 = sum(case when AP.MaxAttributeValue = 4 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue5 = sum(case when AP.MaxAttributeValue = 5 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue6 = sum(case when AP.MaxAttributeValue = 6 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue7 = sum(case when AP.MaxAttributeValue = 7 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue8 = sum(case when AP.MaxAttributeValue = 8 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue9 = sum(case when AP.MaxAttributeValue = 9 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue10 = sum(case when AP.MaxAttributeValue = 10 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue11 = sum(case when AP.MaxAttributeValue = 11 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue12 = sum(case when AP.MaxAttributeValue = 12 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue13 = sum(case when AP.MaxAttributeValue = 13 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue14 = sum(case when AP.MaxAttributeValue = 14 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue15 = sum(case when AP.MaxAttributeValue = 15 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue16 = sum(case when AP.MaxAttributeValue = 16 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue17 = sum(case when AP.MaxAttributeValue = 17 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue18 = sum(case when AP.MaxAttributeValue = 18 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue19 = sum(case when AP.MaxAttributeValue = 19 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue20 = sum(case when AP.MaxAttributeValue = 20 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue21 = sum(case when AP.MaxAttributeValue = 21 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue22 = sum(case when AP.MaxAttributeValue = 22 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue23 = sum(case when AP.MaxAttributeValue = 23 then AP.UtilizationFactor*A.LoadKW ELSE NULL End) ,
HEValue24 = sum(case when AP.MaxAttributeValue = 24 then AP.UtilizationFactor*A.LoadKW ELSE NULL End)
FROM Program P
INNER JOIN WholesaleProduct W
ON P.WholesaleProductID = W.ID
INNER JOIN AssetType AT
ON AT.ID = P.AssetTypeID
INNER JOIN AssetProfile AP
ON AT.ID = AP.AssetTypeID
INNER JOIN Asset A
ON A.AssetTypeID = AT.ID
GROUP BY P.Name,W.Name,A.LoadKW) T1

Can you tell me what can be wrong??

Thanks,
Mavericky

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-08-15 : 16:59:31

Check if max schedule ID is already there in schedule table.

SELECT MAX(ScheduleID)+1 FROM Schedule

SELECT * FROM Schedule Where PrimaryKeyColumnName= paste the result of above query here

Cheers
MIK

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-15 : 17:03:04

Is ScheduleID teh first column in the Schedule table? You might want to (should) specify a column list on the insert.

mavericky
Posting Yak Master

117 Posts

Posted - 2011-08-15 : 17:06:19

I tried that. It gives no results by the method you suggested. In the table it does not have records corresponding to that

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-08-15 : 17:14:36

hmmm sorry i went wrong way .. it seems to be the issue of the "select max(...". Execute only the select statement, and you'll notice that the max value will be same for all rows.

Cheers
MIK

mavericky
Posting Yak Master

117 Posts

Posted - 2011-08-15 : 17:15:39

Yes there are 3 rows and all have the same value. That is why i get that error. I will have to figure out a way to get seperate scheduleids for each row.

Thanks,
Mavericky

mavericky
Posting Yak Master

117 Posts

Posted - 2011-08-15 : 17:25:59

But do you know how can i get a count of the rows returned by this query??

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-08-15 : 17:51:28

You can use row number function in the query of T1 table.

Cheers
MIK

mavericky
Posting Yak Master

117 Posts

Posted - 2011-08-15 : 18:04:37

So ultimately, is there any way to get the results of this query inserted into the table i want, with different scheduleIDs??

Thanks,
Mavericky

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-08-15 : 18:10:33

You could change the table for the ScheduleID is an IDENTITY. Then you wouldn't have to worry about adding to the MAX ID. But, if you can't do that you could use the ROW_NUMBER() function to add to the MAX ID.

mavericky
Posting Yak Master

117 Posts

Posted - 2011-08-16 : 00:03:14

Thanks Lamprey! My problem is solved.

Mavericky

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources