How to add a composite foreign key in liquibase?

user_2999900 0
Viewed 4697

I've been struggling for some time now to figure out a way to create a composite foreign key in liquibase.

I have a table A which has a composite PK, let's say (id1, id2). I'm trying to make another table B, in which the A.PK is mapped as a FK.

I'm using liquibase with YAML and something doesn't seem to add up.

I've tried adding the FK when creating the table (so in the column tag)

- column:
    name: id1_id2
    type: int
    constraints:
       nullable: false
       foreignKeyName: fk_id1_id2
       references: A(id1, id2)

Unfortunately this syntax returns an error:

Caused by: java.sql.SQLSyntaxErrorException: ORA-02256: number of referencing columns must match referenced columns

Another thing that I've tried is creating the table first, with the column for the desired FK and try to add a FK constraint on that column. This doesn't throw any error but it does nothing (also the log for LB says "empty" in the description)

changes:
  - addForeignKeyContraint:
    baseColumnNames: id1, id2
    baseTableName: B
    constraintName: fb_id1_id2
    referencedColumnNames: id1, id2
    referencedTableName: A

Any help would be much appreciated.

Thanks

liquibase oracle
2 Answers

Have you tried addForeignKeyConstraint? Something like this:

- changeSet:
    id: 1
    author: you
    changes:
    - addForeignKeyConstraint:
        baseColumnNames: id1, id2
        baseTableName: tableB
        constraintName: FK_tableB_tableA
        referencedColumnNames: id1, id2
        referencedTableName: tableA
user_27960080

I don't use Liquibase, but here's how it is supposed to look like, as far as Oracle is concerned: if you want to create a composite foreign key (in the detail table), then it has to reference a composite primary key (in the master table).

Have a look at this example:

SQL> create table master
  2    (id_1 number,
  3     id_2 number,
  4     constraint pk_mas primary key (id_1, id_2));

Table created.

SQL> create table detail
  2    (id_det number constraint pk_det primary key,
  3     --
  4     id_1   number,
  5     id_2   number,
  6     constraint fk_det_mas foreign key (id_1, id_2) references master (id_1, id_2));

Table created.

SQL>

It just wouldn't work otherwise; that's why you got the error

ORA-02256: number of referencing columns must match referenced columns

because your detail table contained a single column (id1_id2) and tried to reference two columns in table A (id1, id2).

user_90979060
Related