tablas - postgresql llave primaria compuesta

¿Por qué puedo crear una tabla con CLAVE PRINCIPAL en una columna que admite nulos?

Question

El siguiente código crea una tabla sin generar ningún error:

CREATE TABLE test(
ID INTEGER NULL,
CONSTRAINT PK_test PRIMARY KEY(ID)
)

Tenga en cuenta que no puedo insertar un valor NULL, como se esperaba:

INSERT INTO test
VALUES(1),(NULL)
ERROR:  null value in column "id" violates not-null constraint
DETAIL:  Failing row contains (null).
********** Error **********

ERROR: null value in column "id" violates not-null constraint
SQL state: 23502
Detail: Failing row contains (null).

¿Por qué puedo crear una tabla con una definición autocontradictoria? La columna de ID se declara explícitamente como NULLable, y es implícitamente no anulable, como parte de la CLAVE PRIMARIA. ¿Tiene sentido?

Edit: ¿no sería mejor si este autocontrollante CREATE TABLE simplemente fallara allí?

Answer 1

Porque la PRIMARY KEY hace que la columna NOT NULL automáticamente . Cito el manual aquí :

La restricción de clave principal especifica que una columna o columnas de una tabla solo pueden contener valores únicos (no duplicados), no nulos. Técnicamente, PRIMARY KEY es simplemente una combinación de UNIQUE y NOT NULL .

Énfasis en negrita el mio

Realicé una prueba para confirmar que (¡en mi opinión anterior!) NOT NULL es completamente redundante en combinación con una restricción PRIMARY KEY (en la implementación actual, hasta la versión 9.5). La restricción NOT NULL permanece después de eliminar la restricción PK, independientemente de una cláusula NOT NULL explícita en el momento de la creación.

db=# CREATE TEMP TABLE foo (foo_id int PRIMARY KEY);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "foo_pkey" for table "foo"
CREATE TABLE

db=# ALTER TABLE foo DROP CONSTRAINT foo_pkey;
ALTER TABLE

db=# /d foo
   table »pg_temp_4.foo«
 column |  type   | attribute
--------+---------+-----------
 foo_id | integer | not null

Comportamiento idéntico si se incluye NULL en la CREATE .

Sin embargo , aún así no se perderá si NOT NULL mantiene NOT NULL redundante en los repositorios de código si se supone que la columna NOT NULL . Si luego decide mover la restricción pk, puede olvidarse de marcar la columna NOT NULL , o si se suponía que NOT NULL .

Hay un elemento en el wiki de Postgres TODO para desacoplar NOT NULL de la restricción PK. Así que esto podría cambiar en futuras versiones:

Mueve la información de la restricción NOT NULL a pg_constraint

Actualmente, las restricciones NOT NULL se almacenan en pg_attribute sin ninguna designación de sus orígenes, por ejemplo, claves primarias. Un problema manifiesto es que al eliminar una restricción PRIMARY KEY no se elimina la designación de restricción NOT NULL. Otro problema es que probablemente deberíamos obligar a NOT NULL a propagarse de las tablas primarias a las secundarias, tal como lo son las restricciones CHECK. (Pero, ¿entonces la caída de la CLAVE PRIMARIA afecta a los niños?)

Respuesta a la pregunta añadida:

¿No sería mejor si esta autocomplaciente CREATE TABLE simplemente fallara allí?

Como se explicó anteriormente, este

foo_id INTEGER NULL PRIMARY KEY

es equivalente a:

foo_id INTEGER PRIMARY KEY

Desde NULL se trata como palabra de ruido.
Y no queremos que lo segundo falle. Así que esta no es una opción.

Answer 2

Si como dijo @ErwinBrandstetter, PRIMARY KEY es simplemente una combinación de UNIQUE y NOT NULL , puede usar una restricción UNIQUE sin NOT NULL lugar de PRIMARY KEY . Ejemplo:

CREATE TABLE test(
    id integer,
    CONSTRAINT test_id_key UNIQUE(id)
);

De esta manera puedes hacer cosas como:

INSERT INTO test (id) VALUES (NULL);
INSERT INTO test (id) VALUES (NULL);
INSERT INTO test (id) VALUES (NULL);

Answer 3

Si la memoria sirve, los documentos mencionan que:

• el null en las declaraciones de creación de tabla es básicamente una palabra de ruido que se ignora
• La primary key obliga a un no nulo y una restricción única

Ver:

# create table test (id int null primary key);
CREATE TABLE
# /d test
     Table "public.test"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | integer | not null
Indexes:
    "test_pkey" PRIMARY KEY, btree (id)