ALTER TABLE - Error ORA-02270

Question

I Get this Error: ORA-02270 when i alter table for add foreign key, any idea?

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.

Thanks!

CREATE TABLE "SERMECOOP"."BENEFICIARIO" 
(   "IDBENEFICIARIO" NUMBER(9,0), 
"RUT" CHAR(9 BYTE), 
"APELLIDOPATERNO" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
"APELLIDOMATERNO" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
"NOMBRES" VARCHAR2(100 BYTE) NOT NULL ENABLE, 
"FECHANACIMIENTO" TIMESTAMP (6) NOT NULL ENABLE, 
"SEXO" NUMBER(9,0) NOT NULL ENABLE, 
"DIRECCION" VARCHAR2(256 BYTE) NOT NULL ENABLE, 
"IDCOMUNA" NUMBER(9,0), 
"IDCIUDAD" NUMBER(9,0), 
"IDREGION" NUMBER(9,0), 
"EMAILPERSONAL" VARCHAR2(128 BYTE), 
"INSTITUCIONDESALUD" NUMBER(9,0), 
"EMAILCOMERCIAL" VARCHAR2(128 BYTE), 
"TLFNOCELULAR" VARCHAR2(32 BYTE), 
"TLFNOPARTICULAR" VARCHAR2(32 BYTE), 
"TLFNOCOMERCIAL" VARCHAR2(32 BYTE), 
"BANCO" NUMBER(9,0), 
"CUENTACORRIENTE" VARCHAR2(32 BYTE), 
"TIPOCUENTACORRIENTE" NUMBER(9,0), 
"TIPOBENEFICIARIO" NUMBER(9,0), 
"FECHAINCORPORACION" TIMESTAMP (6), 
"RUTEJECUTIVO" CHAR(9 BYTE), 
"TIPOAFILIADO" NUMBER(9,0), 
"SEGUROCATASTROFICO" NUMBER(1,0) DEFAULT '0', 
"SEGUROVIDA" NUMBER(1,0) DEFAULT '0', 
PRIMARY KEY ("IDBENEFICIARIO"), 
FOREIGN KEY ("SEXO")
REFERENCES "SERMECOOP"."TIPOSEXO" ("IDTIPOSEXO") ENABLE, 
FOREIGN KEY ("IDCOMUNA")
REFERENCES "SERMECOOP"."COMUNAS" ("IDCOMUNA") ENABLE, 
FOREIGN KEY ("INSTITUCIONDESALUD")
REFERENCES "SERMECOOP"."INSTITUCIONESDESALUD" ("IDINSTITUCIONDESALUD") ENABLE, 
FOREIGN KEY ("BANCO")
REFERENCES "SERMECOOP"."BANCOS" ("IDBANCO") ENABLE, 
FOREIGN KEY ("TIPOCUENTACORRIENTE")
REFERENCES "SERMECOOP"."TIPOCUENTACORRIENTE" ("IDTIPOCC") ENABLE, 
FOREIGN KEY ("TIPOBENEFICIARIO")
REFERENCES "SERMECOOP"."TIPOSBENEFICIARIO" ("IDTIPOBENEFICIARIO") ENABLE, 
FOREIGN KEY ("TIPOAFILIADO")
REFERENCES "SERMECOOP"."TIPOAFILIADO" ("IDTIPOAFILIADO") ENABLE, 
FOREIGN KEY ("RUTEJECUTIVO")
REFERENCES "SERMECOOP"."USUARIOS" ("RUT") ENABLE
);

CREATE TABLE "SERMECOOP"."EMPLEADOS" 
("IDBENEFICIARIO" NUMBER(9,0), 
"EMPRESA" NUMBER(9,0), 
"FECHAINGRESOEMPRESA" TIMESTAMP (6) NOT NULL ENABLE, 
"SUCURSALEMPRESA" NUMBER(9,0), 
"SUCURSALENVIOINFORMACION" NUMBER(9,0), 
"SUELDOPACTADO" NUMBER(19,4), 
"FECHABAJA" TIMESTAMP (6), 
"TIPOCONTRATO" NUMBER(9,0), 
"TIPOJORNADAHORA" VARCHAR2(100 BYTE), 
"SINDICATO" VARCHAR2(100 BYTE), 
PRIMARY KEY ("IDBENEFICIARIO", "EMPRESA")
FOREIGN KEY ("IDBENEFICIARIO")
REFERENCES "SERMECOOP"."BENEFICIARIO" ("IDBENEFICIARIO") ENABLE, 
FOREIGN KEY ("EMPRESA")
REFERENCES "SERMECOOP"."EMPRESA" ("IDEMPRESA") ENABLE, 
FOREIGN KEY ("SUCURSALEMPRESA")
REFERENCES "SERMECOOP"."SUCURSAL" ("IDSUCURSAL") ENABLE, 
FOREIGN KEY ("SUCURSALENVIOINFORMACION")
REFERENCES "SERMECOOP"."SUCURSAL" ("IDSUCURSAL") ENABLE, 
FOREIGN KEY ("TIPOCONTRATO")
REFERENCES "SERMECOOP"."TIPOSCONTRATOS" ("IDTIPOCONTRATO") ENABLE);

ALTER TABLE BENEFICIARIO
ADD FOREIGN KEY (IDBENEFICIARIO)
REFERENCES EMPLEADOS(IDBENEFICIARIO);

check this link.. http://stackoverflow.com/questions/10802212/oracle-ora-02270-no-matching-unique-or-primary-key-for-this-column-list-erro — Kevin Andrid, May 04 '15 at 10:28

score 0 · Answer 1 · answered May 04 '15 at 10:22

0

please, check are all your referenced fields have PK field

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

answered May 04 '15 at 10:22

score 0 · Answer 2 · answered May 04 '15 at 10:57

You have given Foreign Key to particular column which can not refer specified Primary Key. So You need to simply see for which column field you to assign FK and check if another table Holds Such Primary key or not. Like

APELLIDOPATERNO this is you Fk then there will be some field which hold this values as PK APELLIDOPATERNO

score 0 · Accepted Answer · answered May 04 '15 at 14:47

You declared "IDBENEFICIARIO" and "EMPRESA" as combined primary key ....

CREATE TABLE "SERMECOOP"."EMPLEADOS"  
("IDBENEFICIARIO" NUMBER(9,0), 
 ...
 "SINDICATO" VARCHAR2(100 BYTE), 
  PRIMARY KEY ("IDBENEFICIARIO", "EMPRESA")
  FOREIGN KEY ("IDBENEFICIARIO")

... so you cannot have a foreign key only pointing to IDBENEFICIARIO:

ALTER TABLE BENEFICIARIO
  ADD FOREIGN KEY (IDBENEFICIARIO)
      REFERENCES EMPLEADOS(IDBENEFICIARIO);

In addition, you missed the comma behind ...

PRIMARY KEY ("IDBENEFICIARIO", "EMPRESA"),

ALTER TABLE - Error ORA-02270

3 Answers3