0

I created an unique index (id_contact_2) over columns id_contact, id_rubrique and poste as you can see in the screenshot below:

CREATE TABLE poste (

    id_poste    int          NOT NULL AUTO_INCREMENT,
    id_contact  int          NOT NULL,
    id_rubrique int          NOT NULL,
    poste       varchar(255)     NULL,

    CONSTRAINT PRIMARY KEY ( id_poste ),

    CONSTRAINT UNIQUE ( id_contact, id_rubrique, poste )
);

CREATE INDEX id_contact ON poste ( id_contact );

enter image description here

Despite this unique index I can add the same value for more than one row.
Have you an idee of hos this can be possible?

Thanks

enter image description here

Dai
  • 141,631
  • 28
  • 261
  • 374
digitalway
  • 61
  • 7
  • What problem does this `UNIQUE` index solve, though? Supposing someone wants to send a short message like just "Hello" twice to the same recipient (but say, a year apart)? – Dai Oct 24 '22 at 14:32
  • 1
    Also, why isn't your `poste` column `NOT NULL`? Why do you allow `NULL` there? – Dai Oct 24 '22 at 14:33
  • In french 'poste' mean 'position', like your position in a company, it can be null because we can know where a person work (id_rubrique), but not his position – digitalway Oct 24 '22 at 14:51
  • 1
    Those four rows are not duplicate of each other, since nulls cannot be compared to anything (nulls or scalar values). Don't treat nulls as values. – The Impaler Oct 24 '22 at 14:58
  • @digitalway Ah, my apologies - I thought it was a table for text messages ("posts"). – Dai Oct 24 '22 at 15:13
  • We need to check the collation; please provide `SHOW CFREATE TABLE`. – Rick James Oct 24 '22 at 16:04

0 Answers0