relational table

[birbal]

suppose:

table1 (parent table)usrid int not null unsighnedname varchar not null

table2 (child table)msgid int not null unsighnedsender int not nullmsg varchar not null

table.1 usrid is in relation with table2.senderif i doon update cascade on delete cascadethen it will delete table2 row when i will delete table1 row (parent).but i want that if table1 row get deleted then table2 row wont get deleted. instead of deleted it will change msgid (int). and i can point out sender as annonymous.i guess some set default may work. though not sure. i was thinking something like either set default number on delte(like 0) and when i will fetch the "sender" with php. it will checkit is ZERO or not if true then echo as annonymous....though i am not sure about it. how to do that. what would be the correct way. need some guide.

[boen_robot]

Instead of "ON DELETE CASCADE", you can use the alternative "ON DELETE SET NULL". That way, whenever you delete a row from the parent, all childs with that value will change their value to NULL. Your app should then use "NULL" to mean "anonymous".

[birbal]

ok...thanks boen_rboti have a another question.

quoted from mysql official siteSET DEFAULT: This action is recognized by the parser, but InnoDB rejects table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

what does that mean actually? if innodb reject to use SET default. for what is it there?

[justsomeguy]

Generally those things are part of the official SQL language that haven't been supported by one of the storage engines for MySQL yet. The syntax is valid, so MySQL doesn't complain about it, it just doesn't do anything with it. In the future they may add support for that in InnoDB or another engine.

[birbal]

Generally those things are part of the official SQL language that haven't been supported by one of the storage engines for MySQL yet. The syntax is valid, so MySQL doesn't complain about it, it just doesn't do anything with it. In the future they may add support for that in InnoDB or another engine.

ok thaks jsg. i was confused by thir doc.can you please tell me what is the diffrence beetween NO ACTION and RESTRICT. they are saying both are same. but why two option for doing same task?

[justsomeguy]

Many languages have multiple ways to do a certain thing. Even something as simple as sending output in PHP has several methods to do it.

[birbal]

ohhh ok...thank you jsg for your time