I'm testing some queries with MySQL and tried to create two tables were there should be a reference between IDs. Something like:
CREATE TABLE user
(
id int(11) UNSIGNED AUTO_INCREMENT,
name varchar(30) NOT NULL,
PRIMARY KEY id
);
CREATE TABLE photo
(
id int(11) UNSIGNED AUTO_INCREMENT,
user_id int(11) UNSIGNED,
filename varchar(50) NOT NULL,
PRIMARY KEY id
);
A query like:
SELECT p.filename, u.name FROM user AS u
INNER JOIN photo AS p ON u.id = p.user_id
Would result in all records of photos and their user's name.
So if it is possible to create a relation on query, why would I use foreign keys and define NO ACTION, considering I want to keep a history of photos, if there is no need for that? Foreign keys helps on performance or just make data more organized?