using a composite primary key as a foreign key

[martynwheeler]

Details

I can't seem to work out how to use a composite primary key from one table as a foreign key in another table. It will only let me use the individual colums that make up the composite primary key as the foreign key. Here is what I mean:

here are the two tables:

CREATE TABLE tbl_room (
    id        INTEGER      PRIMARY KEY AUTOINCREMENT,
    region_id INTEGER      REFERENCES tbl_region (id) 
                           NOT NULL,
    name      VARCHAR (20) NOT NULL
);

CREATE TABLE tbl_sensor_measurement (
    sensor_id      INTEGER REFERENCES tbl_sensor (id) 
                           NOT NULL,
    measurement_id INTEGER REFERENCES tbl_measurement (id) 
                           NOT NULL,
    CONSTRAINT pk_sensor_measurement PRIMARY KEY (
        sensor_id,
        measurement_id
    )
    ON CONFLICT ROLLBACK
);

Now I want to create a junction table between these two. However, it will only let me do the following:

CREATE TABLE tbl_room_sensor_measurement (
    id                    INTEGER NOT NULL,
    room_id               INTEGER REFERENCES tbl_room (id) 
                                  NOT NULL,
    sensor_measurement_id INTEGER NOT NULL,
    FOREIGN KEY (
        sensor_measurement_id
    )
    REFERENCES tbl_sensor_measurement (sensor_id) 
);

I would like the sensor_measurement_id to reference the composite primary key (sensor_id, measurement_id)

I hope my question makes sense.

Thanks for the great software

Martyn

[balajiramanathan]

Check this link for possible syntax suggestions to accomplish what you are trying to do: https://stackoverflow.com/questions/10565846/use-composite-primary-key-as-foreign-key

That particular question is for MySQL, but maybe it (or something similar) will work for SQLite also.

[pawelsalawa]

@martynwheeler If you want to reference 2 columns in foreign table, you need to provide 2 columns in local table. You cannot reference compound key (2 or more columns) from just one column of local table.

You will have to split sensor_measurement_id into two columns - sensor_id and measurement_id. Here's how:

CREATE TABLE tbl_room_sensor_measurement (
    id                    INTEGER NOT NULL,
    room_id               INTEGER REFERENCES tbl_room (id) 
                                  NOT NULL,
    sensor_id INTEGER NOT NULL,
    measurement_id INTEGER NOT NULL,
    FOREIGN KEY (
        sensor_id,
        measurement_id
    )
    REFERENCES tbl_sensor_measurement (sensor_id, measurement_id) 
);

[martynwheeler]

Thanks for the information. I will give it a try.

…

On Sat, 4 Jul 2020, 22:25 Paweł Salawa, ***@***.***> wrote: @martynwheeler <https://github.com/martynwheeler> If you want to reference 2 columns in foreign table, you need to provide 2 columns in local table. You cannot reference compound key (2 or more columns) from just one column of local table. You will have to split sensor_measurement_id into two columns - sensor_id and measurement_id. Here's how: CREATE TABLE tbl_sensor_measurement ( sensor_id INTEGER REFERENCES tbl_sensor (id) NOT NULL, measurement_id INTEGER REFERENCES tbl_measurement (id) NOT NULL, CONSTRAINT pk_sensor_measurement PRIMARY KEY ( sensor_id, measurement_id ) ON CONFLICT ROLLBACK ); CREATE TABLE tbl_room_sensor_measurement ( id INTEGER NOT NULL, room_id INTEGER REFERENCES tbl_room (id) NOT NULL, sensor_id INTEGER NOT NULL, measurement_id INTEGER NOT NULL, FOREIGN KEY ( sensor_id, measurement_id ) REFERENCES tbl_sensor_measurement (sensor_id, measurement_id) ); — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <https://github.com/pawelsalawa/sqlitestudio/issues/3803#issuecomment-653814378>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ABGVUFPG7EYESSRUV3A5P3TRZ6M3JANCNFSM4OJ6SQBQ> .