Q
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?

This was last published in September 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close