Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

References with PostgreSQL

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

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

>Solution :

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

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading