Why does this error appear me on sql developer









up vote
-1
down vote

favorite












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)
);









share|improve this question























  • Put the code as text in the question. Your images are too small to read.
    – Gordon Linoff
    Nov 10 at 16:16










  • We don't know how "veiculo" and "tipoveiculo" tables look like, so - difficult to guess what's wrong. Do post ALL CREATE TABLE statements. BTW, there's no need to declare a column NOT NULL if it is a primary key.
    – Littlefoot
    Nov 10 at 16:22










  • The error message is quite clear, you cannot reference as foreign key a column that is not unique. If they are not PRIMARY KEYs, then make them UNIQUE.
    – Yann39
    Nov 10 at 16:27











  • @Littlefoot there it is
    – Luis De Matos
    Nov 10 at 16:29










  • Kaushik has just said what's wrong; have a look at his answer.
    – Littlefoot
    Nov 10 at 18:06














up vote
-1
down vote

favorite












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)
);









share|improve this question























  • Put the code as text in the question. Your images are too small to read.
    – Gordon Linoff
    Nov 10 at 16:16










  • We don't know how "veiculo" and "tipoveiculo" tables look like, so - difficult to guess what's wrong. Do post ALL CREATE TABLE statements. BTW, there's no need to declare a column NOT NULL if it is a primary key.
    – Littlefoot
    Nov 10 at 16:22










  • The error message is quite clear, you cannot reference as foreign key a column that is not unique. If they are not PRIMARY KEYs, then make them UNIQUE.
    – Yann39
    Nov 10 at 16:27











  • @Littlefoot there it is
    – Luis De Matos
    Nov 10 at 16:29










  • Kaushik has just said what's wrong; have a look at his answer.
    – Littlefoot
    Nov 10 at 18:06












up vote
-1
down vote

favorite









up vote
-1
down vote

favorite











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)
);









share|improve this question















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)
);






sql oracle create-table






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 10 at 17:58









Kaushik Nayak

16.2k41128




16.2k41128










asked Nov 10 at 16:14









Luis De Matos

71




71











  • Put the code as text in the question. Your images are too small to read.
    – Gordon Linoff
    Nov 10 at 16:16










  • We don't know how "veiculo" and "tipoveiculo" tables look like, so - difficult to guess what's wrong. Do post ALL CREATE TABLE statements. BTW, there's no need to declare a column NOT NULL if it is a primary key.
    – Littlefoot
    Nov 10 at 16:22










  • The error message is quite clear, you cannot reference as foreign key a column that is not unique. If they are not PRIMARY KEYs, then make them UNIQUE.
    – Yann39
    Nov 10 at 16:27











  • @Littlefoot there it is
    – Luis De Matos
    Nov 10 at 16:29










  • Kaushik has just said what's wrong; have a look at his answer.
    – Littlefoot
    Nov 10 at 18:06
















  • Put the code as text in the question. Your images are too small to read.
    – Gordon Linoff
    Nov 10 at 16:16










  • We don't know how "veiculo" and "tipoveiculo" tables look like, so - difficult to guess what's wrong. Do post ALL CREATE TABLE statements. BTW, there's no need to declare a column NOT NULL if it is a primary key.
    – Littlefoot
    Nov 10 at 16:22










  • The error message is quite clear, you cannot reference as foreign key a column that is not unique. If they are not PRIMARY KEYs, then make them UNIQUE.
    – Yann39
    Nov 10 at 16:27











  • @Littlefoot there it is
    – Luis De Matos
    Nov 10 at 16:29










  • Kaushik has just said what's wrong; have a look at his answer.
    – Littlefoot
    Nov 10 at 18:06















Put the code as text in the question. Your images are too small to read.
– Gordon Linoff
Nov 10 at 16:16




Put the code as text in the question. Your images are too small to read.
– Gordon Linoff
Nov 10 at 16:16












We don't know how "veiculo" and "tipoveiculo" tables look like, so - difficult to guess what's wrong. Do post ALL CREATE TABLE statements. BTW, there's no need to declare a column NOT NULL if it is a primary key.
– Littlefoot
Nov 10 at 16:22




We don't know how "veiculo" and "tipoveiculo" tables look like, so - difficult to guess what's wrong. Do post ALL CREATE TABLE statements. BTW, there's no need to declare a column NOT NULL if it is a primary key.
– Littlefoot
Nov 10 at 16:22












The error message is quite clear, you cannot reference as foreign key a column that is not unique. If they are not PRIMARY KEYs, then make them UNIQUE.
– Yann39
Nov 10 at 16:27





The error message is quite clear, you cannot reference as foreign key a column that is not unique. If they are not PRIMARY KEYs, then make them UNIQUE.
– Yann39
Nov 10 at 16:27













@Littlefoot there it is
– Luis De Matos
Nov 10 at 16:29




@Littlefoot there it is
– Luis De Matos
Nov 10 at 16:29












Kaushik has just said what's wrong; have a look at his answer.
– Littlefoot
Nov 10 at 18:06




Kaushik has just said what's wrong; have a look at his answer.
– Littlefoot
Nov 10 at 18:06












2 Answers
2






active

oldest

votes

















up vote
2
down vote













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.






share|improve this answer



























    up vote
    0
    down vote













    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.






    share|improve this answer






















      Your Answer






      StackExchange.ifUsing("editor", function ()
      StackExchange.using("externalEditor", function ()
      StackExchange.using("snippets", function ()
      StackExchange.snippets.init();
      );
      );
      , "code-snippets");

      StackExchange.ready(function()
      var channelOptions =
      tags: "".split(" "),
      id: "1"
      ;
      initTagRenderer("".split(" "), "".split(" "), channelOptions);

      StackExchange.using("externalEditor", function()
      // Have to fire editor after snippets, if snippets enabled
      if (StackExchange.settings.snippets.snippetsEnabled)
      StackExchange.using("snippets", function()
      createEditor();
      );

      else
      createEditor();

      );

      function createEditor()
      StackExchange.prepareEditor(
      heartbeatType: 'answer',
      convertImagesToLinks: true,
      noModals: true,
      showLowRepImageUploadWarning: true,
      reputationToPostImages: 10,
      bindNavPrevention: true,
      postfix: "",
      imageUploader:
      brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
      contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
      allowUrls: true
      ,
      onDemand: true,
      discardSelector: ".discard-answer"
      ,immediatelyShowMarkdownHelp:true
      );



      );













       

      draft saved


      draft discarded


















      StackExchange.ready(
      function ()
      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53240858%2fwhy-does-this-error-appear-me-on-sql-developer%23new-answer', 'question_page');

      );

      Post as a guest















      Required, but never shown

























      2 Answers
      2






      active

      oldest

      votes








      2 Answers
      2






      active

      oldest

      votes









      active

      oldest

      votes






      active

      oldest

      votes








      up vote
      2
      down vote













      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.






      share|improve this answer
























        up vote
        2
        down vote













        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.






        share|improve this answer






















          up vote
          2
          down vote










          up vote
          2
          down vote









          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.






          share|improve this answer












          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.







          share|improve this answer












          share|improve this answer



          share|improve this answer










          answered Nov 10 at 18:04









          Kaushik Nayak

          16.2k41128




          16.2k41128






















              up vote
              0
              down vote













              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.






              share|improve this answer


























                up vote
                0
                down vote













                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.






                share|improve this answer
























                  up vote
                  0
                  down vote










                  up vote
                  0
                  down vote









                  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.






                  share|improve this answer














                  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.







                  share|improve this answer














                  share|improve this answer



                  share|improve this answer








                  edited Nov 10 at 18:13

























                  answered Nov 10 at 18:06









                  Alex Poole

                  127k6100173




                  127k6100173



























                       

                      draft saved


                      draft discarded















































                       


                      draft saved


                      draft discarded














                      StackExchange.ready(
                      function ()
                      StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53240858%2fwhy-does-this-error-appear-me-on-sql-developer%23new-answer', 'question_page');

                      );

                      Post as a guest















                      Required, but never shown





















































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown

































                      Required, but never shown














                      Required, but never shown












                      Required, but never shown







                      Required, but never shown







                      這個網誌中的熱門文章

                      How to read a connectionString WITH PROVIDER in .NET Core?

                      In R, how to develop a multiplot heatmap.2 figure showing key labels successfully

                      Museum of Modern and Contemporary Art of Trento and Rovereto