CODEWITHSUNDEEP
sqloracle-databaseplsql
unknownsatan
unknownsatan

Reputation: 731

Changing Primary Key but getting error

I am trying to change the Primary of a table using this,

ACCEPT p_oldserial PROMPT 'Enter Old Serial: ';
ACCEPT p_newserial PROMPT 'Enter New Serial: ';
INSERT INTO car
(SELECT serial, cname, make, model, year, color, trim, enginetype, purchinv, purchdate, purchfrom, purchcost, freight, totalcost, listprice
   FROM customer
  WHERE serial='&p_oldserial');
UPDATE saleinv
   SET serial='&p_newserial'
 WHERE serial='&p_oldserial';
UPDATE serivceinv, saleinv;
DELETE FROM customer
 WHERE serial='&p_oldserial';
COMMIT;

But I am getting ORA-02291. Am I making any mistake in my script?

Upvotes: 0

Views: 493

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Your question was "Am I making any mistake in my script?".

The answer is "Yes, very definitely", but perhaps not in the way you think. It's not a syntax error, which would be simple to fix. This is a logic and understanding issue which is much, much more difficult to deal with.

A primary key has three basic attributes:

  1. It is UNIQUE.
  2. It is NOT NULL.
  3. It is UNCHANGING.

You're trying to violate rule #3. Now, all other things being equal, Oracle (and every other relational database I've ever worked with) will let you violate rule #3 at will. However, in this case there is another table out there with a FOREIGN KEY constraint which references the primary key you're trying to change. After the UPDATE statement has executed Oracle then checks all the foreign key constraints which might be affected, and finds that one of them has been violated because the key value on the 'child' table no longer exists in the 'parent' table (the SALEINV table in this case).

A common reaction at this point is to say, "Stupid database! Relational databases are obviously broken! I am so much smarter!! Foreign key constraints are bad and must be eliminated!!!". And so the developer marches angrily off the cliff, deleting foreign keys like mad and wondering why there's all this wind, anyways. I mean, what's with...<SPLAT!>?

In fact the foreign key constraint has just saved you from doing something really, REALLY bad - so bad, in fact, that it looks like doing something really, really good but no, honestly, take it from me, changing a primary key is a really SERIOUSLY BAD THING to do. (Don't ask me how I know... :-). That primary key is the one thing that lets you know that the row in question is always and forever the same thing, and if you change it you are saying "This thing that before was ABC is now XYZ, and everywhere it's referred to must now refer to is as XYZ". Think of it as "Invasion Of The Body Snatchers" in your database - once you start doing this you can no longer believe that anything will remain as it was, you can't safely relate any rows to any others as the keys may change, you can't enforce relational integrity, and...well, your life will get very interesting very quickly as you start dealing with all the "lost records", "missing parent records", "missing data", and related issues.

Final analysis - figure out some other way to do what you want. Besides which, are you REALLY suggesting that you're changing the VIN on a vehicle? That's another Bad Thing to do, and I suspect there are any number of law enforcement agencies who would be happy to explain why... :-)

Share and enjoy.

Upvotes: 2

BillThor
BillThor

Reputation: 7576

First question would be which statement is failing. Execute the script one statement at a time. I would expect it is the DELETE statement. 

DELETE FROM customer
WHERE serial='&p_oldserial';

If so you haven't moved all records that reference this row using a foreign key. Should the second update read?

UPDATE serviceinv
SET    serial='&p_newserial'
WHERE  serial='&p_oldserial';

Are there other tables that need similar updates?

As others have noted, primary keys are generally considered immutable. As such this should be a one off change. If this is likely to be a frequent change, then you may need a surrogate key. This would reduce the update to:

UPDATE car
SET    serial='&p_newserial'
WHERE  serial='&p_oldserial';

If this is likely to be a frequent change and you can't add a surrogate key, look into the CASCADE options.

Upvotes: 1

Related Questions