Jump to content

Setting up foreign key


phppup
 Share

Go to solution Solved by Barand,

Recommended Posts

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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)

 

Link to comment
Share on other sites

Posted (edited)

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 by phppup
Clean up post
Link to comment
Share on other sites

  • Solution
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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.