CLÉ PRIMAIRE Composite applique les contraintes not NULL sur les colonnes concernées

C'est un étrange, un comportement non désiré, j'ai rencontré dans Postgres:
Lorsque je crée une Postgres tableau avec les clés primaires composites, il applique une contrainte not NULL sur chaque colonne de la combinaison composite.

Par exemple,

CREATE TABLE distributors (m_id integer, x_id integer, PRIMARY KEY(m_id, x_id));

applique NOT NULL contrainte sur les colonnes m_id et x_id, que je ne veux pas!
MySQL ne fait pas cela. Je pense que l'Oracle ne pas le faire aussi bien.

Je comprends que PRIMARY KEY applique UNIQUE et NOT NULL automatiquement, mais qui fait sens pour une seule colonne de clé primaire. Dans un multi-colonne de clé primaire de la table, l'unicité est déterminée par la combinaison.

Est-il un moyen simple d'éviter ce comportement de Postgres?

Si j'exécute ce:

CREATE TABLE distributors (m_id integer, x_id integer);

Je n'ai pas tout NOT NULL contraintes de cours.

  • Bien: c'est le standard. Imaginez deux tuples présents, à la fois avec x_id = NULL. Maintenant, si vous voulez mettre à jour l'un de ces, vous ne seriez pas en mesure de traiter un seul, qui serait en violation de 2FN. (tout cela parce que: NULL n'est pas une valeur) Remarque que pour le SGBD au ddl-temps, il y a deux choix: soit en refusant de la définition, ou la conversion de la clé des champs à ne pas accepter les valeurs null automatiquement.
  • Je vois ce que vous dites. Je suis en train de migrer de MySQL à Postgresql et donc cette incohérence causes d'un problème. Pouvez-vous donner plus de détails sur "Notez que pour le SGBD au ddl-temps, il y a deux choix: soit en refusant de la définition, ou la conversion de la clé des champs à ne pas accepter les valeurs null automatiquement."
  • Il est illégal définition. Si le SGBD peut la refuser. Ou de le corriger. (Vous avez pu le comparer à un compilateur C confronté à la définition int i=1.0; )
  • Vous êtes dans l'erreur. MySQL, Oracle, SQL-Server, tous ont exactement le même comportement que Postgres. Pas de valeurs null sont autorisées dans les colonnes de la partie de la clé primaire.
  • Il doit y avoir un malentendu. Comme @ypercube a commenté: Oracle ne pas autoriser les valeurs NULL dans un PK colonne: sqlfiddle.com/#!4/52cf2/1. Ni MySQL: sqlfiddle.com/#!2/ce8d4e.
  • Il n'est pas étrange comportement. Une collection de colonnes que l'on peut inclure les valeurs NULL ne sont pas, par définition, une clé candidate. Cela a été le cadre du modèle relationnel depuis 1970. Si elle ne peut pas être un candidat à la clé, alors il ne peut pas être la clé primaire. Il serait étrange si Postgres n'a pas l'appliquer.

InformationsquelleAutor user3422637 | 2014-10-30
  1. 35

    Si vous besoin pour autoriser les valeurs NULL, utilisez un UNIQUE contrainte au lieu d'un PRIMARY KEY (et ajouter un substitut PK colonne, je vous propose un série). Cela permet colonnes NULL:

    CREATE TABLE distributor (
   distributor_id serial PRIMARY KEY
 , m_id integer
 , x_id integer
 , UNIQUE(m_id, x_id)
);

    Note, cependant (par la documentation):

    Pour l'application d'une contrainte unique, les valeurs null ne sont pas considérées comme égales.

    Dans votre cas, vous pouvez entrer (1, NULL) pour (m_id, x_id) un nombre quelconque de fois, sans violer la contrainte. Postgres ne considère jamais deux valeurs NULL l'égalité des - selon la définition dans la norme SQL.

    Si vous avez besoin pour traiter NULL valeurs comme l'égalité de refuser ces "doublons", je vois deux options:

    1. Deux partielle index

    En outre à la UNIQUE contrainte ci-dessus:

    CREATE UNIQUE INDEX dist_m_uni_idx ON distributor (m_id) WHERE x_id IS NULL;
CREATE UNIQUE INDEX dist_x_uni_idx ON distributor (x_id) WHERE m_id IS NULL;

    Mais cela devient de mains rapidement, avec plus de deux colonnes, ce qui peut être NULL.

    Connexes:

    2. Un multi-colonne d'index UNIQUE sur des expressions

    Au lieu de la contrainte d'unicité. Nous avons besoin d'un libre valeur par défaut qui n'est jamais présente dans les colonnes concernées, comme -1. Ajouter CHECK contraintes pour la rejeter:

    CREATE TABLE distributor (
   distributor serial PRIMARY KEY
 , m_id integer
 , x_id integer
 , CHECK (m_id <> -1)
 , CHECK (x_id <> -1)
);
    CREATE UNIQUE INDEX distributor_uni_idx ON distributor (COALESCE(m_id, -1)
                                                      , COALESCE(x_id, -1))

    Comment certains SGBDR gérer les choses n'est pas toujours un indicateur utile pour le bon comportement. Le Postgres manuel de conseils à ce:

    Cela signifie que, même en présence d'une contrainte unique, il est possible
    pour stocker les doublons de lignes qui contiennent une valeur null dans au moins un des
    les colonnes contraintes. Ce comportement est conforme à la norme SQL,
    mais nous avons entendu dire que d'autres bases de données SQL peuvent pas suivre cette règle.
    Donc soyez prudent lors de développement d'applications qui sont destinés à être portables.

    Gras c'est moi qui souligne.

    • MySQL se comporte de la même manière exacte que Postgres dans cet aspect. L'OP s'est trompé dans ses affirmations.
    • C'est une belle (mais moche) solution de contournement. Soupir. @ypercube : pourrait dépendre sur le moteur, IIRC. Ou la phase de la lune...
    • Non, il ne dépend pas du moteur (au moins myisam et innodb, les 2 plus courants des moteurs.) Peut-être que l'OP est de travailler sur une ancienne version de mysql.
    • Ok. Je corrige la position des mains.
    • Et pourquoi est-il laid? L'op veut contraintes uniques et les valeurs null.
    • OK, suppression de l'affirmation à propos de MySQL, j'ai confiance que vous savez de son comportement. Je sais Oracle considère comme NULLE et chaîne vide à l'identique, au moins dans certains contextes, je m'en doutais MySQL traiter deux NULS comme égale à une contrainte UNIQUE, mais non, ce n'est pas le cas: sqlfiddle.com/#!2/78ddc/1. Mais Oracle n': sqlfiddle.com/#!4/5a6db/1 (décevant pour un gros joueur ...)
    • Oui, en effet. Et il devient de plus en plus bizarre. Oracle merveilles: Violon-2. Pouvez-vous deviner quelles sont les causes?
    • Je suppose que Larry et ses amis fumaient des mauvaises choses dans la fin des années 70, quand ils ont commencé à travailler sur leur SGBDR. Sérieusement, on dirait que toutes les valeurs null constituent une véritable "composite null" qui est considérée comme pas égale à une autre, bien que partielle, les valeurs NULL sont traités comme une chaîne vide dans les types de caractères, et donc l'égalité. Ne veux pas que ...
    • Il n'a rien à voir avec des chaînes. C'est un effet secondaire de l'index - qui ne comprennent pas les lignes qui ont seulement les valeurs null. Si une ligne (l'index) a certaines valeurs non nulles, alors il est inclus dans l'index. La seule excuse qu'ils peuvent avoir, c'est que leur mise en œuvre peut précéder la norme SQL (aucune idée si c'est vrai) 😉
    • C'est une très mauvaise solution de contournement, par la modification de la contrainte à l'unique et l'ajout d'une substitution de la clé primaire.

    InformationsquelleAutor Erwin Brandstetter