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
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.