1

I completely understand the need to PKs and FKs but when using relational databases. But when using Mysql from the the terminal. what difference does it make adding PKs & FKs when you can you can join on a common column?

If I create the table 

mysql> create table dudes (dude_id int, name varchar(30), age int);
mysql> create table pets (pet_id int, owner_id int, address varchar(30));

as opposed to doing this

mysql> create table dudes (dude_id int primary key, name varchar(30), age int);
mysql> create table pets (pet_id int primary key, owner_id int references(dude_id), address varchar(30));

I can still join each table correctly and do all that is needed. What else is MySQL doing in the background that makes my life easier by explicitly adding the PK and FK references?

SELECT *
From dudes d
JOIN pets p ON d.dude_id=p.own_id;
skaffman
  • 398,947
  • 96
  • 818
  • 769
stackoverflow
  • 18,348
  • 50
  • 129
  • 196

4 Answers4

2

It does a lot, actually. Primary keys will create and index on that key to make lookups faster.

EDIT: Adding primary keys is by and large a necessity. With a database of any significant size this will give you major performance gains. Setting up foreign keys, on the other hand, is more a matter of opinion.

The point of maintaining foreign keys is to support the relational aspect of your RDMS. By defining these relationships explicitly, you make it so that arbitrary values cannot be put in those fields.

Additionally, if you chose to implement them, you could do things like cascading updates and deletes. These make sure that when you delete a record, all associated data (depending on their relationships), gets deleted as well.

There are also reasons not to implement foreign keys. There is added overhead with them, and the same logic could be maintained in code.

Stefan H
  • 6,635
  • 4
  • 24
  • 35
1

In MySQL a primary key is automatically indexed -- using indexes to join rows will be faster than sequentially reading all the data of a table

Murray McDonald
  • 631
  • 4
  • 5
1

PK are always indexed, thus search will be orders of magnitude faster when the table will grow. FK will ensure that if a dependency is to be broken or not fulfilled , the request will break or operate a controlled automatic operation (CASCADE)

Ie: if you add a pet and it has no valid associated dude : FK will break the request attempt issuing an error if you delete a dude that had pets , if set, CASCADING can automatically delete the associated pet rows.

dweeves
  • 5,525
  • 22
  • 28
1

The running time is the difference between them.

Let's assume the following

  • An index lookup is a binary search
  • dudes has 1000 rows
  • pets has 1000 rows

The tables without dude_id as primary key will produce 1,000,000 key comparisons, aka Cartesian Product. That's O(n^2) running time.

The tables with dude_id as primary key will produce 10,000 (1000 x 10(log base2 of 1000) key comparisons. That's O(n log n) running time.

Unless there is poor distribution of dude_id values in owner_id (such as if dude_id owns like 50+ pets), the second layout should always win hands down.

RolandoMySQLDBA
  • 43,883
  • 16
  • 91
  • 132