Search code examples
sqloracle-databaseoracle-apexcreate-tableoracle-apex-5

no matching unique or primary key for this column-list. Im not sure how to solve it for my case

I've been trying to create this table in my database. We were told to use Oracle-Apex for creating the database. So I keep getting this error that I cant solve:

this error

If I remove the last line of the code, it creates the table fine without any errors. Here are screenshots of the other tables being referenced here:

Company Table

Company Table

Branch Table

Branch Table

IDK if this is a rookie mistake, I only learnt apex/sql in like an hour and went off to make the database. Thank you for helping me! :)

Solution

  • The column(s) referenced by a foreign key must have a unique index in the source table (or they must be the primary key of that table). Your code fails because of the following foreign key declaration, where the target is not unique:

    foreign key (BranchNo) references Branch(BranchNo)

    Here, I think that you want a compound foreign key that references the primary key of Branch rather than two different keys. Branch(CCode) references Company(CCode) already so there is no need to put that relationship in the Equipment table.

    create table Equipment(
    CCode int,
    BranchNo int,
    EquipNo it,
    Description varchar2(50),
    NumberOfEquip int,
    primary key(CCode, BranchNo, EquipNo),
    foreign key (CCode, BranchNo) references Branch(CCode, BranchNo)
);