CODEWITHSUNDEEP
sqlspringpostgresqlspring-mvcjpa
user630209
user630209

Reputation: 1207

Unique key with Empty values

This is the schema for the table, here defined a unique constraint with job_category_id, screening_type_id, test_id, sex.

CREATE TABLE job_profile
(
  profile_id numeric(5,0) NOT NULL,
  job_category_id numeric(5,0),
  test_id numeric(5,0),
  sex character(1),
  default_yn character(1),
  screening_type_id numeric(5,0),
  CONSTRAINT job_profile_pkey PRIMARY KEY (profile_id),
  CONSTRAINT fk_jobprofile_jobcate FOREIGN KEY (job_category_id)
      REFERENCES job_category_mast (job_category_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_jobprofile_test FOREIGN KEY (test_id)
      REFERENCES test_mast (test_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_prof_screentype FOREIGN KEY (screening_type_id)
      REFERENCES screening_type_mast (screening_type_id) MATCH SIMPLE
      ON UPDATE RESTRICT ON DELETE RESTRICT,
  CONSTRAINT uk_job_test_sex_screening UNIQUE (job_category_id, screening_type_id, test_id, sex)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE job_profile
  OWNER TO cdcis;
GRANT ALL ON TABLE job_profile TO cdcis;
GRANT SELECT, UPDATE, INSERT, DELETE ON TABLE job_profile TO cdcis_app;

If one field is empty then the unique constraint fails here.

enter image description here

How to add constraint so that one empty value is accepted, so it will be unique.

Can handle this scenario in the application using JPA?

Upvotes: 0

Views: 1237

Answers (1)

pumbo
pumbo

Reputation: 3826

According to the documentation:

Null values are not considered equal

So unique constraint won't work this way. You have 2 options:

  1. Create a trigger that will manually check the data integrity and deny changes if the table contains more than one empty value.

  2. Set default value to 0 and NOT NULL constraint on these columns. In that case, you will be able to have only one row containing empty (zero) value.

Update:

As Abelisto suggested, it can be easily done with functional indexes.

CREATE UNIQUE INDEX uix_job_test_sex_screening on job_profile(coalesce(job_category_id, -1), screening_type_id, test_id, sex);

Upvotes: 4

Related Questions