Shirkam Shirkam - 1 year ago 94
SQL Question

Performance and model differences between indexed FK and indexed column

I was designing some tables, using MySQL WorkBench, and setting Foreign Keys on some columns, to follow some "correct" design pattern. Then, my boss asked me why I used FK, because they "only cause problems".

Seeing what does MySQL WorkBench when creating a new FK, it creates the relationship, and an index on it. Like this:

CREATE TABLE `smx_portales-des`.`A1` (
`idA1` INT NOT NULL,
`name` VARCHAR(45) NULL,
PRIMARY KEY (`idA1`));

CREATE TABLE `smx_portales-des`.`A2` (
`idA2` INT NOT NULL,
`price` DECIMAL(10,2) NULL,
`fkA1` INT NULL,
PRIMARY KEY (`idA2`),
INDEX `fk_A2_A1_idx` (`fkA1` ASC),
CONSTRAINT `fk_A2_A1`
FOREIGN KEY (`fkA1`)
REFERENCES `smx_portales-des`.`A1` (`idA1`)
ON DELETE NO ACTION
ON UPDATE NO ACTION);


My boss told me to create table
A2
as follows:

CREATE TABLE `smx_portales-des`.`A2` (
`idA2` INT NOT NULL,
`price` DECIMAL(10,2) NULL,
`fkA1` INT NULL,
PRIMARY KEY (`idA2`),
INDEX fk_A2_A1(fkA1));


So, aside from the obvious restriction of FK that only allows values that are on the other table, or
NULL
s, is there any other performance, stylist, or whatever difference in those models?

EDIT: I know that FKs help in DB data integrity, and that there are some cascade options that help removing/updating childs. Also, that the difference is that in the second model, I'm using an index in a "emulated" FK column. My wonders where more about plausible differences in those two models, like efficiency, size...

Answer Source

Your boss is wrong. And feel free to show him or her this answer.

One key piece of functionality in relational databases is maintaining data integrity. A key part of data integrity is assuring that foreign key relationships are correct.

In your database, that guarantees that a2.fka1 is always referring to a valid column in a1 (or has a NULL value). This is important for people understanding the data, for people writing queries (so rows are not lost inadvertently in joins), and potentially for the optimizer).

The right place to maintain this functionality is in the database. You can try to do it from applications, but the database ensures that the data is correct regardless of data modification operations.

Finally, foreign keys are pretty flexible. You (and your boss) should learn about cascading constraints. They usually implement the functionality needed by an application.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download