Setting up foreign key

[phppup]

It was so much simpler when I stored all my collected data on one big table (well, not really too big, actually)

Now I am attempting to be more efficient by learning the proper use of foreign keys.

Table1 id PK auto increment

Table2 id PK auto increment
       linkID  FK references Table1(id)

Table3 id PK auto increment
       linkID  FK references Table1(id)

When I tried to implement this table structure, MySql would not allow me to create Table3.

What am I doing wrong?
Is my approach/understanding flawed?
Do tables 2 & 3 need a primary key? Why?

[gizmola]

Do you have an Entity Relationship diagram we can look at?

Every table needs a primary key.  The key is used to tell the difference between rows, so it must be unique across all rows in the table. 

I'm assuming you are using InnoDB for all the tables.  You need InnoDB for referential integrity constraints, row level locking and transactions to work in MySQL.  With InnoDB, the data is actually stored in primary key order, which means that when you read a row by PK, whether that be from a direct query or join, the entire data of the row is also read.  This is because the table itself is used as the PK index.  Sometimes you will see this referred to as a "clustered index".  This design adds efficiency to many normal operations because MySQL only has to read the data directly via the indexing process and doesn't have to read an index first and then use that to locate the data.  Not all searches are based on keys, but many are.

When you say "MySQL would not allow me to create Table3" what error are you receiving?

[phppup]

I think it's Maria DB, if that makes any difference.

I don't have access to now, but when putting the code directly in as SQL through the admin access, I got a red dot and an "unrecognized    expecting... " balloon message.

I tried several variations to resolve the issue, but thought I'd better check my structure here (since everything else to establish the FK send pretty straight forward.

Table1 id PK auto increment
       - users personal info -

Table2 id PK auto increment
       favorite car year, make, model, color
       linkID  FK references Table1(id)

Table3 id PK auto increment
       favorite vacation location, hotel, duration
       linkID  FK references Table1(id)

 

My approach is that while tables 2 & 3 are separate from each other they are both connected to the individual user, and would connect to the respective ID.

Is my approach valid, or do I need to connect the tables like a set of trains on a track rather than a motor boat with several water skiers?

[Barand]

Should be possible. Without seeing your SQL code we can't help.

This worked...

mysql> CREATE TABLE `person` (
    ->   `id` int(11) NOT NULL,
    ->   `username` varchar(20) DEFAULT NULL,
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.21 sec)

mysql> CREATE TABLE `favourite_car` (
    ->   `id` int(11) NOT NULL,
    ->   `year` year(4) DEFAULT NULL,
    ->   `make` varchar(45) DEFAULT NULL,
    ->   `model` varchar(45) DEFAULT NULL,
    ->   `color` varchar(45) DEFAULT NULL,
    ->   `person_id` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `FK_car_person_idx` (`person_id`),
    ->   CONSTRAINT `FK_car_person` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.26 sec)

mysql> CREATE TABLE `favourite_vacation` (
    ->   `id` int(11) NOT NULL,
    ->   `location` varchar(45) DEFAULT NULL,
    ->   `hotel` varchar(45) DEFAULT NULL,
    ->   `duration` int(11) DEFAULT NULL,
    ->   `person_id` int(11) DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `FK_vacation_person_idx` (`person_id`),
    ->   CONSTRAINT `FK_vacation_person` FOREIGN KEY (`person_id`) REFERENCES `person` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.31 sec)

 

[phppup]

Thanks @Barand.

I seem to have had it right, but obviously there is a wrinkle somewhere that I'll need to iron out while creating the PHP code.

Along these lines, once the FK connections are established, will they enable a more direct INSERT of data from a form? Or do I need an INSERT statement for each table independently?

Are JOINS only for data retrieval?

Edited May 22, 2022 by phppup
Clean up post

[Barand]

26 minutes ago, phppup said:

do I need an INSERT statement for each table independently?

Yes. The referential integrity enforced by the FK will allow you to insert car or vacation records only if the parent person exists.

 

28 minutes ago, phppup said:

Are JOINS only for data retrieval?

No, they can be used in updates and deletions too.