how to use the key word 'references' in mysql ?

Question

hi, how is 'references' keyword use while "creating" a table ? let's say I want to create two tables name them as person and hobby and i want the hobby table to reference the id of person table so how is that ?

person table - id - name

hobby - id - person_id - hobby_name

Answer 1

Here is an example directly from MySQL website:

CREATE TABLE parent (id INT NOT NULL,
                     PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (id INT, parent_id INT,
                    INDEX par_ind (parent_id),
                    FOREIGN KEY (parent_id) REFERENCES parent(id)
                    ON DELETE CASCADE
) ENGINE=INNODB;

Answer 2

Create the hobby table similarly to this:

CREATE TABLE hobby (
  id INT NOT NULL AUTO_INCREMENT,
  person_id INT NOT NULL,
  hobby_name VARCHAR(255),
  PRIMARY KEY(id),
  FOREIGN KEY(person_id) REFERENCES person(id))

Answer 3

CREATE TABLE person (person_id INT NOT NULL, 
PRIMARY KEY (person_id));

CREATE TABLE hobby (hobby_id INT NOT NULL, person_id INT NOT NULL,
PRIMARY KEY(hobby_id),
FOREIGN KEY(person_id) REFERENCES person(person_id));

The references keyword is used to define which table and column is used in a foreign key relationship. This means that a record in the hobby table must have a person_id that exists in the person table or else at the time of insert you will receive an error that the key does not exist.

To answer your question above about what "ON DELETE CASCADE" does, it allows you to delete a parent key record (in person) and it's corresponding children records (in hobby) without having to delete all the children records first.

To clarify, if you have children records attached to a primary key entry and you attempt to delete the primary key entry like:

DELETE FROM person where person_id = 1;

without having the DELETE ON CASCADE, you would receive an error if any records in hobby had person_id's of 1. You would have delete all of those records first before doing the delete above. With DELETE ON CASCADE used, the above delete would succeed and automatically delete any and all records from table hobby table linked to the person_id being deleted from the primary key table.