0
CREATE TABLE image_info
(
    id BIGSERIAL PRIMARY KEY,
    is_main boolean,
    image_id BIGINT REFERENCES image(id) NOT NULL,
    good_id BIGINT REFERENCES good(id) NOT NULL
);

I need to add a constraint that only unique good_id must have one is_main = true, but many is_main = false for example :

INSERT INTO image_info (is_main, good_id, image_id) VALUES (true, 2,1);
INSERT INTO image_info (is_main, good_id, image_id) VALUES (false, 2,2);
INSERT INTO image_info (is_main, good_id, image_id) VALUES (false, 3,1);
INSERT INTO image_info (is_main, good_id, image_id) VALUES (true, 3,2);
INSERT INTO image_info (is_main, good_id, image_id) VALUES (false, 3,3);
INSERT INTO image_info (is_main, good_id, image_id) VALUES (true, 3,4); // - error because of constraint

I can add this constraint via SQL script:

CREATE UNIQUE INDEX "only_one_good_with_is_main_true_uix" ON image_info(good_id) WHERE is_main = true;

My question is how can I add the same constraint with hibernate mapping?

Sergey Bogdanov
  • 1
  • 1
  • I'm not sure Hibernate mapping supports this as, if I remember correctly, this isn't supported by all databases and hard to emulate otherwise. - This might help or at least provide some more info: https://stackoverflow.com/questions/12025844/how-to-annotate-unique-constraint-with-where-clause-in-jpa – Thomas Aug 24 '22 at 19:27
  • Thank you for sharing the link, I couldn't find how to do it with Hibernate, I thought maybe I was missing something, but your answer confirms that most of all it will have to be done manually – Sergey Bogdanov Aug 24 '22 at 19:40

1 Answers1

0

You can't do that with plain Hibernate annotations. You should use a schema management tool like Liquibase or Flyway anyway, as Hibernate is not meant to be used for schema migration or these database specific things.

Christian Beikov
  • 15,141
  • 2
  • 32
  • 58