Primary key + delete....

General Design Question….

Suppose I have a table (Table1) with a primary key (PKId), if a user deletes a record then wants to add a record with the same PKId value there is a Primary key violation because the table has not yet Been packed.

What is the best solution to this

I have Considers the Following…

1) Indexing on PKId+!deleted() , but this would only solve the problem if he delete the record once, what happens if he deletes the second records he created The Delete will fail( ), or a primary key violation.

2) When deleting, replace the primary key with a value from a counter table. This will work but I Loose the ability to recall deleted records.

3) Just using a counter for the ID, I loose primary key enforcement altogether.

4) Use option 2 but back up the pk data to another field in the table prior to replacing the original value and deleting, I would then have to write a new Recall Function.(And when recalling come up with some way to resolve PK Conflicts)

Option 4 seem to be the best solution but, something about it rubs me the wrong way.

Is there a “Best Practices” Rule for this What are the common solutions….

Thanks
Sammy

Answer this question

Primary key + delete....

AlexLeonG
No I won't argue. How I couldn't think that since you were saying it, it should be right and everything else in this world of course is wrong.
Liberty1
We got off my Question!!!

How Do I allow a User to Deleted and then recreate a record with the same CANDIDATE key
treasacrowe

>>How Do I allow a User to Deleted and then recreate a record with the same CANDIDATE key

As far as I know the only way to do this is to create the Candidate Index as a filtered index that uses "FOR NOT DELETED()" as the filter.

Of course, that disables Rushmore Optimization for your index but you can't have it both ways. As Tamar has said, the correct solution is either not to have this as a candidate index and handle the issue in code or to re-cycle records by recalling them when a value that exists, but is flagged as deleted is entered.
xyzt82

Hi Cetin

>> A primary key is a constraint, no A primary key is a UNIQUE constraint, no A unique constraint can uniquely identify rows, no

Sorry but you are absolutely wrong in all of these points! These are only characteristics of a primary key because you are choosing to use a 'natural' key as a PK. They are not inherent to primary keys, I repeat:

A Primary Key is that column, or combination of columns, that uniquely identifies a record within a table and upon which referential integrity depends.

A PK is not a constant! There is no earthly reason why a PK value cannot be changed at will (unless, of course, it is a 'natural' key - but that is a consequence of using a natural key as a primary key, and arises only because the natural key is governed by rules that have nothing to do with its function as a primary key).

A PK is not a constraint - that is merely an implementation detail! You can still implement primary keys in databases that do not use (or even support) unique constraints

The fact that a adding a unique constraint means that the indexed column can uniquely identify rows is an accidental consequence of the database implementation, not of the data model. If the model were to be implemented in an environment that would not support Unique Constraints it would be useless!

The problem, as I keep saying, is that natural keys are bound by rules that are unnecessary to their function as a primary key.

For example, VIN are required to be globally unique. But there is no reason why multiple tables - even in the same database - should not each use identical combinations of characters and digits as primary keys (the only requirement for a PK is that it is unique within a table). The fact that you have defined a VIN as having some additional significance (i.e. it is a natural key) makes that impossible.

The issue is not really even one of debate. This is one of the rare occasions when there really is a right, and a wrong, answer in software design. The "right" answer is not to use 'natural keys' as Primary Keys.

You can argue till you are blue in the face but that will not change the fact that, from a database perspective, it is bad practice, poor design and incorrect implementation to use a natural key as a PK.
squirrel_sc
Sammy,

You are right in saying if you have a natural PK then no need for a surrogate key. If you delete a PK you shouldn't recall it at all or simply recall it when new one is the same as the one deleted.
ravenbrk
You have a couple of options here.

1) Don't make it a candidate key. In that case, you have to enforce uniqueness manually, except that, as I said in my previous message, nothing that a user can change is really unique.

2) Recycle records, so that when the user wants to add a new record with the same value as a deleted record, you recall the existing record and use it.

Tamar
harbonne nathalie
I understand what you are saying but for small reference tables I feel no need have two unique indexes, take my zip code table for example, that table has four fields ZipCode, City, State and TaxRate. Why should I need another Unique ID besides ZipCode

There is no way I am putting filled called ZipID in the Customer table then Joining on ZipId just to get the Zip Code in every place the address is displayed. I only use the ZIpCode table when adding a new customer to get the city and state, or a entering a new order to get the tax rate.

Besides even if I use candidate keys I still have the same problem… A user still cannot delete than recreate a second record with the same value in a unique Field.

Thank you,
Sammy
Erik Taylor

Hi Cetin

>>You are right in saying if you have a natural PK then no need for a surrogate key

No, I have to disagree with you on this one. Even if you have a "natural" key you should still use a surrogate primary key.

The reason is that ONLY function of a surrogate key is that it should uniquely identify a record within a table. The only rule is that it's valule must be unique within the table. As soon as you start using natural keys you introduce additional rules and constraints that (as here!) have nothing to do with the function of a primary key.

There is much more to be said, but this, in a nutshell is why you should NOT use natural keys as Primary Keys. They should (in VFP Terminology) be defined as "Candidate" keys, but not Primary.
Cagiov

Andy,

So in Sammy's case with that zip code table, what would you do

1) If you store the ZipPk in the Customer table, you must join to the zip code table just to get the zip code every time you want to display the customers address. Because city and state is dependant on zip code, City, state and zip code should ONLY exist in the Zip code table.

2) If you just store the Zip, city and state in you customer table you are again breaking relational database rules.

Yes, based on the “Laws” of Relational database design, Option 1 is Correct. But for Performance and complexity reasons
I use option 2, and only use the zipcode table when doing recorded insertions, with Zip code acting a unique identifier. And yes, I have some cases where city, state and zip, in my customer table don’t match the data in the zip code table.

That’s Ok, and it’s NOT bad design.

Dave M.
Matt Travis
A primary key is a constraint, no A primary key is a UNIQUE constraint, no A unique constraint can uniquely identify rows, no

Who guarantees an autoinc value in VFP or identity in MSSQL is unique (or another concrete surrogate like GUID) The PK constraint and nothing else. IOW you already have a constraint there.

As long as there is a natural key that should be unique (candidate as you say) adding another key to support unicity is NOT mandatory and porve to be an overkill. In fact natural keys are better than surrogates if you could have one for every table. The data length of natural key generally is the reason you might choose another surrogate key as PK but the pain introduced is more.

For example a UPN on Active Directory is a unique value and is a natural key, a Vehicle Identification Number or a NIC etc are samples of natural keys. If I have one of these as a natural key I wouldn't use another surrogate key (why should I anyway). I can't talk about zipcodes (not sure how much they can identify a location but looks like fairly well). Usernames in a given domain is just another good natural key (better yet username@domain). Why should passport.com maintain a surrogate key for its users table. Well one reason would be if uname@domain changes its related tables would still be in synch but that would be debatable if rows in "children" staying in synch with surrogate are the right ones now or not. A GIS table would use location (be it UTM,WSGS84 or one else) as a natural PK and replacing that PK with a surrogate would be nonsense etc.
safi_h80
<< take my zip code table for example, that table has four fields ZipCode, City, State and TaxRate. Why should I need another Unique ID besides ZipCode >>

Because ZipCode isn't a unique ID. The zipcode I live in can refers to three different town names in two different townships, so there are at least two different tax rates.

The problem is that almost anything we think of as a unique key in the real world can actually be duplicated, by accident if nothing else. For example, social security numbers are duplicated.

You asked for a best practice. Best practice is for every single table to have a surrogate primary key that the user has no access to.

Tamar
VBISKING
You are confusing Primary Key with Identity (or Candidate) Key.

Primary keys should be non-meaningful surrogate keys. They do not have any value or meaning to the user. They are not reused. Each record has a PK (generally from a counter). When you delete a record, that key is lost. No problem as it has no intrinsic value anyway.

See:
http://fox.wikis.com/wc.dll Wiki~CategoryKeyFields

http://fox.wikis.com/wc.dll Wiki~SurrogateKey

http://fox.wikis.com/wc.dll Wiki~PrimaryKey

Routine to generate PKs from a counter (also shows example on GUID creation)
http://www.feldstein.net/newid.asp
ccheruvu
Hi Tamar,

It is not true that it is really considered as a best practice in database world. I didn't mean VFP's world only (and it is not true in VFP only world either).

Just google "Primary Key" and you'd see excerpts like this:

"If a table does not have a "natural" unique key, ,,,"

many times and they are from documentations/forums/best practices of world's most popular backends.

Anyway internet is full of documentation about it, everybody can read themselves and decide.
Cordell Cahill
Not because Andy says it, but because this really is considered a best practice throughout the database world.

Tamar
Primary key + delete....

Answer this question