Error implementing relational model in Oracle-CodePudding

I need help creating this MER in Oracle. Specifically in FOTOS table as I have this code:

CREATE TABLE "FOTOS"
(  
    "ID_FOTO" INT NOT NULL ENABLE,
    "ID_USU" INT NOT NULL ENABLE,
    "FECHA" DATE NOT NULL ENABLE,

    CONSTRAINT "FOTOS_PK" 
        PRIMARY KEY ("ID_FOTO") ENABLE,
    CONSTRAINT "FOTOS_FK" 
        FOREIGN KEY ("ID_USU") REFERENCES "USUARIOS" ("ID_USU") ENABLE
)

However I keep getting this error:

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

ID_USU is one of two primary keys in USUARIOS USUARIOS TABLE

CodePudding user response：

ID_USU is one of two primary keys in USUARIOS

No. A table can have just one primary key. It can have more than one unique key, but you can only make one of them primary.

What you mistakenly did instead is create a composite primary key. You want the ID to be unique and the name to be unique, but instead you made the combination of ID and name unique, thus allowing duplicate IDs and duplicate names in the table.

What you have:

create table usuarios
(
  id_usu  number        not null,
  nomusu  varchar2(50)  not null,
  ...
  constraint pk_usuarios primary key (id_usu, nomusu)
);

What you want instead:

create table usuarios
(
  id_usu  number        not null,
  nomusu  varchar2(50)  not null,
  ...
  constraint pk_usuarios primary key (id_usu),
  constraint uq_usuarios_nomusu unique (nomusu)
);

CodePudding user response：

Foreign keys have to match the primary/unique key they reference column for column. Since the primary key of "USUARIOS" are columns "ID_USU" and "NOMUSU" you need two columns to add FK from "FOTOS" to "USUARIOS", so just add "NOMUSU" column to "FOTOS" and write:

    CREATE TABLE "FOTOS"
    (  
    "ID_FOTO" INT NOT NULL ENABLE,
    "ID_USU" INT NOT NULL ENABLE,
    "NOMUSU" VARCHAR2(50) NOT NULL ENABLE,
    "FECHA" DATE NOT NULL ENABLE,

    CONSTRAINT "FOTOS_PK" 
        PRIMARY KEY ("ID_FOTO") ENABLE,
    CONSTRAINT "FOTOS_FK" 
        FOREIGN KEY ("ID_USU","NOMUSU") REFERENCES "USUARIOS" ("ID_USU","NOMUSU") ENABLE);