Ndtyjky

I create the table "Funcionario"
but then the tables "Viagem" and "Encomenda"
show me this error. I didn't understand why, can somebody help me? i can share all the script if you want to see all.
The error that appears is :

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

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.

[EDIT] SCRIPT

-- Criar Tabela Zona Geografica



CREATE TABLE ZonaGeografica(

id_zona_geo INTEGER CONSTRAINT pk_ZonaGeografica_id_zona_geo PRIMARY KEY,

latitude INTEGER NOT NULL,

longitude INTEGER NOT NULL

);



-- Criar Tabela Armazem



CREATE TABLE Armazem(

cod_armazem INTEGER CONSTRAINT pk_Armazem_cod_armazem PRIMARY KEY,

id_zona_geo INTEGER NOT NULL,

nome VARCHAR(40) NOT NULL,

morada VARCHAR(50) NOT NULL,

CONSTRAINT fk_ZonaGeografica_id_zona_geo FOREIGN KEY (id_zona_geo) REFERENCES ZonaGeografica(id_zona_geo)

);



-- Criar Tabela TipoVeiculo



CREATE TABLE TipoVeiculo(

tipo_veiculo VARCHAR(20) CONSTRAINT pk_TipoVeiculo_tipo_veiculo PRIMARY KEY,

capacidade_volume INTEGER NOT NULL,

capacidade_peso INTEGER NOT NULL

);



-- Criar Tabela Veiculo



CREATE TABLE Veiculo(

cod_veiculo INTEGER NOT NULL,

tipo_veiculo VARCHAR(20) NOT NULL,

matricula VARCHAR(8) NOT NULL,

marca VARCHAR(20) NOT NULL,

modelo VARCHAR(5) NOT NULL,

nr_apolice INTEGER NOT NULL,

nr_quilometros INTEGER NOT NULL,

CONSTRAINT pk_Veiculo_cod_veiculo_tipo_veiculo PRIMARY KEY(cod_veiculo, tipo_veiculo),

CONSTRAINT fk_Veiculo_tipo_veiculo FOREIGN KEY (tipo_veiculo) REFERENCES TipoVeiculo(tipo_veiculo),

CONSTRAINT ck_Veiculo_matricula CHECK(REGEXP_LIKE(matricula ,'[0-9]{2}-[A-Z]{2}-[0-9]{2}|[0-9]{2}-[0-9]{2}-[A-Z]{2}|[A-Z]{2}-[0-9]{2}-[0-9]{2}'))

);



-- Criar Tabela Funcionario



CREATE TABLE Funcionario(

id_func INTEGER CONSTRAINT pk_Funcionario_id_func PRIMARY KEY,

id_tipo INTEGER NOT NULL,

cod_armazem INTEGER NOT NULL,

cod_supervisor INTEGER NOT NULL,

cc INTEGER NOT NULL CONSTRAINT ck_Funcionario_cc CHECK(REGEXP_LIKE(cc ,'[0-9]{8}-[0-9]{1}-[A-Z]{2}[0-9]{1}')),

nome_func VARCHAR(40) NOT NULL,

morada_func VARCHAR(40) NOT NULL,

nif_func INTEGER NOT NULL UNIQUE CONSTRAINT ck_Funcionario_nif_func CHECK(REGEXP_LIKE(nif_func ,'[0-9]{7}')),

salario_mensal NUMERIC(*,2) NOT NULL,

CONSTRAINT fk_Funcionario_id_tipo FOREIGN KEY (id_tipo) REFERENCES Categoria(id_tipo),

CONSTRAINT fk_Funcionario_cod_armazem FOREIGN KEY (cod_armazem) REFERENCES Armazem(cod_armazem),

CONSTRAINT fk_Funcionario_cod_supervisor FOREIGN KEY (cod_supervisor) REFERENCES Funcionario(id_func)

);



-- Criar Tabela Categoria



CREATE TABLE Categoria(

id_tipo INTEGER CONSTRAINT pk_Categoria_id_tipo PRIMARY KEY,

tipo VARCHAR(20) NOT NULL

);



-- Criar Tabela Viagem





CREATE TABLE Viagem(

nr_viagem INTEGER CONSTRAINT pk_Viagem_nr_viagem PRIMARY KEY,

id_tipo INTEGER NOT NULL,

id_func INTEGER NOT NULL,

cod_veiculo INTEGER NOT NULL,

tipo_veiculo VARCHAR(20) NOT NULL,

data_partida DATE NOT NULL,



CONSTRAINT fk_Viagem_id_tipo FOREIGN KEY (id_tipo) REFERENCES Categoria(id_tipo),

CONSTRAINT fk_Viagem_id_func FOREIGN KEY (id_func) REFERENCES Funcionario(id_func),

CONSTRAINT fk_Viagem_cod_veiculo FOREIGN KEY (cod_veiculo) REFERENCES Veiculo(cod_veiculo),

CONSTRAINT fk_Viagem_tipo_veiculo FOREIGN KEY (tipo_veiculo) REFERENCES TipoVeiculo(tipo_veiculo)

);





-- Criar Tabela Encomenda



CREATE TABLE Encomenda(

id_encomenda INTEGER CONSTRAINT pk_Encomenda_id_encomenda PRIMARY KEY,

cod_armazem INTEGER NOT NULL,

cod_veiculo INTEGER NOT NULL,

nr_viagem INTEGER NOT NULL,

CONSTRAINT fk_Encomenda_nr_viagem FOREIGN KEY (nr_viagem) REFERENCES Viagem(nr_viagem),

id_func INTEGER NOT NULL,

CONSTRAINT fk_Encomenda_cod_armazem FOREIGN KEY (cod_armazem) REFERENCES Armazem(cod_armazem),

CONSTRAINT fk_Encomenda_cod_veiculo FOREIGN KEY (cod_veiculo) REFERENCES Veiculo(cod_veiculo),

CONSTRAINT fk_Encomenda_id_func FOREIGN KEY (id_func) REFERENCES Funcionario(id_func)

);

Here's what's wrong

CONSTRAINT fk_Viagem_cod_veiculo FOREIGN KEY (cod_veiculo) REFERENCES Veiculo(cod_veiculo)

For table Viagem you have this FOREIGN KEY which is referring to only a part of the composite PRIMARY KEY in Veiculo (cod_veiculo, tipo_veiculo)

same with this for Encomenda

CONSTRAINT fk_Encomenda_cod_veiculo FOREIGN KEY(cod_veiculo) REFERENCES Veiculo(cod_veiculo)

Fix your design such that a FOREIGN KEY combination matches with a UNIQUE KEY/PRIMARY KEY in the referenced tables.

The error message is fairly clear (or at least better than some); you have a foreign key which tries to reference column(s) that don't form a unique or primary key in the parent table.

In Viagem you have:

CONSTRAINT fk_Viagem_cod_veiculo FOREIGN KEY (cod_veiculo) REFERENCES Veiculo(cod_veiculo),

but the primary key in Veiculo is the combination of two columns:

CONSTRAINT pk_Veiculo_cod_veiculo_tipo_veiculo PRIMARY KEY(cod_veiculo, tipo_veiculo),

You can't reference a single column from that key, as that single column won't be unique on its own, leading to ambiguity. So the obvious (but probably wrong) fix is to change Viagem to reference both columns:

CONSTRAINT fk_Viagem_cod_veiculo FOREIGN KEY (cod_veiculo, tipo_veiculo)

  REFERENCES Veiculo(cod_veiculo, tipo_veiculo),

(If you did that then wouldn't really need the fk_Viagem_tipo_veiculo constraint as well, since the tipo_veiculo is part of the FK to Veiculo, and that table already has its own FK to TipoVeiculo.)

But your Encomenda table has the same problem; you have:

CONSTRAINT fk_Encomenda_cod_veiculo FOREIGN KEY (cod_veiculo) REFERENCES Veiculo(cod_veiculo),

there too. But for that table you don't have the id_tipo column, so you can't include that in the FK.

Which points to the more likely solution, via a question - why does Veiculo include tipo_veiculo in its PK at all? It looks like the cod_veiculo should be unique on its own; in which case change that PK to just:

CONSTRAINT pk_Veiculo_cod_veiculo_tipo_veiculo PRIMARY KEY(cod_veiculo),

and leave both Viagem and Encomenda as you already had them, with all their Fk referencing a single column each. (Although you may want to consider whether Viagem actually needs the id_tipo column at all, since yuo can get that from Veiculo via the linked cod_veiculo...)

If you really want to keep that PK as it is then you would have to add a seperate unique key for just cod_veiculo, which would then satisfy fk_Encomenda_cod_veiculo - but that looks wrong.