Create foreign key against non-PK column set

Question

I want to create a foreign key against two columns that are not part of the parent table's primary key (I understand that's possible) but I always hit this wall:

ORA-02270 no matching unique or primary key for this column-list

Parent table is this:

CREATE TABLE FOO_PERSONAL (
    FOO_IMPORTACION_ID NUMBER(*,0) NOT NULL,
    TALLER_ID NUMBER(*,0) NOT NULL,
    PERSONAL_ID NUMBER(*,0) NOT NULL,

    NOMBRE_PERSONA VARCHAR2(255 CHAR) NOT NULL,

    CONSTRAINT FOO_PERSONAL_PK PRIMARY KEY (FOO_IMPORTACION_ID, TALLER_ID, PERSONAL_ID)
);

-- Not the right syntax?
CREATE UNIQUE INDEX FOO_PERSONAL_UQ1 ON FOO_PERSONAL (FOO_IMPORTACION_ID, PERSONAL_ID);

... and child table is:

CREATE TABLE FOO_CARGO (
    FOO_IMPORTACION_ID NUMBER(*,0) NOT NULL,
    PERSONAL_ID NUMBER(*,0) NOT NULL,
    CARGO_ID NUMBER(*,0) NOT NULL,

    CARGO VARCHAR2(255 CHAR) NOT NULL,

    CONSTRAINT FOO_CARGO_PK PRIMARY KEY (FOO_IMPORTACION_ID, PERSONAL_ID, CARGO_ID),

    CONSTRAINT FOO_CARGO_FK1 FOREIGN KEY (FOO_IMPORTACION_ID, PERSONAL_ID)
    REFERENCES FOO_PERSONAL (FOO_IMPORTACION_ID, PERSONAL_ID)
    -- ^ ORA-02270
    ON DELETE CASCADE
    ENABLE
);

What bit am I missing?

Answer 1

You need a UNIQUE constraint, not just a unique index:

CREATE TABLE FOO_PERSONAL (
    FOO_IMPORTACION_ID NUMBER(*,0) NOT NULL,
    TALLER_ID NUMBER(*,0) NOT NULL,
    PERSONAL_ID NUMBER(*,0) NOT NULL,

    NOMBRE_PERSONA VARCHAR2(255 CHAR) NOT NULL,

    CONSTRAINT FOO_PERSONAL_PK PRIMARY KEY (FOO_IMPORTACION_ID, TALLER_ID, PERSONAL_ID),
    CONSTRAINT FOO_PERSONAL_UQ1 UNIQUE (FOO_IMPORTACION_ID, PERSONAL_ID)
);