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.
If you can reference two
foreign keys
for the same field.Example:
If now I insert values in the responsible table with
foregin keys
which they already exist asprimary key
in the reference tables, there is no problem.But if I try to include a record that has a
OID_Proj
that doesn't exist likeprimary key
in the reference tablesThe error you get in your case tells you that
FK_progdep
it does not exist asprimary key
in the tablePROGRAMASDEPORTIVOS
in the insert you are trying to perform.If I understand correctly, and sports events and programs are disjoint sets (what is in one is not in the other, and vice versa), this design would always fail, because trying to insert any record in the detail table would miss the reference in at least one of the two master tables, by definition. Go back to your ER model and think again if events and sports programs are not the same entity, and therefore should not be the same table (that the fields are the same gives you a clue in that direction)
As "Carmen" tells you, it can be done, but it is a design error, already indicated by Guillermo García, which will cause errors.
Answering your question is this correct? Can two foreign keys be referenced for the same attribute field? the question is not whether you can, but the complication you cause. I propose you to maintain the relationship between the SPORTS PROGRAMS and EVENTS tables, you should have your own primary and different key in each of these two tables, and the key is a third table, for example INDEX, which contains as a foreign key the references to each of the tables, and its own id, so that when you want to retrieve the information of SPORTS PROGRAMS and EVENTS, from MANAGERS, you reference the identifier of the INDEX table.
At the design level you can see it in this image
On the other hand, I understand that you want to insert in MANAGERS without existing in SPORTS PROGRAMS and EVENTS, you can achieve this with a cascade insert, in Oracle by means of a trigger where if id_events null and id_programs... null it will generate the inserts for each of The tables. Or if you use the INDEX proposal, if it doesn't exist, create the insert.