2nd part Referential integrity with MySQL

In first part I talked about the basis of referential integrity, in this part I will go through few practical examples.
For start we will create on table that we will call ‘news’ and then insert few rows

CREATE TABLE news (id TINYINT NOT NULL AUTO_INCREMENT,
title VARCHAR(50), body VARCHAR(255) NOT NULL, PRIMARY KEY(id)) ENGINE=INNODB;
Query OK, 0 rows affected (0.11 sec)

INSERT INTO novosti VALUES (1, 'Some title, 'Testing body'), (2, 'Another title', 'Again some body for news');
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

To show example of referential integrity we will create another table called ‘comments’

CREATE TABLE comments (
id INT(4) NOT NULL,
title VARCHAR(50) NOT NULL,
comment VARCHAR(255) NOT NULL,
id_news TINYINT(4) NOT NULL,
INDEX (id_news),
FOREIGN KEY (id_news) REFERENCES novosti (id),
PRIMARY KEY(id)
) ENGINE=INNODB;

In table comments we defined foreign key id_news and referred it to id field in table news. I we tried to insert random rows in comments table it would look something like this:

INSERT INTO comments VALUES (1, 'Comment title', 'Some comment text', 91);
ERROR 1216 (23000): Cannot add or update a child row: a foreign key
constraint fails

Note: look at first and last value of this insert, note that it does not match any news so this comment would be just junk data.
So we are protected on database level from junk data. Even tho this is highly recommended don’t try to dump your application level validation, since you need error handling (so you users don’t get ugly errors) and also mysql is not able to validate data for example E-mail addresses.

Hope it helps.