Do you need to set foreign keys in MySQL?

foreign-keys, mysql, php, sql

Solution

Yes, one concrete reason is to have faster retrieving of rows if you want to join tables. Creating a foreign key constraint automatically creates a an index on the column.

So table address' schema should look like this, (assuming `People`'s table primary key is `PERSON_ID`)

CREATE TABLE Address
(
    Address_ID INT,
    Person_ID INT,
    ......,
    CONSTRAINT tb_pk PRIMARY KEY (Address_ID),
    CONTRRAINT tb_fk FOREIGN KEY (Person_ID) 
        REFERENCES People(Person_ID)
)

Problem

Let's say you have got two tables like the following in a MySQL database: TABLE people: ``` primary key: PERSON_ID, NAME, SURNAME, etc. ``` TABLE addresses: ``` primary key: ADDRESS_ID, foreign key: PERSON_ID, addressLine1, etc. ``` If you manage the creation of rows (in both table) and the retrieving of data trough PHP do you still need to create a physical relationship in the database? If yes, why?

Original source

Related problems