Search code examples
javaspringderby

Constraint is invalid: there is no unique or primary key constraint on table that matches the number and types of the columns in the foreign key

I'm trying to create 2 tables with reference. But I can't find my mistake. This is script for derby:

CREATE TABLE AUDITORIUM (
  audId BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
  nameAud          VARCHAR(50),
  numberOfSeats    INTEGER,
  vipSeats         VARCHAR(100)
);

CREATE TABLE EVENT (
  eventId    BIGINT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
  name       VARCHAR(50),
  basePrice  INTEGER,
  rating     VARCHAR(50),
  audId      BIGINT,
FOREIGN KEY (audId) REFERENCES AUDITORIUM (audId)
);

As a result I am getting this error: Constraint 'SQL160511200811240' is invalid: there is no unique or primary key constraint on table '"SA"."AUDITORIUM"' that matches the number and types of the columns in the foreign key.

Solution

  • The error is telling you that the audId column in the AUDITORIUM table needs to be declared as the PRIMARY KEY (or UNIQUE). Foreign keys can only reference unique or primary keys in another table.