how do i update primary key

hi, i got this error

Table 'product' does not have the identity property. Cannot perform SET operation.

=======================================================
CREATE TABLE [dbo].[product] (
      [prodID] [int] NOT NULL ,
      [prodCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [prodName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [prodDesc] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
      [pfID] [int] NULL ,
      [prodLicName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [prodLicEncKey] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [supported] [smallint] NULL ,
      [licVersion] [smallint] NULL ,
      [prodAltCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO

an not to sure about this,

i am trying to update

SET IDENTITY_INSERT tablename ON

and then you can use explicitly insert the data.

you are trying to update the ProdID with the current value, so no change for ProdID valeue

UPDATE product SET
       prodID       =@prodID, ------------------------------------------ check here
      prodCode     =@prodCode,
       prodAltCode   =@prodAltCode  
WHERE        prodID = @prodID  ------------------------------------- and here
GO

Hi you can't update the Identity Column.
as you code prodID is not a Identity column.

tell me prodID  have primary key??

if yes, you can't update the same prodID which is already in the table and you can use any other id which is not exists in the table and can update with that.

post your error which you are geeting.

Please check the relations for the product table...

if you have referenced tables and the specific data you are trying to modify is present in child tables, you will not be able to update. You may have to delete those child rows before update the master.

I suggest you establish the CASCADE UPDATE and relationships then change the data type. to autoincrement with a reseed.  Never tried it but it should work...

guys

The primary key in my database is used as a product id, so it is not set to autoincrement.

the values of prodid could be 10, 20, 45 but they are 1,2,3 as normal

so ved17nov, i know i can't update that primary key and i am not geting any errors even with breakpoiont on.

how do i fix it,

else how do i use this cascade update

thanks

how do i do this?
I suggest you establish the CASCADE UPDATE and relationships then change the data type. to autoincrement with a reseed.  Never tried it but it should work...

hi lowfat

do u mean somithing like this
ALTER PROCEDURE SPR_UPDATE_PRODUCTS
      (
       @prodID         int,
       @numUniqueDevices int,
       @matchBaseLicOnly  int,
       @prodCode       varchar(10),
       @prodName       varchar(30),
       @prodDesc       varchar(200),
       @pfID           int,
       @prodLicName    varchar(50),
       @prodLicEncKey  varchar(20),
       @supported      smallint,
       @licVersion     smallint,
       @prodAltCode    varchar(10)
     )
AS
UPDATE product SET
   
      prodID       =@prodID,
      prodCode     =@prodCode,
      prodName     =@prodName,      
      prodDesc     =@prodDesc,      
      pfID         =@pfID,          
      prodLicName   =@prodLicName,    
      prodLicEncKey =@prodLicEncKey,
      supported     =@supported,    
      licVersion    =@licVersion,    
      prodAltCode   =@prodAltCode,
set prodid=@newprodid
WHERE
      productid=@oldProductid

i've read throuygh the question and cannot make out what your actual problem is ...>>


whilst i can update other fields in the table, i cannot update the prodid

thanks

ok

ALTER PROCEDURE SPR_UPDATE_PRODUCTS
      (
       @prodID         int,
       @numUniqueDevices int,
       @matchBaseLicOnly  int,
       @prodCode       varchar(10),
       @prodName       varchar(30),
       @prodDesc       varchar(200),
       @pfID           int,
       @prodLicName    varchar(50),
       @prodLicEncKey  varchar(20),
       @supported      smallint,
       @licVersion     smallint,
       @prodAltCode    varchar(10),
       @newprodid      int,
       @oldprodid      int
     )
AS
UPDATE product SET
   
      prodID       =@prodID,
      prodCode     =@prodCode,
      prodName     =@prodName,      
      prodDesc     =@prodDesc,      
      pfID         =@pfID,          
      prodLicName   =@prodLicName,    
      prodLicEncKey =@prodLicEncKey,
      supported     =@supported,    
      licVersion    =@licVersion,    
      prodAltCode   =@prodAltCode,
       prodID=@newprodID
WHERE
      prodID=@oldProdID

i will test this

i now have this,


   'Updates users
    Public Shared Function UpdateProduct(ByVal productProfile As ProductProfile) As Boolean
        Dim DBConnection As SqlConnection = Connection()
        Dim ProdID As SqlParameter
        DBConnection.Open()
        Try
            Dim cmdproduct As SqlCommand = New SqlCommand("SPR_UPDATE_PRODUCTS", DBConnection)
            cmdproduct.CommandType = CommandType.StoredProcedure
            cmdproduct.Parameters.Add("@prodID", productProfile.prodID)
            cmdproduct.Parameters.Add("@newprodID", productProfile.newprodID)
            cmdproduct.Parameters.Add("@prodCode", productProfile.prodCode)
            cmdproduct.Parameters.Add("@prodName", productProfile.prodName)
            cmdproduct.Parameters.Add("@prodDesc", productProfile.prodDesc)
            cmdproduct.Parameters.Add("@pfID", productProfile.pfID)
            cmdproduct.Parameters.Add("@prodLicName", productProfile.prodLicName)
            cmdproduct.Parameters.Add("@prodLicEncKey", productProfile.prodLicEncKey)
            cmdproduct.Parameters.Add("@supported", productProfile.supported)
            cmdproduct.Parameters.Add("@licVersion", productProfile.licVersion)
            cmdproduct.Parameters.Add("@prodAltCode", productProfile.prodAltCode)
            'updates data in the licPolicy table
            cmdproduct.Parameters.Add("@numUniqueDevices", productProfile.numUniqueDevices)
            cmdproduct.Parameters.Add("@matchBaseLicOnly", productProfile.matchBaseLicOnly)

            Dim iCount As Integer
            iCount = cmdproduct.ExecuteNonQuery()
            If iCount > 0 Then
                Return True
            Else
                Return False
            End If
        Catch ex As Exception
            System.Diagnostics.Debug.WriteLine(ex.ToString)
        Finally
            DBConnection.Close()
        End Try
    End Function
 ============================and
ALTER PROCEDURE SPR_UPDATE_PRODUCTS
      (
       @prodID            int,
       @numUniqueDevices  int,
       @matchBaseLicOnly  int,
       @prodCode          varchar(10),
       @prodName          varchar(30),
       @prodDesc          varchar(200),
       @pfID              int,
       @prodLicName       varchar(50),
       @prodLicEncKey     varchar(20),
       @supported         smallint,
       @licVersion        smallint,
       @prodAltCode      varchar(10),
       @newprodID        int,
       @oldprodID        int
     )
AS
UPDATE product SET
   
      prodID       =@prodID,
      prodCode     =@prodCode,
      prodName     =@prodName,      
      prodDesc     =@prodDesc,      
      pfID         =@pfID,          
      prodLicName   =@prodLicName,    
      prodLicEncKey =@prodLicEncKey,
      supported     =@supported,    
      licVersion    =@licVersion,    
      prodAltCode   =@prodAltCode,
      prodID=@newprodID
WHERE
      prodID=@oldProdID

on breakpoint
cmdproduct.Parameters.Add("@newprodID", productProfile.newprodID) value is
"nothing" instead of 111, for instance.

UPDATE product SET
   
 --     prodID       =@prodID,
      prodCode     =@prodCode,
      prodName     =@prodName,      
      prodDesc     =@prodDesc,      
      pfID         =@pfID,          
      prodLicName   =@prodLicName,    
      prodLicEncKey =@prodLicEncKey,
      supported     =@supported,    
      licVersion    =@licVersion,    
      prodAltCode   =@prodAltCode,
      prodID=@newprodID
WHERE
      prodID=@oldProdID



but you have to initialise the oldprodid and newprodid parameters correctly in your code as well.....

By definition, you *cannot* update the primary key in SQL Server.  SQL will simply not allow it.

<<By definition, you *cannot* update the primary key in SQL Server.  SQL will simply not allow it.>>
 I would add to that a primary key that requires *frequent* updating certainly is *not* a primary key (speaking of the fundamental definition of primary key).

Theory is good, but real life must take priority, so I think SQL should allow a "primary key" to be changed, even if MS wanted to require a special role/permission to do it.

<<Theory is good, but real life must take priority, so I think SQL should allow a "primary key" to be changed, even if MS wanted to require a special role/permission to do it>>

I respectfully disagree on the perspective (always a pleasure Scott !!! ;)).  I do not believe there should any priority of any kind: both are equally important.  Poor implementation with good theory supporting is bad.  implementation with no theory at all is even worse: the chances for building a sound db schema without *some* undistorted knowledge of fundamentals is close to zero.    A true primary key should be *stable* in *any* case else chances it is everything but a primary key.  If a primary key is updated and changed too frequently, it simply means that the designer did not do a good job into selecting it among candidate keys.  Or it may mean that the system was not built seriously.  

I am convinced that's it's because too many practitioners choose to ignore fundamental design rules in RM that they create real life problems on hard to maintain systems (NULLS, redundancy, dupplicates, etc...).  After that, too many practitionners keep running behind problems they created themselves.  

At *best*, choosing to ignore fundamentals RM will make you an average practitioners but as DATE stated...*Intellectual agility and experience may replace fundamental knowledge in a limited manner*...

At *worst*, it pours in tons of XML (hierarchical) in db's and brings us back 40 years in the past (old IMS,MVS systems) when people had to write entire programs to do one single update...

Theory is not here to make people life harder and imposes in no way to make mutually exclusive choices between real life and theory.  It is perfectly valid and possible to design better systems by a better study of data fundamentals even on good ol' SQL Systems.  I think it is the duty and role of all experts in this board to help people not only with hacks and daily tricks but also by encouraging them into finding out more about the fundamentals of database management.   I unfortunately do not believe we have as db professionals the luxury of choosing between theory and implementation (or real life)..

Guess that was my rant against ignorance of data fundamentals....(or was it that stupid developper who brought down an entire db down this afternoon in one single select because of unormalized schema)...

thanks you all.

i shall close down this ques

<<i shall close down this ques>>
You are welcome.  Hope the various comments helped...

yes they did.

thanks all

Scott:
>>By definition, you *cannot* update the primary key in SQL Server.  SQL will simply not allow it.

i think nowhere this is defined. i just tested and sqlserver does allow changing PKey value. can you provide pointers from where did you get  this.
yes i agree that its a bad practice to change primary key values.

and this is the explanation of Primary key from BOL

A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. You can create a primary key by defining a PRIMARY KEY constraint when you create or modify a table.

A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints guarantee unique data, they are frequently defined on an identity column.

When you specify a PRIMARY KEY constraint for a table, the SQL Server 2005 Database Engine enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. Therefore, the primary keys that are chosen must follow the rules for creating unique indexes.

If a PRIMARY KEY constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the PRIMARY KEY constraint definition must be unique.

As shown in the following illustration, the ProductID and VendorID columns in the Purchasing.ProductVendor table form a composite PRIMARY KEY constraint for this table. This makes sure that that the combination of ProductID and VendorID is unique.


When you work with joins, PRIMARY KEY constraints relate one table to another. For example, to determine which vendors supply which products, you can use a three-way join between the Purchasing.Vendor table, the Production.Product table, and the Purchasing.ProductVendor table. Because ProductVendor contains both the ProductID and VendorID columns, both the Product table and the Vendor table can be accessed by their relationship to ProductVendor.

Scott
I agree with your sentiments

but SQL Server does allow you to change Primary Key values ,,,

but changing a primary key value is always symptomatic of some underlying design problem...

(just think of the havoc is causes for any triggers on the table...
  there is nothing which tells you which before (deleted)  matches  which after (inserted)  

<<but changing a primary key value is always symptomatic of some underlying design problem...>>
Agreed.  A primary key is a concept that is independent from a specific technology implementation.

It is safe to say that a primary key that requires frequent updating is no primary key at all.  

Regards...

Hmm, interesting.  Sorry about the false info.  I know I had trouble with this in the past.