Can't find the solution to an error ORA-02270

Question

I want to convert ERD schema to relational schema. I wrote some commands, but some of them do not work.

When I try to execute this command:

CREATE TABLE Egzemplarz_papierowy(
id_egzemplarza INTEGER NOT NULL, 
id_autora INTEGER REFERENCES Autor(id_autora), 
tytuł CHAR(20) NOT NULL REFERENCES Książka(tytuł), 
PRIMARY KEY(id_egzemplarza, id_autora, tytuł), 
nazwa_działu CHAR(20) REFERENCES Dział(nazwa_działu), 
rok_wydania INTEGER NOT NULL,
status_wypożyczenia CHAR(20) NOT NULL,
okładka CHAR(20) NULL, 
liczba_stron INTEGER NOT NULL);

an error occurs: ORA-02270. I understand that it is telling me that there is a mismatch in PRIMARY KEYS, but I don't understand why, because primary keys of entity Książka are matched and I am only adding id_egzemplarza here. What should I do?

Whole code:

CREATE TABLE Autor(id_autora INTEGER PRIMARY KEY, imię CHAR(20) NOT NULL, nazwisko CHAR(20) NOT NULL);

CREATE TABLE Dział(nazwa_działu CHAR(20) PRIMARY KEY, liczba_egzemplarzy INTEGER NOT NULL, liczba_egzemplarzy_wypożyczonych INTEGER NOT NULL);

CREATE TABLE Książka(tytuł CHAR(20) NOT NULL, id_autora INTEGER REFERENCES Autor(id_autora), nazwa_działu CHAR(20) REFERENCES Dział(nazwa_działu), PRIMARY KEY(tytuł, id_autora), liczba_egzemplarzy INTEGER NOT NULL, liczba_egzemplarzy_wypożyczonych INTEGER NOT NULL);

CREATE TABLE Egzemplarz_papierowy(id_egzemplarza INTEGER NOT NULL, id_autora INTEGER REFERENCES Autor(id_autora), tytuł CHAR(20) NOT NULL REFERENCES Książka(tytuł), PRIMARY KEY(id_egzemplarza, id_autora, tytuł), nazwa_działu CHAR(20) REFERENCES Dział(nazwa_działu), rok_wydania INTEGER NOT NULL, status_wypożyczenia CHAR(20) NOT NULL, okładka CHAR(20) NULL, liczba_stron INTEGER NOT NULL);

CREATE TABLE Egzemplarz_elektroniczny(id_egzemplarza INTEGER NOT NULL, id_autora INTEGER REFERENCES Autor(id_autora), tytuł CHAR(20) NOT NULL REFERENCES Książka(tytuł), PRIMARY KEY(id_egzemplarza, id_autora, tytuł), rok_wydania INTEGER NOT NULL, status_wypożyczenia CHAR(20) NOT NULL, okładka CHAR(20) NULL, format_pliku CHAR(20) NOT NULL);

CREATE TABLE Karta_użytkownika(PESEL VARCHAR(11) PRIMARY KEY, imię CHAR(20) NOT NULL, nazwisko CHAR(20) NOT NULL, balans_konta INTEGER NOT NULL);

CREATE TABLE Należy(id_autora INTEGER REFERENCES Autor(id_autora), nazwa_działu CHAR(20) REFERENCES Dział(nazwa_działu), PRIMARY KEY(id_autora, nazwa_działu));

CREATE TABLE Wypożyczenie_papierowy(data_wypożyczenia DATE NOT NULL, id_autora REFERENCES Autor(id_autora), tytuł REFERENCES Książka(tytuł), id_egzemplarza REFERENCES Egzemplarz_papierowy(id_egzemplarza), pesel REFERENCES Karta_użytkownika(PESEL), PRIMARY KEY(data_wypożyczenia, id_autora, tytuł, pesel), rzeczywista_data_zwrotu DATE, deadline_daty_zwrotu DATE, status_wypożyczenia CHAR(20));

CREATE TABLE Wypożyczenie_elektroniczny(data_wypożyczenia DATE NOT NULL, id_autora REFERENCES Autor(id_autora), tytuł REFERENCES Książka(tytuł), id_egzemplarza REFERENCES Egzemplarz_elektroniczny(id_egzemplarza), pesel REFERENCES Karta_użytkownika(PESEL), PRIMARY KEY(data_wypożyczenia, id_autora, tytuł, pesel), rzeczywista_data_zwrotu DATE, deadline_daty_zwrotu DATE, status_wypożyczenia CHAR(20));

William Robertson · Answer 1 · 2021-07-04T22:35:40.850

A multi-column constraint can't be defined as part of one column - it has to be a separate definition. So this:

tytuł CHAR(20) NOT NULL REFERENCES Książka(tytuł)

won't work, because the PK of Książka is

CREATE TABLE Książka( ... PRIMARY KEY(tytuł, id_autora) );

The FK referencing it needs to name both columns, like this:

foreign key (tytuł, id_autora) references książka(tytuł, id_autora)

Fixed script (first four tables):

create table autor
( id_autora                         integer primary key
, imię                              varchar2(20) not null
, nazwisko                          varchar2(20) not null );

create table dział
( nazwa_działu                      varchar2(20) primary key
, liczba_egzemplarzy                integer not null
, liczba_egzemplarzy_wypożyczonych  integer not null);

create table książka
( tytuł                             varchar2(20) not null
, id_autora                         references autor(id_autora)
, nazwa_działu                      references dział(nazwa_działu)
, liczba_egzemplarzy                integer not null
, liczba_egzemplarzy_wypożyczonych  integer not null
, primary key(tytuł, id_autora)
);

create table egzemplarz_papierowy
( id_egzemplarza                    integer not null
, id_autora                         references autor(id_autora)
, tytuł                             varchar2(20) not null
, nazwa_działu                      references dział(nazwa_działu)
, rok_wydania                       integer not null
, status_wypożyczenia               varchar2(20) not null
, okładka                           varchar2(20) null
, liczba_stron                      integer not null
, primary key(id_egzemplarza, id_autora, tytuł)
, foreign key (tytuł, id_autora) references książka(tytuł, id_autora)
);

You might consider naming the constraints, as then any violation errors will be easier to trace, although I do sometimes wish they would find a way to word the error message in a way that doesn't require a named constraint, as with not null.

You should avoid CHAR, as it's a bug trap with no advantages, designed (I can only imagine) for ANSI completeness and cross-platform portability, rather than any useful functionality, as it has none.

For single-column foreign key constraints, you can omit the datatype and let it inherit from the parent table, which in my view is better practice.

score 0 · Answer 2 · answered Jul 04 '21 at 17:34

0

The syntax for defining a PK in Oracle is:

CONSTRAINT constraint_name PRIMARY KEY (column1, column2, ... column_n)

answered Jul 04 '21 at 17:34

NickW

8,430
2
6
19

This is a valid syntax. – Atif Jul 04 '21 at 17:36

score 0 · Answer 3 · answered Jul 04 '21 at 17:39

Problem seems to be with the definition of table Książka which has composite Primary key containing the columns tytuł, id_autora but when you are creating the foreign key you have referred to only column tytuł.

You should change your create table as below:

CREATE TABLE Egzemplarz_papierowy(
id_egzemplarza INTEGER NOT NULL, 
id_autora INTEGER REFERENCES Autor(id_autora), 
tytuł CHAR(20) NOT NULL REFERENCES Książka(tytuł, id_autora), 
PRIMARY KEY(id_egzemplarza, id_autora, tytuł), 
nazwa_działu CHAR(20) REFERENCES Dział(nazwa_działu), 
rok_wydania INTEGER NOT NULL,
status_wypożyczenia CHAR(20) NOT NULL,
okładka CHAR(20) NULL, 
liczba_stron INTEGER NOT NULL);

score 0 · Answer 4 · answered Jul 04 '21 at 21:23

There are problems in the definitions of keys on the Egzemplarz_papierowy, Egzemplarz_elektroniczny, Wypożyczenie_papierowy, and Wypożyczenie_elektroniczny tables. In each of these tables an attempt is made to create a foreign key constraint on a single field - however, the primary key on the target table is a multiple-column key. For example, your definition of Egzemplarz_papierowy is:

CREATE TABLE Egzemplarz_papierowy(
id_egzemplarza INTEGER NOT NULL, 
id_autora INTEGER REFERENCES Autor(id_autora), 
tytuł CHAR(20) NOT NULL REFERENCES Książka(tytuł), 
PRIMARY KEY(id_egzemplarza, id_autora, tytuł), 
nazwa_działu CHAR(20) REFERENCES Dział(nazwa_działu), 
rok_wydania INTEGER NOT NULL,
status_wypożyczenia CHAR(20) NOT NULL,
okładka CHAR(20) NULL, 
liczba_stron INTEGER NOT NULL);

This fails because of the REFERENCES clause on the tytuł field:

tytuł CHAR(20) NOT NULL REFERENCES Książka(tytuł),

as the field tytuł is neither a primary or unique key on the table Książka. Foreign keys must refer to all of the fields on the target table which are in either a primary key or unique key constraint.

To correct this you should change the foreign key constraint to include all the columns which are in the primary key of the Książka table:

CREATE TABLE Egzemplarz_papierowy
 (id_egzemplarza INTEGER
   NOT NULL,
  id_autora INTEGER
    REFERENCES Autor(id_autora),
  tytuł CHAR(20)
    NOT NULL,
  FOREIGN KEY (tytuł, id_autora)
    REFERENCES Książka(tytuł, id_autora),
  PRIMARY KEY(id_egzemplarza, id_autora, tytuł),
  nazwa_działu CHAR(20)
    REFERENCES Dział(nazwa_działu),
  rok_wydania INTEGER
    NOT NULL,
  status_wypożyczenia CHAR(20)
    NOT NULL,
  okładka CHAR(20)
    NULL,
  liczba_stron INTEGER NOT NULL);

This compiles correctly. The Egzemplarz_elektroniczny, Wypożyczenie_papierowy, and Wypożyczenie_elektroniczny tables have similar problems.

db<>fiddle here

Can't find the solution to an error ORA-02270

4 Answers4