Foreign Key with Multiple Fields and Date Field

DoubleDee · July 12, 2014, 2:49am

Could use some help understanding how to create a Foreign Key Constraint with two parent tables - one of which has a Composite UK that includes a DateTime field.

The first “parent” table is normal and looks like this…

member (Reviewer)


- id (PK)
- email (UK)

and so on...

The second “parent” table is trickier, because it has a Composite UK like this…

article_comment


- id (PK)
- article_id (UK1)(FK)
- member_id (UK2)(FK)  **Commenter
- created_on (UK3)

My “child” junction table looks like this…

article_comment_review


- id (PK)
- article_id (UK1)(FK)
- commenter_id (UK2)(FK)
- comment_created_on (UK3)(FK)
- reviewer_id (UK4)(FK)
- rating

While I could join this article_comment_review to the PK of each Parent table above, it wouldn’t be very descriptive, and so I would like to use the Composite UK from the second “parent” table.

Here is where I am confused…

When I build the Foreign Key Constraint for article_comment_review, how do I link FROM article_comment_review.comment_created_on TO article_comment.created_on?? :-/

To build a link between the Parent and Child tables in the Foreign Key relationship, phpMyAdmin requires that an Index exists on each field in the Foreign Key. So that means I would need to place an Index on the article_comment_review.comment_created_on field…

In the Parent table, only the combination of “article_id” + “member_id” + “created_on” make a Comment unique. So it seems to me that I need the following links…


article_comment_review.article_id ---> article_comment.article_id
article_comment_review.commenter_id ---> article_comment.member_id
article_comment_review.comment_created_on ---> article_comment.created_on
article_comment_review.reviewer_id ---> member (Reviewer).id

But how can I put an Index on a DateTime column - especially when that column might have multiple instances of the same time-stamp??

Follow me?

What do I do???

Sincerely,

Debbie

r937 · July 12, 2014, 12:22pm

i doubt very much that this is true

mysql requires only that an index exists on the foreign key as a whole, and mysql will helpfully build it for you when you declare the FK

i tested this for you

like this –

CREATE TABLE article_comment_review
( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
, article_id INTEGER
, commenter_id INTEGER
, comment_created_on DATETIME
, reviewer_id INTEGER
, rating TINYINT 
, CONSTRAINT debbie_doo
    FOREIGN KEY ( article_id, commenter_id, comment_created_on )
      REFERENCES article_comment
                ( article_id, member_id, created_on )
);

if the table already exists and you are adding the FK, you’d use ALTER TABLE instead, but the constraint definition would still be the same

as i said, mysql goes ahead and creates a 3-column KEY on the FK columns for this FK definition

so you don’t even have to create indexes in phpmyadmin ahead of time

DoubleDee · July 12, 2014, 3:04pm

Sadly, it is true.

Last night, I broke down and created 4 Indexes on my 4 fields, plus a 4-column composite Unique Index. After doing that, phpMyAdmin let me link the 4 fields in the Junction Table to the corresponding 4 fields in the two Parent tables.

After reading your post, as a test, I made a copy of article_comment_review, dropped the 4 individual Indexes, and tried to set up the Foreign Key Constraints using just my 4-column Composite Unique Index like you suggested, but no luck.

Well, according to the Manual…

InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the first columns in the same order.

http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html

r937:

i tested this for you

like this –
CREATE TABLE article_comment_review
( id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT
, article_id INTEGER
, commenter_id INTEGER
, comment_created_on DATETIME
, reviewer_id INTEGER
, rating TINYINT 
, CONSTRAINT debbie_doo
    FOREIGN KEY ( article_id, commenter_id, comment_created_on )
      REFERENCES article_comment
                ( article_id, member_id, created_on )
);
if the table already exists and you are adding the FK, you’d use ALTER TABLE instead, but the constraint definition would still be the same

as i said, mysql goes ahead and creates a 3-column KEY on the FK columns for this FK definition

so you don’t even have to create indexes in phpmyadmin ahead of time

Not sure how your test relates to what I said above, but from my own testing, I know that in phpMyAdmin, when I dropped my individual Indexes I can no longer create the Foreign Key. And according to the Manual, phpMyAdmin’s behavior is consistent with this rule.

Thoughts??

Off Topic:

On a side note, how much of a sin is it to put an Index on a DateTime field? (That was my initial concern in creating this thread…)

Sincerely,

Debbie

r937 · July 12, 2014, 4:04pm

we could argue this forever, so i’ll just say that i believe you have misunderstood how FKs and indexes work

re: the “but no luck” story… i cannot comment on it because i didn’t see your CREATE TABLE or the error message

re: how my test relates to what you said above… because you claimed that indexes on the individual columns were needed, and i demonstrated that they weren’t

maybe you’ve heard me comment before that phpmyadmin is a p.o.s. and it wouldn’t surprise me if it is distorting your view of what’s required to make a multi-column foreign key in mysql

DoubleDee · July 12, 2014, 4:41pm

Yes, I know you don’t like phpMyAdmin, and yes, it could be distorting things.

Sadly, I don’t have time to learn WorkBench or become a super DBA like you, so sometimes I have to just get things working for now.

I’ll assume my current indexes will not blow up my database, and that worst case scenario things may not be tuned as well as they could be.

And I would love to learn more about this topic, but based on former experience, I assume my quarter has run out with you…

Sincerely,

Debbie

r937 · July 12, 2014, 5:01pm

there is no need to start calling me names

i’ve told you before, i’m not a DBA

DoubleDee · July 12, 2014, 5:08pm

Then what are you?

A lover of databases?

I used the term loosely to imply that you are an expert with databases…

Debbie

r937 · July 12, 2014, 5:20pm

okay, then here’s a suggestion… learn databases and their behaviours via sql

i’m guessing you tried your tests using phpmyadmin’s point-and-click interface?

try creating your test tables using raw sql

pro tip: keep your sql in a library or folder or drawer or whatever you apple people call it

use a text editor to make changes to the sql

copy/paste the entire sql statement into phpmyadmin’s SQL tab, and run it there

if you made an error, go back to the source sql in your text editor, change it (and save it), and re-copy/paste/phpmyadmin

you will learn, doing it this way, the correct structure mysql actually requires

DoubleDee · July 13, 2014, 1:28am

Good advice.

When I get my website done - in a few weeks? - then for v3.0 maybe I can start doing that?

(I have always loved working with databases, but I am rusty on database stuff, and my priority right now is getting v2.0 done after working on it for nearly 4 years!!!)

Yep.

FWIW, I started doing that a few months ago with VIEWS I was creating.

I hand-wrote them in TextEdit, and then pasted them in phpMyAdmin.

You may also find this hard to believe of me, but all of the (DML) queries on my website were hand-written in Text Edit. (I rely on phpMyAdmin for DDL and DCL since those aren’t my forte’…)

Sincerely,

Debbie