Can different foreign keys be referenced for the same attribute field?

Navigation
6

I'm working on a 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 aims 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 you refer to two foreign keys for the same attribute field? I am not entirely sure that I am doing it correctly. Is there an alternative to, in the same way, not having to depend on two different fields (one for each of the references)?

When trying to insert in 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 help me out.

Thank you very much.

    
asked by Mario Ruano 28.12.2018 в 03:12
source

2 answers

4

If you can reference two foreign keys for the same field.

Example: 

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

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

INSERT INTO EVENTOS VALUES (1,'Evento1','Ubicación1');
INSERT INTO EVENTOS VALUES (2,'Evento2','Ubicación2');

INSERT INTO PROGRAMASDEPORTIVOS VALUES (1,'Programa1','Ubicación1');
INSERT INTO PROGRAMASDEPORTIVOS VALUES (2,'Programa2','Ubicación2');

If I now insert values in the table commissioned with foregin keys that already exist as primary key in the reference tables, there is no problem. 

INSERT INTO ENCARGADOS VALUES (1,1,1);
INSERT INTO ENCARGADOS VALUES (2,2,1);

But if I try to include a record that has a OID_Proj that does not exist as primary key in the reference tables 

INSERT INTO ENCARGADOS VALUES (3,3,1);
  

ORA-02291: integrity constraint (USER_4_A2827.FK_EVENTOS) violated -   parent key not found

The error you get in your case tells you that FK_progdep does not exist as primary key in table PROGRAMASDEPORTIVOS in the insert you are trying to make.

    
answered by 28.12.2018 / 12:48
source
0

If I understand correctly, and sports events and programs are disjoint sets (what is in one is not in another and vice versa), this design would always fail, because when you try to insert any record in the detail table you the reference in at least one of the two master tables would be missing, by definition. Go back to your model ER and think again if events and sports programs are not the same entity, and therefore should not be the same table (the fields are the same gives you a clue in that direction)

    
answered by 31.12.2018 в 15:55
Difference between std :: uninitialized_copy and std :: copy? Problem when defining time zone using tzinfo on datetime using pytz.timezone ()