Can a unique key with 3 columns act as a foreign key for one-to-many relationship

Question

I am having a classic case of one-to-many relation.

Currently I'm having an SQL like given below with no relation whatsoever defined between two tables (my_table and comments table):

SELECT
    (SELECT
            COUNT(1)
        FROM
            comments
        WHERE
            comments.gl_account = my_table.gl_account
                AND comments.document_number = my_table.document_number
                AND comments.division = my_table.division) AS comments_count,
    my_table.division,
    my_table.gl_account,
    my_table.document_number,
    my_table.reviewer_group,
    my_table.id
FROM
    my_table;

It is returning comments count in addition to other columns from my_table.

I want to correct this SQL by using 1-to-many relationship; that from my_table (one) to comments table (many).

How do I do this? Note that there are three columns used by inner query above.

Sample SQL snippet hint appreciated, thank you!

How come they have "no relation whatsoever" and still compare their columns in the subquery WHERE clause? — jarlh, Aug 25 '20 at 17:38
I mean they are logically storing same data. But no constraints defined as such e.g. a foreign key. — Akshay Lokur, Aug 25 '20 at 17:39

score 2 · Answer 1 · answered Aug 25 '20 at 17:38

2

Presumably, those three columns are unique in my_table, so you can declare them as a primary key:

create table my_table (
    . . . ,
    primary key (division, gl_account, document_number)
);

Then you can declare a foreign key relationship:

create table comments (
    . . . ,
    foreign key (division, gl_account, document_number) references my_table (division, gl_account, document_number)
);

answered Aug 25 '20 at 17:38

Gordon Linoff

1,242,037
58
646
786

Should a unique work instead of primary key? – Akshay Lokur Aug 25 '20 at 17:42
@Akshay If it is unique, what's the issue implementing the PK? – Zak Aug 25 '20 at 17:46
@AkshayLokur . . . MySQL will recognize the foreign key reference with just `key` -- *any* index. The standard specifies `unique` or `primary key`. But you should use the primary key; foreign key references are one of the main reasons to define them. – Gordon Linoff Aug 25 '20 at 17:48
@GordonLinoff, Makes sense. The PK has been assigned to another column and I will need to change it now. Possible to give example of how can I get the comments count though? – Akshay Lokur Aug 25 '20 at 17:48
@AkshayLokur . . . Wrong approach. Use the primary key in second table. – Gordon Linoff Aug 25 '20 at 20:20
Ah it is my framework (Laravel Eloquent) which does not support composite primary keys. I think I need to resort to unique keys in this case... – Akshay Lokur Aug 26 '20 at 02:25
@AkshayLokur . . . I support avoiding composite primary keys. – Gordon Linoff Aug 26 '20 at 10:57
Well then you suggest doing that in your answer above. Sorry a bit confusing. – Akshay Lokur Aug 26 '20 at 10:59

Can a unique key with 3 columns act as a foreign key for one-to-many relationship

1 Answers1