Problem solve Get help with specific problems with your technologies, process and projects.

Preventing changes to the primary key

Is there any such thing as ON UPDATE RESTRICT? If not, what is the best way to not allow changes on the primary key?

Is there any such thing as ON UPDATE RESTRICT? If not, what is the best way to not allow changes on the primary key?

Yes, there is such a thing as ON UPDATE RESTRICT. However, it is a property which applies to foreign keys, not the primary key.

ON UPDATE RESTRICT means that when you are about to update the value of a primary key, the database will check to see if there are any foreign keys in other tables with this same value, and if there are, it will disallow the update to the primary key.

However, this doesn't seem to be what you are looking for. It sounds like you want the primary key to be "locked" and never updated.

Well, suppose there actually were a foreign key in some other table, and it was defined with ON UPDATE RESTRICT. Furthermore, suppose every value of your main table's primary key were in there; this would mean that no primary key value in your main table could ever be updated, because the matching foreign key is in this other table.

This other table would be very simple:

create table locked_keys
 ( thekey integer not null 
 , primary key ( thekey )
 , foreign key ( thekey ) 
         references main_table ( pkey )
         on update restrict
         on delete cascade

Now all you have to do is keep this table in sync with your main table. When you insert a row in your main table, just also insert its key into this table.

Two things of interest should be noted: firstly, while you do need to handle the inserts yourself (whether in your application or via a trigger), the ON DELETE CASCADE takes care of deletions; secondly, you do not need to insert every primary key, just the ones you want locked. Neat, eh?

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.