//create table
$sql = "CREATE TABLE `s_tags` (
`id` INT(11) NOT NULL AUTO_INCREMENT, 
`user` INT(11) NOT NULL DEFAULT '0', 
`photo` INT(11) NOT NULL DEFAULT '0', 
PRIMARY KEY (`id`), 
KEY `tag_photo_ind` (`photo`), 
KEY `tag_user_ind` (`user`)
) 
ENGINE=InnoDB DEFAULT CHARSET=utf8";
mysql_query($sql) or die(mysql_error());
 
 
// alter table
$sql = "ALTER TABLE `s_tags` 
ADD CONSTRAINT `s_ibfk_1` 
FOREIGN KEY (`user`) 
REFERENCES `users` (`id`) 
ON DELETE CASCADE, 
ADD CONSTRAINT `s_ibfk_2` 
FOREIGN KEY (`photo`) 
REFERENCES `photos` (`id`) 
ON DELETE CASCADE";
mysql_query($sql) or die(mysql_error());

Why is the purpose of the syntax (that I have underlined)?

What is the purpose of ADD CONSTRAINT, FOREIGN KEY, REFERENCES and ON DELETE CASCADE; in the //alter table part of the SQL code, in relation to the //create table part?


Thanks,
Lashtal

This may be helpful

W3schools was able to answer the first three constraints, but not the fourth: ON DELETE CASCADE

does anyone know this one?

Hmm, I don't know, but I'll guess. Maybe it means that when you delete something, the data from the previous record fills that spot?

Quick google:
Use the ON DELETE CASCADE option to specify whether you want rows deleted in a child table when corresponding rows are deleted in the parent table. If you do not specify cascading deletes, the default behavior of the database server prevents you from deleting data in a table if other tables reference it.

For MySQL take note that CONSTRAINT, FOREIGN KEY, and ON DELETE CASCADE will ONLY be valid IF you are using InnoDb as the storage engine.

Thank you, lynxus

Lemme' me see if I have broken this down right.


So...

the ALTER TABLE portion of this script sets a constraint for `users` (`id`) and `photos` (`id`)

then: if either 'id', 'users', or 'photos' is deleted from the parent table; the child table and it's constraints, will also be removed from the database?