References with PostgreSQL-CodePudding

I have table:

CREATE TABLE cars_info.cars(
  id SERIAL,
  owner_id INTEGER,
  brand VARCHAR(50) NOT NULL,
  model VARCHAR(50) NOT NULL,
  color VARCHAR(50) NOT NULL,
  register_number VARCHAR(50) NOT NULL,
  created DATE NOT NULL,
  PRIMARY KEY(id, brand, model, color, register_number, created),
  CONSTRAINT fk_owner_id
    FOREIGN KEY(owner_id)
      REFERENCES persons_info.persons(id)
);

But when i tried create next table:

CREATE TABLE cars_info.violations(
  id SERIAL PRIMARY KEY,
  car_id INTEGER NOT NULL,
  message VARCHAR(100) NOT NULL,
  active BOOLEAN NOT NULL,
  CONSTRAINT fk_car_id
    FOREIGN KEY(car_id)
      REFERENCES cars_info.cars(id)
);

I take error about that "target external table "cars" does not have a unique constraint corresponding to the given keys" How i can fix that? Im begginer in SQL and dont know how googling that

CodePudding user response：

Your primary key definition for cars

PRIMARY KEY(id, brand, model, color, register_number, created)

makes no sense: The id column, being serial, is itself unique and it alone should be the primary key.

Delete your primary key definition and change the id column definition to:

id serial not null primary key

Unrelated, but best practice is to name table in the singular; names your tables car and violation rather than cars and violations