Very Computer

By which way, someone can delete records in a database table with a primary
key and that record to be totally ignored?
This hasn't to do with the 'Set Deleted On' or it shouldn't have to do with
the 'Set Deleted On'.
You must always turn OFF and ON the Deleted Records in order to genarate a
new key.
And what if we delete a record and want to insert a new one with the same
'key' code? This is not allowed unless you pack the database.
Any solutions?

Thanks

Demetrios Panayotakopoulos

Go to Craig Berntson's page http://www.xmission.com/~craigb/kb/kb0006.html
and read about primary keys, and some code to generate them.

Primary keys should never be re-used.  Other data like Invoice numbers or
SSN's should be Candidate keys.   If you want to make sure there is not
another invoice or SSN with that number, do

IF SEEK(InvoiceNum, "MyTable") AND !DELETED()
    MESSAGEBOX("Sorry, this number is already in the database.")
ENDIF

but use GetKey() for the primary key.
--
Cindy Winegarden
Duke Children's Information Systems
Duke University Medical Center

|I have the following question.
|
|By which way, someone can delete records in a database table with a primary
|key and that record to be totally ignored?
|This hasn't to do with the 'Set Deleted On' or it shouldn't have to do with
|the 'Set Deleted On'.
|You must always turn OFF and ON the Deleted Records in order to genarate a
|new key.
|And what if we delete a record and want to insert a new one with the same
|'key' code? This is not allowed unless you pack the database.
|Any solutions?
|
|Thanks
|
|Demetrios Panayotakopoulos
|
|
|

Thanks for the info. I will check out.
I will try to use an example which is not my problem but may help you
understand the key problem:
Let us say that I have a database with parcels.
Each parcel is identified by an Integer number. This number is unique and I
can not replace a parcel's number with another number. With what you are
saying I must use an independent number as a key and include the parcel's
number as a candidate key. But with this way I should have one more column
in the database.
Now, if someone would delete a parcel's record (let us say that he is
allowed to) then in order to re-enter the info I should pack the table.

My feeling is that Microsoft should add an option to totaly ignore the
deleted records. I believe that they should 'get rid' of this dbaseIII
ability to recover deleted records. I do not know what happens with other
databases. I believe that they do not have such problems.

Thanks again.

Demetrios Panayotakopoulos

My way:
1. I always "set deleted on", I always disgard deleted records. Sometimes
reindex to free space.
2. I set e.g. clientCode, as primary key, the index filter expr is
"!DELETED()"
3. It works...and I am happy!

I am happy because:
1. Someone create a new clientCode, its okay,
2. Someone create a duplicate clientCode, the database engine will reject
this.
3. The client record is deleted, its gone.
4. Someone re-use the clientCode again as new record, it is accepted!
5. What else should I ask for?
    (except that I cannot create "a primary key with filter expr by
coding", I must use GUI)

Tonie.

Quote:> I have the following question.

> By which way, someone can delete records in a database table with a
primary
> key and that record to be totally ignored?
> This hasn't to do with the 'Set Deleted On' or it shouldn't have to do
with
> the 'Set Deleted On'.
> You must always turn OFF and ON the Deleted Records in order to genarate
a
> new key.
> And what if we delete a record and want to insert a new one with the same
> 'key' code? This is not allowed unless you pack the database.
> Any solutions?

> Thanks

> Demetrios Panayotakopoulos

When you place a filter on the index expression it is no
longer Rushmore optimizable.  Also, filters on indexes can
add to confusion when trying to debug routines since they are
not readily apparent.

Myron Kirby
Independent Consultant
-------------------------------------------------------------

I agree with Cindy.  The role of the primary key in a relational
data base is to provide a mechanism to uniquely identify the
record whether deleted or not.  This key can be used to
form relationships and audit trail.  As far as adding a column,
you can use an integer field which is 4 bytes and provides a rather
significant number of keys.

You might do a search for this subject through DejaNews.  There
has been some excellent prior threads that has hashed through
this in detail.

Myron Kirby
Independent Consultant
-------------------------------------------------------------