One to Many with one table

Question

I am trying to create one to many relation using one table. Is this possible?

create table user(id int primary key auto_increment not null,                                                                                                                      
created_by int default null                                                                                                                                                        
)ENGINE=INNODB;                                                                                                                                                                    

alter table user add foreign key (created_by) references user(id) ON DELETE SET NULL ON UPDATE CASCADE;                                                                            

insert into user (id) VALUES(1);                                                                                                                                                   
insert into user (id, created_by) VALUES (2,1);   

Now when I delete user with id=1 the value of created_by automaticaly change to NULL as I expected.

But when I change id of the user with id=1 I get this error

mysql> update user set id=2 where id=1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`jrt`.`user`, CONSTRAINT `user_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `user` (`id`) ON DELETE SET NULL ON UPDATE CASCADE)

How can I fix this? Affter this update I want the column created_by to be cascade changed.

Answer 1

Keys aren't meant to be changed, they are meant to be 'identifying'. So user with id 2 is logically a different user with user with id 1. The moment you create a unique user, it's primary key should be the same for the lifetime of that user.

So this is really a design issue. You need to question why you want to change the id for a particular user. It might be that what you want is both a fixed identifying key AND another identifier (which isn't the key and exists solely on the user table) which you can change.

[Updated with more information] Here's a resource (there are many available online) on the fundamentals of relational database design. http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

The relevant section is "Tables, Uniqueness and Keys".

Fabian Pascal, in his book SQL and Relational Basics, notes that the decision should be based upon the principles of minimality (choose the fewest columns necessary), stability (choose a key that seldom changes), and simplicity/familiarity (choose a key that is both simple and familiar to users).

Personally, I would go further on stability; try to choose a key that never changes. For example, "email" would be a bad choice of key for a user as user's can change their email. If you choose a key, such as an internally generated number or perhaps a unique identifier from the personnel system, then you don't ever have to worry about it changing and migrating this change to other tables.

Note: there may be cases where as a "one off" you need to change a primary key. This is best done with a few manual SQL statements (delete the first user and create an identical second user with a different key). It shouldn't be part of the database design though, which the automated nature of a cascaded update implies.

SEE ALSO: When to use "ON UPDATE CASCADE"

ALSO NOTE: http://forums.mysql.com/read.php?136,391782,391782