Menu
How is the references keyword used when creating a table?
Let's say I want to create two tables person and hobby and I want the hobby table id to reference the id of person?
person table - id - name hobby - id - person_id - hobby_name How do I do that?
360
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.
We want to ensure that the orders table only contains orders of products that actually exist. So we define a foreign key constraint in the orders table that references the products table: CREATE TABLE orders ( order_id integer PRIMARY KEY, product_no integer REFERENCES products (product_no), quantity integer );
The only way you can enforce referential integrity in MySQL is by using a foreign key. This is an indexed column in a child table that refers to a primary key in a parent table. This is achievable if you design your tables with MySQL InnoDB database engine.
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)) 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; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
