0

Im Not sure if this question already been asked, because I think my question is not really clear...

Let say i have this table :

CREATE TABLE IF NOT EXISTS EntrepriseSpecialites
(
    id_EntrepriseSpecialite INT AUTO_INCREMENT PRIMARY KEY
    ,id_Entreprise INT
    ,id_Specialite INT UNIQUE
);

At the moment, i can only have one id_Specialite with the same value in my table, but does there is a way to only an unique id_Specialite by id_Entreprise.

Example :

    id_EntrepriseSpecialite | id_Entreprise | id_Specialite
    1 | 1 | 1
    2 | 2 | 1
    3 | 2 | 4
    4 | 2 | 1 <- ops id_Entreprise 2 already have the id_Specialite 1
    5 | 3 | 1
seb
  • 313
  • 1
  • 7
  • 19
  • Why not make `id_Entreprise` and `id_Speciatelite` your `Composite Primary Key` so that it would always be unique? – Edper Nov 12 '13 at 00:49
  • I suppose you just want a compound `UNIQUE` constraint on two columns: `id_Entreprise` and `id_Specialite` - check this thread: [UNIQUE on more than one column](http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql) – Przemyslaw Kruglej Nov 12 '13 at 00:50

1 Answers1

1

Why not make d_Entreprise and id_Specialite as your compound/composite Primary Key, like:

CREATE TABLE IF NOT EXISTS EntrepriseSpecialites
(
 id_Entreprise INT
 ,id_Specialite INT
 , PRIMARY KEY (id_Entreprise, id_Specialite)
);

See SQLFiddle Demo

Edper
  • 9,144
  • 1
  • 27
  • 46