Combining two tables

[!const]

I want to make an association type "a message can have zero or more comment" but I have no idea how to do it. I managed to lay it after my research but I am not sure that it do what I want:

CREATE TABLE `messageboard` { 
`id_messageboard` int UNSIGNED NOT NULL AUTO_INCREMENT, 
`ip` varchar(15) NOT NULL, 
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, 
`pseudo` varchar(20) NOT NULL, 
`mess` text NOT NULL, 
PRIMARY KEY (`id_messageboard`) 
} ENGINE=InnoDB DEFAULT CHARSET=utf8; 

CREATE TABLE `comments` { 
`id_comments`   int UNSIGNED NOT NULL AUTO_INCREMENT, 
`id_messageboard` int UNSIGNED NOT NULL, 
`ip` varchar(15) NOT NULL, 
`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP, 
`pseudo` varchar(20) NOT NULL, 
`message` text NOT NULL, 
PRIMARY KEY (`id_comments`), 
FOREIGN KEY (`id_messageboard`) REFERENCES `messageboard` (`id_messageboard`) ON DELETE NO ACTION ON UPDATE NO ACTION 
} ENGINE=InnoDB DEFAULT CHARSET=utf8;

So now, I am or not?

[Grohu]

There is just something that I can not capture the "NO ACTION ON DELETE ..."
If you delete the message, it will trigger or do something that will make you do your work.

[!const]

Yes but that's because I do not know what to put in place. But yes you're right, we need something happens when a message is deleted.

[Grohu]

Given that the last BDD on which I worked were SQL Server and Oracle, I am a little rusty in MySQL. But I suppose there must be the equivalent of trigger to trigger removal of comments that are linked to this message. And in general the DBMS is even able to do it alone.

[!const]

"ON DELETE CASCADE"

I think that's right. Otherwise, you think it will work if I make my tables like that?

[Grohu]

Yeah that's right

(Same syntax as Oracle and SQL Server )