Primary keys and their purpose

  • Thread starter Thread starter mklapp
  • Start date Start date
M

mklapp

Hello,

I am used to the type of primary key which is
maintained in such a way so that newly added records are
placed in the correct sequence based on the primary key.

An ADO.NET dataset I am using has a Primary key and is
used for sequential 'browsing'. When I add a new row to
it, the new keys are added to the end.

Does the primary key enforce or enable an access
sequence?

After adding a row is there a 'refresh', short of
making a trip to the DB, that restores the correct
sequence of keys?

mklapp
 
Hi,

mklapp said:
Hello,

I am used to the type of primary key which is
maintained in such a way so that newly added records are
placed in the correct sequence based on the primary key.

An ADO.NET dataset I am using has a Primary key and is
used for sequential 'browsing'. When I add a new row to
it, the new keys are added to the end.

Does the primary key enforce or enable an access
sequence?
Click to expand...

It enforces uniqueness.
After adding a row is there a 'refresh', short of
making a trip to the DB, that restores the correct
sequence of keys?
Click to expand...

If the pk is an identity one then you'll need to store new records to
database to get actual key values.
(there is DataAdapter.Update method)
 
The key is not an identity field. It is an identifier.
The dataset itself is used to provide a 'browsing'
capability (First, last, previous and next). I also
allow the user to go into the 'middle' of the table to
retrieve identifiers non-serially (random access). After
a 'random selection' I use a binary search on the table
to get the row index so the user can continue (First,
last, previous and next).

With a binary search, the 'key' needs to be sorted. I
thought adding new rows to the table, would insert them
in their 'proper' sequence. I find it simply appends
them to the end of the table.

I have considered a dataview, but that will mean
rewriting a significant part of the App (time constraints
play a role here).

If the Primary key merely prevents duplicate entries,
I could gen a sorted row array, but would really like to
put that sorted array back into the dataset's table.

I am not finding a lot of hope in the docs and may be
forced to execute a round trip to the server everytime a
new record is added (not desirable).

A Primary key should really do what Primary keys have
done for me for 30 years or so, maintain a sorted access
path. That or call it something else.

mklapp
 
Without getting into a lecture on good old E.F's theory, a Primary Key's
main purpose is the enforce integrity by quaranteeing uniqueness. You can
use them any way you want, but you certainly can't fault MS for implementing
PK's in the way they were designed to be used.
I'm not sure what your problem is though...what mehtod are you using to Find
your rows and move your position? Even if the value is added at the end, if
it's a key field, can't you use .Select against it and find your position?
If that absolutely won't work, why not use an Array and just sort it, or
write an implementation for IComparable? These are easy enough but I think
calling the .Select Method should do it for you. Another approach is to use
a Hash Table where the position of the new row maps back to where it is
actually located...this is basically the way most indexes work anyway - this
is another way you'd always know where a given row was located.
 
I have considered a dataview, but that will mean
rewriting a significant part of the App (time constraints
play a role here).
Click to expand...

Ouch. It is the best solution.
If the Primary key merely prevents duplicate entries,
I could gen a sorted row array, but would really like to
put that sorted array back into the dataset's table.
Click to expand...

It won't help you here. DataTable is always unsorted and new records are
always appended at the end.
I am not finding a lot of hope in the docs and may be
forced to execute a round trip to the server everytime a
new record is added (not desirable).

A Primary key should really do what Primary keys have
done for me for 30 years or so, maintain a sorted access
path. That or call it something else.
Click to expand...

Through DataView would work just fine.
 
I do use .Find. The problem comes after .Find has been
used.

The user locates a record randomly, then wants to see the
one after (or before) that record. I adapted to that by
developing a common binary search to return the row
number for the randomly selected record regardless of the
dataset used, but, as you know, a binary search only
works when the 'search keys' are in a known and
consistent sequence.

I have used PKs with IMS, several ISAMs(embedded and
separate) and a few RDBMS over the years and I believe (
although I could be wrong ) they all had the added
feature of ordering access to the data. Of course, one
also could get the rows in entry sequence.

Regardless, my personal challenge is now to add that
functionality to this App's data. Quickly. I have a few
ideas, some have been mentioned here.

mklapp
 
Hi mklapp,

Primary key is used as an unique identity in a database table. It can be a
field of many types. Numeric, string, GUID are all accepted types. So we
might not sort the records with the primary key. In ADO.net, the rows can
be sorted on a primary key or non-primary key field. So use a DataView is a
good idea.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Hello,

So, If I set :

myDS = ws.PopDataset()
myDS.tables("MYTABLE").DefaultView.sort = "StudentID"

thisID = myDS.tables("MYTABLE").DefaultView.Item(0)
("StudentId")

This is the way to use the view?

mklapp
 
Hi mklapp,

Yes, this is the correct way to manipulate the default view of a DataTable.
Besides, you can also create multiple views for a single table.

Dim dv1 As New DataView(myDS.tables("MYTABLE"))
dv1.Sort = "StudentID"
Dim dv2 As New DataView(myDS.tables("MYTABLE"))
dv2.Sort = "StudentName"

This example creates two DataViews from the DataTable named "MYTABLE",
which are sorted on "StudentID" and "StudentName" fields.

The DataView can also be filtered with certain criterias. For more
information about DataView, please refer to the following link about
DataView and DataView constructors.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatadataviewclasstopic.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatadataviewclassctortopic.asp

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Actually, I should clarify myself here. Primary keys or for that matter any key, has only provided sorted access to the file in an ISAM type of file system. I know that RDBMS ordering really only comes through the ORDER Clause of the SQL Statement. Soon, this will be second nature and before long I will undoubtedly complain that IMS/ISAM/VSAM doesn't work like it should because it does not behave like ADO.Net

----- mklapp wrote: ----

The key is not an identity field. It is an identifier.
The dataset itself is used to provide a 'browsing'
capability (First, last, previous and next). I also
allow the user to go into the 'middle' of the table to
retrieve identifiers non-serially (random access). After
a 'random selection' I use a binary search on the table
to get the row index so the user can continue (First,
last, previous and next).

With a binary search, the 'key' needs to be sorted. I
thought adding new rows to the table, would insert them
in their 'proper' sequence. I find it simply appends
them to the end of the table

I have considered a dataview, but that will mean
rewriting a significant part of the App (time constraints
play a role here)

If the Primary key merely prevents duplicate entries,
I could gen a sorted row array, but would really like to
put that sorted array back into the dataset's table

I am not finding a lot of hope in the docs and may be
forced to execute a round trip to the server everytime a
new record is added (not desirable)

A Primary key should really do what Primary keys have
done for me for 30 years or so, maintain a sorted access
path. That or call it something else

mklap
 
Back
Top