0

I'm planning a database with Oracle and I have problems in table creation. I have a specialization construct and one of the child entities has a relation with another entity. This is my EER schema: https://i.stack.imgur.com/z7pb0.png

I tried to implement this part of database as follows:

CREATE TABLE Prenotazione(
Num_Protocollo          NUMBER(8, 0),
Num_Tentativi           NUMBER(2, 0),
Data_Ora_Previste       DATE NOT NULL,
Tipo_Prenotazione       VARCHAR(7) CHECK (Tipo_Prenotazione IN ('SINGOLA','DOPPIA')) NOT NULL,

PRIMARY KEY (Num_Protocollo, Num_Tentativi)
);



CREATE TABLE Anamnesi (
Num_Protocollo                          NUMBER(8, 0) NOT NULL,
Num_Tentativi                           NUMBER(2, 0) NOT NULL,
Data_Ora_Controllo                      DATE NOT NULL,
Num_Iscrizione_Ordine_Medici            NUMBER(10, 0) NOT NULL,

PRIMARY KEY (Num_Protocollo, Num_Tentativi, Data_Ora_Controllo),

CONSTRAINT Foreign_Key_Anamnesi1 FOREIGN KEY (Num_Protocollo, Num_Tentativi) REFERENCES Prenotazione(Num_Protocollo, Num_Tentativi),
CONSTRAINT Foreign_Key_Anamnesi2 FOREIGN KEY (Num_Iscrizione_Ordine_Medici) REFERENCES Operatore_Sanitario_Responsabile(Num_Iscrizione_Ordine_Medici) /* Ignore this */
);



CREATE TABLE Idoneo(
Num_Protocollo                          NUMBER(8, 0) NOT NULL,
Num_Tentativi                           NUMBER(2, 0) NOT NULL,
Data_Ora_Controllo                      DATE NOT NULL,

CONSTRAINT Foreign_Key_Idoneo FOREIGN KEY (Num_Protocollo, Num_Tentativi, Data_Ora_Controllo) REFERENCES Anamnesi(Num_Protocollo, Num_Tentativi, Data_Ora_Controllo)
);



CREATE TABLE Vaccinazione (
Data_Ora_Effettiva          DATE,
Braccio_Inoculazione        CHAR(1) CHECK (Braccio_Inoculazione IN ('L', 'R')) NOT NULL,
Num_Carta                   NUMBER(8, 0),
Nome_Luogo                  VARCHAR(30) NOT NULL,
Distretto_Luogo             VARCHAR(20) NOT NULL,
Num_Lotto                   VARCHAR(10) NOT NULL,
Nome_Farmaco                VARCHAR(30) NOT NULL,

Num_Protocollo              NUMBER(8, 0) NOT NULL,
Num_Tentativi               NUMBER(2, 0) NOT NULL,
Data_Ora_Controllo          DATE NOT NULL,

PRIMARY KEY (Num_Carta, Data_Ora_Effettiva),

CONSTRAINT Foreign_Key_Vaccinazione1 FOREIGN KEY (Num_Protocollo, Num_Tentativi, Data_Ora_Controllo) REFERENCES Idoneo(Num_Protocollo, Num_Tentativi, Data_Ora_Controllo),
CONSTRAINT Foreign_Key_Vaccinazione2 FOREIGN KEY (Num_Carta) REFERENCES Card_Vaccinato(Num_Carta), /* Ignore this */
CONSTRAINT Foreign_Key_Vaccinazione3 FOREIGN KEY (Nome_Luogo, Distretto_Luogo) REFERENCES Luogo(Nome, Distretto), /* Ignore this */
CONSTRAINT Foreign_Key_Vaccinazione4 FOREIGN KEY (Num_Lotto, Nome_Farmaco) REFERENCES Lotto_Vaccino(Num_Lotto, Nome_Farmaco) /* Ignore this */
);

Whit this implementation I'm getting this error:

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

And it's right because the "Idoneo" table does not have a Primary Key, being a child entity, but now I don't know how to properly create a relation between "Idoneo" and "Vaccinazione".

Any suggestion?

Shvji
  • 1
  • 1
    Why can you not add a Primary Key column to Idoneo table? – Rajeev Pande May 29 '21 at 11:18
  • 1
    As @RajeevPande says, you need to add a PK to IDONEO as `primary key (num_protocollo, num_tentativi, data_ora_controllo)`. By the way, `varchar2` is the standard type for short strings in Oracle. `varchar` is [marked "Do not use" in the documentation](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/Data-Types.html#GUID-DF7E10FC-A461-4325-A295-3FD4D150809E), and `char` is [just horrible](https://stackoverflow.com/a/42165653/230471). – William Robertson May 29 '21 at 13:28
  • 1
    The FK on Idoneo includes all the columns in the table, so it will only contain data that already exists in the parent table - which makes no sense (to me) – NickW May 29 '21 at 17:15

0 Answers0