Join Tables on Columns of Composite Foreign/Primary Key in a Query

Join tables on columns of composite foreign / primary key in a query

There is a NATURAL JOIN:

SELECT *
FROM   subscription
NATURAL JOIN delivery;

Quoting the manual on SELECT:

NATURAL

NATURAL is shorthand for a USING list that mentions all columns in the two tables that have the same names.

It would work for your test setup, but it's not strictly doing what you ask for. The connection is based on all columns sharing the same name. Foreign keys are not considered. The cases where NATURAL JOIN is a good idea are few and far between.

Simplify code / less verbose

For starters, you could use table aliases and you don't need parentheses around the join conditions with ON (unlike with USING):

SELECT *
FROM   subscription s
JOIN   delivery     d ON d.magazine_id = s.magazine_id
                     AND d.user_id = s.user_id;

Since column names in the join conditions are identical, you can further simplify with USING:

SELECT *
FROM   subscription s
JOIN   delivery     d USING (magazine_id, user_id);

There is no syntax variant making joins based on foreign key constraints automatically. You would have to query the system catalogs and build the SQL dynamically.

Joining two tables, with a primary key to many foreign keys

This should do the job:

CREATE TABLE T1 (
    ID INT,
    Name VARCHAR (25),
    Job VARCHAR (25)
    );
CREATE TABLE T2 (
    ID INT,
    Color VARCHAR (25)
    );

INSERT INTO T1 VALUES
(1, 'John', 'Worker'),
(2, 'Jane', 'Worker');

INSERT INTO T2 VALUES
(1, 'Blue'),
(1, 'Yellow'),
(1, 'Green'),
(2, 'Orange');

SELECT  *,
        ( 
            SELECT  T2.color + ' '
            FROM    T2  INNER JOIN 
                    T1 ON T2.ID = T1.ID
            WHERE TT.ID = T2.ID
            FOR XML PATH('')
        ) AS Colors
FROM    T1 TT

Results:

+----+------+--------+--------------------+
| ID | Name |  Job   |       Colors       |
+----+------+--------+--------------------+
|  1 | John | Worker | Blue Yellow Green  |
|  2 | Jane | Worker | Orange             |
+----+------+--------+--------------------+

Demo

Join two tables together with the use of a third table that contains composite keys from both?

The problem here is your design; you need to fix it. You are storing a delimited value in your column, combinedcode, in your junction table.

What you should be doing is storing the 2 values in separate columns, and then creating your foreign keys on those values. This would look something like this:

CREATE TABLE dbo.Table1 (SomeID varchar(10) NOT NULL,
                         SomeValue varchar(20));
ALTER TABLE dbo.Table1 ADD CONSTRAINT PK_Table1 PRIMARY KEY (SomeID);
GO
CREATE TABLE dbo.Table2 (OtherID varchar(10) NOT NULL,
                         OtherValue varchar(20));
ALTER TABLE dbo.Table2 ADD CONSTRAINT PK_Table2 PRIMARY KEY (OtherID);
GO
CREATE TABLE dbo.JunctionTable (SomeID varchar(10) NOT NULL,
                                OtherID varchar(10) NOT NULL);
ALTER TABLE dbo.JunctionTable ADD CONSTRAINT FK_JunctionTable1 FOREIGN KEY (SomeID) REFERENCES dbo.Table1(SomeID);
ALTER TABLE dbo.JunctionTable ADD CONSTRAINT FK_JunctionTable2 FOREIGN KEY (OtherID) REFERENCES dbo.Table2(OtherID);

Depending on your design, you may want to make it so that the value in the junction table are are unique:

ALTER TABLE dbo.JunctionTable ADD CONSTRAINT PK_JunctionTable PRIMARY KEY (SomeID,OtherID);

Then, to do your JOINs it would be as simple as:

SELECT {Your Columns}
FROM dbo.Table1 T1
     JOIN dbo.JunctionTable JT ON T1.SomeID = JT.SomeID
     JOIN dbo.Table2 T2 ON JT.OtherID = T2.OtherID;

Why Composite Primary key when I can use Single Primary key with Unique constraints on composite columns?

You do not need a primary key to enforce uniqueness. You can use a unique constraint or index instead.

I am not a fan of composite primary keys. Here are some reasons:

• All foreign key references have to include all the keys in the correct order and matching types. This makes is slightly more cumbersome to define those tables.
• Because the composite keys are included in all referencing tables, those tables are often larger, which results in worse performance.
• If you decide that you want to change the type of one of the component keys -- say the length of a string or an int to a numeric -- you have to modify lots and lots of tables.
• When joining tables, you have to include all the keys. If you miss one . . . well, the code is syntactically correct but the results are wrong.

There are occasions where composite keys are acceptable, such as tables that have no foreign key references. Even in those cases, I use synthetic keys, but I totally understand the other perspective.

Foreign key relationship with composite primary keys in MySQL

One way is this

alter table t add constraint fk_t_id1_id2
foreign key (id1, id2) references table2(id1, id2);

As Gordon said, the best option is create an auto incremental ID and make this the primary key.

SQL: Foreign key references a composite primary key

If you have a compound PK made up from three columns, then any child table that wants to establish a foreign key relationship must ALSO have all those 3 columns and use all 3 columns to establish the FK relationship.

FK-PK relationship is an all or nothing proposal - you cannot reference only parts of a primary key - either you reference all columns - or you don't reference.

CREATE TABLE Purchase 
(
    No_Installments int,
    Rate int,

    Person varchar(50) NOT NULL PRIMARY KEY,

    First_Name varchar(20) NOT NULL,
    Name varchar(20) NOT NULL,
    Address varchar(50) NOT NULL,

    CONSTRAINT PFK 
        FOREIGN KEY (First_Name, Name, Address) 
        REFERENCES Person (First_Name, Name, Address)
);

---

Related Topics