I am working on some Oracle SQL Express 11g code . So far I have these two tables:
CREATE TABLE EVENTOS (
OID_Proj INTEGER PRIMARY KEY NOT NULL,
nombre VARCHAR2(50) NOT NULL,
ubicacion VARCHAR2(50) NOT NULL
);
CREATE TABLE PROGRAMASDEPORTIVOS (
OID_Proj INTEGER PRIMARY KEY NOT NULL,
nombre VARCHAR2(50) NOT NULL,
ubicacion VARCHAR2(50) NOT NULL
);
As can be seen, the primary key of both tables takes the same name OID_Proj
, since the intention is that both tables share the same sequence as primary key.
CREATE SEQUENCE SEC_Proj INCREMENT BY 1 START WITH 1;
Well, the problem arises in the following table (which is intended to reference both primary keys for a single attribute field):
CREATE TABLE ENCARGADOS (
OID_RP INTEGER PRIMARY KEY NOT NULL,
OID_Proj INTEGER NOT NULL,
OID_Coord INTEGER NOT NULL,
CONSTRAINT FK_progdep FOREIGN KEY (OID_Proj) REFERENCES PROGRAMASDEPORTIVOS(OID_Proj) ON DELETE CASCADE,
CONSTRAINT FK_eventos FOREIGN KEY (OID_Proj) REFERENCES EVENTOS(OID_Proj) ON DELETE CASCADE,
FOREIGN KEY (OID_Coord) REFERENCES COORDINADORES
);
Is this correct? Can two foreign keys be referenced for the same attribute field? I'm not entirely sure I'm doing it correctly. Is there an alternative to similarly not having to rely on two different fields (one for each of the references)?
When trying to insert into table ENCARGADOS
, Oracle SQL Developer throws this error:
Informe de error -
ORA-02291: integrity constraint (MRUANO.FK_PROGDEP) violated - parent key not found
ORA-06512: at "MRUANO.REGISTRAR_EVENTO", line 8
ORA-06512: at line 1
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause: A foreign key value has no matching primary key value.
*Action: Delete the foreign key or add a matching primary key.
I hope someone can give me a hand.
Thank you very much.