UNIQUE CONSTRAINT Meaning in CREATE TABLE SPONSORSHIP

Question

I would like to know the meaning of the UNIQUE constraint, it is a generated script, it concerns sponsorship, it does not have to be rather?

CONSTRAINT PARRAINAGE_PERSONNE_COMPTE_AK UNIQUE (id_pers_cpt, id_pers_cpt_PERSONNE_COMPTE)

Table:

CREATE TABLE PARRAINAGE
(
    id_parrainage               Int  Auto_increment  NOT NULL ,
    date                        Date NOT NULL ,
    id_pers_cpt                 Int NOT NULL ,
    id_pers_cpt_PERSONNE_COMPTE Int NOT NULL ,
    id_etab                     Int,
    CONSTRAINT PARRAINAGE_PK PRIMARY KEY (id_parrainage)
   ,CONSTRAINT PARRAINAGE_PERSONNE_COMPTE_FK FOREIGN KEY (id_pers_cpt) REFERENCES PERSONNE_COMPTE(id_pers_cpt)
   ,CONSTRAINT PARRAINAGE_PERSONNE_COMPTE0_FK FOREIGN KEY (id_pers_cpt_PERSONNE_COMPTE) REFERENCES PERSONNE_COMPTE(id_pers_cpt)
   ,CONSTRAINT PARRAINAGE_ETABLISSEMENT1_FK FOREIGN KEY (id_etab) REFERENCES ETABLISSEMENT(id_etab)
   ,CONSTRAINT PARRAINAGE_PERSONNE_COMPTE_AK UNIQUE (id_pers_cpt)

)ENGINE=InnoDB;

It means that the values of `id_pers_cpt` cannot be duplicated. They are unique. — Gordon Linoff, Sep 06 '18 at 11:44
Possible duplicate of [What is the difference between UNIQUE, UNIQUE KEY and CONSTRAINT 'name' UNIQUE?](https://stackoverflow.com/questions/9201584/what-is-the-difference-between-unique-unique-key-and-constraint-name-unique) — Madhur Bhaiya, Sep 06 '18 at 11:45
It is a generated script, it concerns sponsorship, it does not have to be rather? CONSTRAINT PARRAINAGE_PERSONNE_COMPTE_AK UNIQUE (id_pers_cpt, id_pers_cpt_PERSONNE_COMPTE) — L.G, Sep 06 '18 at 11:50

Zaynul Abadin Tuhin · Answer 1 · 2018-09-06T12:14:08.140

1

The UNIQUE constraint ensures that all values in a column are different.

Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.

A PRIMARY KEY constraint automatically has a UNIQUE constraint.

However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

In your table id_parrainage primary key and id_pers_cpt unique key both will never be duplicated

id_parrainage column will not allow null value and also id_pers_cpt will not allow null value

edited Sep 06 '18 at 12:14

answered Sep 06 '18 at 11:47

Zaynul Abadin Tuhin

31,407
5
33
63

What about null values? – jarlh Sep 06 '18 at 11:52
@jarlh ansi sql will allow many and mysql also but mssql allow one – Zaynul Abadin Tuhin Sep 06 '18 at 11:55
Opps sorry thanks i though it ia only unique key i edited – Zaynul Abadin Tuhin Sep 06 '18 at 12:13

score 1 · Accepted Answer · answered Sep 06 '18 at 12:13

The table relates two persons it seems:

id_pers_cpt
id_pers_cpt_PERSONNE_COMPTE

The constraint

CONSTRAINT PARRAINAGE_PERSONNE_COMPTE_AK UNIQUE (id_pers_cpt)

assures that a id_pers_cpt can only appear once in the table. Thus an id_pers_cpt_PERSONNE_COMPTE can be related to many id_pers_cpt, but an id_pers_cpt can only be related to one id_pers_cpt_PERSONNE_COMPTE.

This makes this a pers_cpt table, with one record per id_pers_cpt. If you want to allow an id_pers_cpt to be related to multiple id_pers_cpt_PERSONNE_COMPTE, then you need the constraint you mentioned instead:

CONSTRAINT PARRAINAGE_PERSONNE_COMPTE_AK UNIQUE (id_pers_cpt, id_pers_cpt_PERSONNE_COMPTE)

UNIQUE CONSTRAINT Meaning in CREATE TABLE SPONSORSHIP

2 Answers2