Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to make a column in a relation unmodifyable for consistency reasons.

The backstory is that I have a n:n relationship where the "connecting" relation has additional values. I don't want that anyone is able to change the IDs of the relationship partners.

What I have come up with so far:

Creating a trigger that checks if the NEW value is the same as the OLD value. But I don't know how to deny the UPDATE action if the condition proves as TRUE.

Here is my trigger so far:

CREATE TRIGGER deny_nton_change BEFORE UPDATE ON Schueler_in_Klasse
    IF NEW.Schueler_ID != OLD.Schueler_ID OR NEW.Klasse_ID != OLD.Klasse_ID THEN

    END IF;
share|improve this question
up vote 10 down vote accepted

Rather than abort the update, just override it?

    SET NEW.Schueler_ID = OLD.Schueler_ID;
    SET NEW.Klasse_ID = OLD.Klasse_ID;
share|improve this answer
+1 because this is much simpler than my approach. I am going to look over the MySQL Stored Procedure Language Book ( and see it suggests this in the same chapter. – RolandoMySQLDBA Nov 24 '11 at 18:22
BTW congrats on 20000 rep and being the first DBA.SE trusted user. – RolandoMySQLDBA Nov 28 '11 at 17:10
I think I'll use this then. Thanks! – sinni800 Nov 29 '11 at 11:07

A fairly nice and clean way to abort and return a readable error message is by using a signal:

SIGNAL sqlstate '45000' SET message_text = 'Schueler_ID and Klasse_ID may not be altered!';

Do make sure your MySQL version supports this, it was introduced in MySQL 5.5. In earlier versions you need hacks like inserting a non existant field into a non existant table.

As gbn mentions, overwriting the new values with the old ones is a good option too.

share|improve this answer
As suggested by @RolandoMySQLDBA, this seems to not work... It says there is an error in my SQL syntax – sinni800 Nov 29 '11 at 11:06
like I said, check the mysql version. It works on 5.5 fine. link To signal a generic SQLSTATE value, use '45000', which means “unhandled user-defined exception.” – Jannes Dec 1 '11 at 11:37

I actually learned a back-alley approach to aborting triggers. It is very crude, but works because SIGNAL processing is not implemented in MySQL's Stored Procedure Language. It can be emulated at best.

Chapter 11, Pages 254-256 of the book MySQL Stored Procedure Programming under the subheading 'Validating Data with Triggers' suggests making a SELECT of a character string into a dummy integer (in terms of syntax, it is correct, but dies on execution [NO PUN INTENDED]):

CREATE TRIGGER deny_nton_change BEFORE UPDATE ON Schueler_in_Klasse 
    DECLARE dummy INT; 
    IF NEW.Schueler_ID != OLD.Schueler_ID OR NEW.Klasse_ID != OLD.Klasse_ID THEN 
        SELECT 'Cannot Carry Out This Trigger Any Further'
        INTO dummy FROM table_you_know_does_not_exist WHERE 1=1 LIMIT 1;
    END IF; 
share|improve this answer
Wow, that is really... Back-alley. Nice one... – sinni800 Nov 24 '11 at 18:20

It is actually possible to do this neatly without using triggers if you are using InnoDB.

Create another table with just one column. That column should have a foreign key (hence the innodb requirement in this solution) that points to the immutable column of the original table in question.

Put a restriction like "ON UPDATE RESTRICT".

In summary:

CREATE TABLE original (
     immutable_column ...
     INDEX index1(immutable_column)

CREATE TABLE restricter (
     INDEX index2(col1),
share|improve this answer
This looks smart but I see a problem. The column may not be unique (and probably isn't in this case) so a foreign key towards it, is not advised although allowed in MySQL. – ypercubeᵀᴹ Jul 13 '14 at 11:36
+1 though because if you use the (Schueler_ID, Klasse_ID) combination instead of the one column, it should work. – ypercubeᵀᴹ Jul 13 '14 at 11:39

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.