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?