Primary Keys

  • Thread starter Thread starter Jim Heavey
  • Start date Start date
J

Jim Heavey

If you have an application which is using the DataAdapter.Update() method
and you application allows the user to alter a "primary key" value, is
ado.net generating an Insert and delete behind the scenes?

I know that I had to do inserts and deletes when modifying a primary key on
DB2 platform, so I presume it is the same in SQL Server. Is that correct?

Thanks in advance for your assistance!
 
Hi Jim,

Jim Heavey said:
If you have an application which is using the DataAdapter.Update() method
and you application allows the user to alter a "primary key" value, is
ado.net generating an Insert and delete behind the scenes?
Click to expand...

Generating when?
It is not.
You'll have to alter the commands or create yours.
I know that I had to do inserts and deletes when modifying a primary key on
DB2 platform, so I presume it is the same in SQL Server. Is that correct?
Click to expand...

You mean delete and insert?
An update will do just fine I guess (it won't work with autoincrement
identity columns, though).
 
When you use the IDE to build your dataAdapter, it generates the
Insert/Update/Delete and select commands.

So the commands are generated, at least in this case, and my question was
does this now allow me to update the primary key?
 
Thanks, but I am still unclear what is really happening...

If a have a table with a single column which has a string field called
"color". If I have a single row in this table and the value for "color" is
"Blue" and this value is changed to "Orange" - in situations where the
commands are generated for your by the IDE, when you call the
DataAdapter.Update() method, what is happening? Does the system generate a
"delete" for the color "Blue" and then an "Insert" for "Orange". Is this
what will happen?
 
Hello Jim

If you are allowing the changing on a non-identity key value in a
SQL-Server database then SQL-Server will delete the existing row and replace
it with a new row containing the new key values. ADO.Net does not care
either way. In the case of trying to change an Identity column SQL-Server
will generate an error.

I am doing my best not to get up on a soapbox here, but the concept of
changing a Primary Key value whether a simple key or a compund key flies in
the face of relational database design. Your key values should be designed
so that they define the existance of a given row of data. The row of
data's existance should be dependant upon the Key, not the other way around.
If you are identifying a person (for example) in a database you would not
make the color shirt they are wearing as part of the key. Because the
minute they change their shirt they can no longer be found in the database
based upon their shirt color. Keys should uniquely identify a row of data
without being dependant upon the values of data that can change. If the Key
value can change for a given row of data, then I suggest you rethink the
design of your data store.


--
Ibrahim Malluf
http://www.malluf.com
==============================================
MCS Data Services Code Generator
http://64.78.34.175/mcsnet/DSCG/Announcement.aspx
==============================================
Pocket PC Return On Investment Calculator
Free Download http://64.78.34.175/mcsnet/kwickKalk1.aspx
 
Back
Top