Example of MySQL foreign key constraint in relational database design

In relational database design, some of tables have several foreign keys, which point to parent tables.
When the rows in  parent tables  are deleted or updated, what is the behavior of child tables?
Are they deleted or updated accordingly, or et to NULL, or reject the parent table delete/update?
Suppose we have users table:
CREATE TABLE users
(
user_id int NOT NULL,
user_name varchar(45) NOT NULL,
PRIMARY KEY (user_id)
)

We have another table  pictures,  user_id is foreign key which is primary key in table users.
Using foreign key to prevent  insert user_id in table pictures which is invalid in table users.
We can use  naming of foreign key constraint, which can be used in drop foreign key.
CREATE TABLE pictures
( 
pic_id  int NOT NULL,
user_id int NOT NULL,
PRIMARY KEY (pic_id),
CONSTRAINT fk_pictures FOREIGN KEY (user_id) REFERENCES users(user_id)
ON DELETE CASCADE ON UPDATE CASCADE
)

 Now we can drop the FOREIGN KEY constraint using the following SQL:
ALTER TABLE pictures DROP FOREIGN KEY fk_pictures

When the primary key is updated or deleted on parent table, what is the behavior of the child table?
 We have four options:

  RESTRICT | CASCADE | SET NULL | NO ACTION

CASCADE: automatically delete or update the child table when the parent table is deleted or rejected
SET NULL: the foreign key table is set NULL  child table when the parent table is deleted or rejected
RESTRICT:  Rejects the delete or update operation for the parent table
NO ACTION: the same as RESTRICT

 INSERT INTO `users`  VALUES
('1', 'jiansen'), ('2', 'andy')
INSERT INTO `pictures`  VALUES('1', '1')
will be OK
INSERT INTO `pictures`  VALUES('2', '3')
will fail due to that   user_id=3 is not in users table.
Reference:
http://www.w3schools.com/sql/sql_foreignkey.asp
http://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html

Video:  MySQL foreign key constraint