0

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?

3

The basic reason in my opinion for using constraints - and not only foreign key ones - is to enforce integrity at the database level and do not rely on the possibly several applications that will use the database in its entire lifetime.

Defining that FOREIGN KEY enforces that every photo - to be inserted - has to be related to an existing user.
Defining NO ACTION enforces that a user cannot be deleted if any related photos are not deleted first.
Defining ON CASCADE DELETE enforces that the photos are deleted along with the user's deletion.
Defining a UNIQUE constraint on username does not allow two users with same name, etc...

In all of the above, the DBMS is entitled to maintain the integrity according to the constraints defined and not allow the database to fall into an inconsistent state.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.