I have a record in my database.
The index key is Patient_ID

I want to modify the record because the patients name has changed or
their ID has changed.

The simple way to do this would be to delete the record and add a new
one.

But what happens in the case of a relational database were deleting
the record would delete relations in other tables?

What SQL statement would do this properly?

UPDATE table-1 SET set-list [WHERE predicate]

SpreadTooThin wrote:
No, this is not the simplest way. For one thing it would require two
calls to the database.

The relations will be broken.

That's what it's there for.

And be careful - ideally, the PK should ideally be unrelated to the rest
of the data. A less than ideal second choice would be to have the PK be
something unique to the row. In either case, the PK should not change;
if it can, ensure you're using an engine which maintains foreign key
integrity like InnoDB.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

SpreadTooThin:
That would be the 'same' as kkilling the person, and giving birth to a
new one, who you are going to dress up like the old one.

If names and IDs can change, they should be normal attributes of the
Patient entity. Patient should then be identified by something else.
Perhaps just an autoincrement value.

So, perhaps you could first convert the table, so the ID field gets
copied into a new field 'notReallyAnID". Then change that field and
treat it as the display-value for ID.

You'd be in trouble, unless you can restore the relationships after
deleting and inserting (which is what you don't want to do).


Now, I'm just wondering: how can a patient's ID get changed? Wasn't the
person the one he claimed to be, or has he undergone a severe Identity
Transplantation?


--
Erick

SpreadTooThin wrote:

That's why its common practice to not delete historical data..but flag
it as 'obsolete'

That may not be the best way with a good database design, but it avoids
breaking enquiries..

I assume typo's are possible and that someone may have entered a
patient into the database with the wrong ID by accident and want to
change it at a later date.

Erick T. Barkhuis wrote:
Marriage, divorce, adoption, voluntary legal name change. The problem
is that the OP used the patients name as the primary key for the table.

The problem is that you had a poor design in the beginning. A primary
key should be something that identifies a particular record FOREVER. It
should not be something that can change, like a name, address, email or
anything like that. The name should have been one of the field values
that is in the record identified by that primary key. Usually, a simple
auto-increment key is sufficient.

What you have to do now is quite messy.
1 - Create a new field (primary key) in the patient table that
auto-increments.
2 - Go to all tables that use the patientID as a primary key and update
those tables such that it now contains the value of the new primary key
you created.
3 - Go back into all your queries in your code and change the references
to patientID over to the new primary key.
4 - Look in your code where you want the actual name of the patient and
get that from a query based upon the new primary key.

Have fun :-)

--
Shelly

I don't think the ID is the name:

different names.
No, it's quite apparent that the ID and the name are different fields.

Now, why would the *ID* change? I'll presume no one is stupid enough
to use the number off of an insurance card as an ID, since that changes
every few years typically, and it covers more than one person. It would
make sense to have the database *assign* new IDs.

Gordon Burditt wrote:
You are correct! I missed that he said the name OR ID would change.
Then his job is _much_ easier. He can simply duplicate the patientID
field and call the latest one "displayPatientID" or something like that.
Then the old patientID remains unchanged as a primary key and all he
need do is get the data from the "displayPatientID" for display
purposes. Changes of "ID" would only appear in "displayPatientID".

He could also add searches based up "displayPatientID" if he wants to,
besides "patientID".

:
Oh, I thought he used some ID number and wants to change that.
Name changes happen all the time...


Well, yes. But STT made a valid point: how would you correct a typo,
then?

I agree. But if you have something that is a unique identifier, the
temptation to use it as a key is obvious.

Hmm....as I posted before, I wouldn't do that. Too much hassle and
risk. Instead, I would keep the old PKs and add the IDfield as an
attribute.

Yeah, right.

--
Erick

Erick T. Barkhuis wrote:
That is why nearly all the tables I create that use a primary key have
it called something like "ID" and are auto-incremented. It bears no
relation to anything else, be they unique or not. So, while social
security numbers, for example, may be unique, they would only be an
attribute field in any table I create. I can always search on that
field if I have to. IOW, my primary keys are for referential integrity.
I can always create indeces and uniqueness constraints on other fields
or combinations of fields.