CODEWITHSUNDEEP
sqloracle-databaseforeign-keyskeyconstraints
SJ19
SJ19

Reputation: 2123

Oracle SQL Database error: "no matching unique or primary key for this column-list"

I'm trying to set up a database in Oracle sql developer, I've got these 3 tables. I need the table "GuyAddress" to have 3 primary keys, which are all foreign keys as well. This is where I'm running into an error which I can't get my head around.

CREATE TABLE Guy
( 
    id NUMBER(10)   PRIMARY KEY, 
    name            VARCHAR(50)
);

CREATE TABLE Address
(
    zipcode         VARCHAR(6),
    "number"        NUMBER(10),
    CONSTRAINT PK_Address PRIMARY KEY(zipcode, "number")
);

CREATE TABLE GuyAddress
(
    Guy_id          NUMBER(10),
    Address_zipcode VARCHAR(6),
    Address_number  NUMBER(10),
    CONSTRAINT FK_GuyAddress_Guy_id FOREIGN KEY(Guy_id) REFERENCES Guy(id),
    CONSTRAINT FK_GuyAddress_Address_zipcode FOREIGN KEY(Address_zipcode) REFERENCES Address(zipcode),
    CONSTRAINT FK_GuyAddress_Address_number FOREIGN KEY(Address_number) REFERENCES Address("number"),
    CONSTRAINT PK_GuyAddress PRIMARY KEY(Guy_id, Address_zipcode, Address_number)
);

This is the error, hopefully someone can spot the mistake I made because I can't...

Error starting at line : 18 in command -
CREATE TABLE "GuyAddress"
  (
    Guy_id          NUMBER(10),
    Address_zipcode VARCHAR(6),
    Address_number  NUMBER(10),
    CONSTRAINT FK_GuyAddress_Guy_id FOREIGN KEY(Guy_id) REFERENCES Guy(id),
    CONSTRAINT FK_GuyAddress_Address_zipcode FOREIGN KEY(Address_zipcode) REFERENCES Address(zipcode),
    CONSTRAINT FK_GuyAddress_Address_number FOREIGN KEY(Address_number) REFERENCES Address("number"),
    CONSTRAINT PK_GuyAddress PRIMARY KEY(Guy_id, Address_zipcode, Address_number)
  )
Error report -
SQL Error: ORA-02270: no matching unique or primary key for this column-list
02270. 00000 -  "no matching unique or primary key for this column-list"
*Cause:    A REFERENCES clause in a CREATE/ALTER TABLE statement
           gives a column-list for which there is no matching unique or primary
           key constraint in the referenced table.
*Action:   Find the correct column names using the ALL_CONS_COLUMNS
           catalog view

Thanks!

Upvotes: 1

Views: 929

Answers (1)

Boneist
Boneist

Reputation: 23588

You don't need separate foreign keys for each column in the referenced table's primary key - you can have multiple columns in a foreign key, e.g.:

CREATE TABLE Guy
( 
    id NUMBER(10)   PRIMARY KEY, 
    name            VARCHAR(50)
);

CREATE TABLE Address
(
    zipcode         VARCHAR(6),
    address_number  NUMBER(10),
    CONSTRAINT PK_Address PRIMARY KEY(zipcode, address_number)
);

CREATE TABLE GuyAddress
(
    Guy_id          NUMBER(10),
    Address_zipcode VARCHAR(6),
    Address_number  NUMBER(10),
    CONSTRAINT FK_GuyAddress_Guy_id FOREIGN KEY(Guy_id) REFERENCES Guy(id),
    CONSTRAINT FK_GuyAddress_Address FOREIGN KEY(Address_zipcode, Address_number) REFERENCES Address(zipcode,address_number),
    CONSTRAINT PK_GuyAddress PRIMARY KEY(Guy_id, Address_zipcode, Address_number)
);

Note that I've updated your address.number column to be address.address_number, as it's not recommended that you use a column based on a keyword. Using doublequotes to get around this (and also enforce case sensitivity) is not recommended either; you'll have to remember to use them every time you reference that column!

As an aside, I assume that your address table has other columns? Because as things stand, the address table is pointless and could be skipped!

Upvotes: 2

Related Questions