5

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.

Lion
  • 18,729
  • 22
  • 80
  • 110
mecio
  • 263
  • 1
  • 3
  • 7

1 Answers1

2

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

Community
  • 1
  • 1
Tim Gee
  • 1,062
  • 7
  • 9
  • Let's say we have email address instead of id. User want to hange his email and is also primary key of the table like in example above. – mecio Dec 17 '11 at 17:02
  • Yes that's bad database design and so there's no automatic way to cascade such a change because you shouldn't do it. I'll update my answer to provide an external resource with clarification. – Tim Gee Dec 17 '11 at 17:05
  • @mecio:) Taking email addresses as a primary key is a wrong concept to implement-- a wrong database design. – Lion Dec 17 '11 at 17:06
  • Hey, I know it is wrong database desing... But I am trying to implement this for educational purpuse. – mecio Dec 17 '11 at 17:11
  • @mecio:) Implementing a wrong database design for educational purpose makes no sense. Do you really want to go in a wrong way? – Lion Dec 17 '11 at 17:16
  • @TimGee I see your point :) But I was wondering if this is possible to make this kind of relation between one table. I know it's bad but i need to know if it is possible. – mecio Dec 17 '11 at 17:19
  • @Lion I just' play with foreign keys to understand them better. I won't use this database at all. – mecio Dec 17 '11 at 17:21
  • OK then my final question..Is it even possible to tell mysql to update foreign key if i update parent id? We are talking about the above example. – mecio Dec 17 '11 at 17:27
  • Check out: http://stackoverflow.com/questions/1481476/when-to-use-on-update-cascade – Tim Gee Dec 17 '11 at 17:29
  • @TimGee I was there..This is quite diffrent concept - we have one table and foreign key that point to the same table. – mecio Dec 17 '11 at 17:33
  • I think we are veering off topic from the question you originally asked. You should clarify your original question. – Tim Gee Dec 17 '11 at 17:38
  • My question was "is this possible?". – mecio Dec 17 '11 at 17:41
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/5937/discussion-between-tim-gee-and-mecio) – Tim Gee Dec 17 '11 at 17:59