postgres

gistfile1.txt

in Postgresql I have created a table "create table pippo (id integer not null, target_name text, source_name text, value text , part_type_id integer not null, value_acc integer UNIQUE (target_name , source_name , value , part_type_id , value_acc ) but POSTGRESQL allows insert of multiple records with same value of (target_name , source_name , value , part_type_id , value_acc)

ChatGPT
The problem you're experiencing is because of a syntax misunderstanding. The constraint you've created is not doing what you think it's doing.

In your table creation statement:

sql
Copy code
CREATE TABLE pippo (
  id integer NOT NULL,
  target_name text,
  source_name text,
  value text,
  part_type_id integer NOT NULL,
  value_acc integer,
  UNIQUE (target_name , source_name , value , part_type_id , value_acc)
);
The UNIQUE constraint at the end applies to the combination of (target_name, source_name, value, part_type_id, value_acc). This means that for each row in the table, the combination of those five fields must be unique.

However, if any of those fields contain a NULL value, then the UNIQUE constraint is not enforced for that particular row. This is because in SQL, NULL is not considered equal to any other value, including another NULL.

So, for example, if you insert the following rows:

sql
Copy code
INSERT INTO pippo VALUES (1, 'A', 'B', 'C', 1, NULL);
INSERT INTO pippo VALUES (2, 'A', 'B', 'C', 1, NULL);
Both inserts will succeed because the NULL value in the value_acc column means the UNIQUE constraint is not enforced.

If you truly want to ensure uniqueness across those columns, you have a few options:

Disallow NULLs: Make sure all the columns in your unique constraint are set to NOT NULL. This means you'll always have to provide a value for these columns, and the uniqueness constraint will always be enforced.

Partial Index: If you want to allow NULLs but still enforce uniqueness where the value isn't NULL, you can create a unique partial index:

sql
Copy code
CREATE UNIQUE INDEX idx_unique_pippo
ON pippo(target_name, source_name, value, part_type_id, value_acc)
WHERE value_acc IS NOT NULL;
Consider separate constraints: If you are okay with having unique values in some columns, and combined unique values in others, you might consider creating multiple unique constraints or unique indices.

Remember to think about the logic and requirements of your application when deciding how to enforce uniqueness.