Définition d'une colonne SQL : valeur par défaut et not null redondants ?

Question

J'ai vu à plusieurs reprises la syntaxe suivante qui définit une colonne dans une déclaration DDL de création/modification :

ALTER TABLE tbl ADD COLUMN col VARCHAR(20) NOT NULL DEFAULT "MyDefault"

La question est la suivante : puisqu'une valeur par défaut est spécifiée, est-il nécessaire de préciser également que la colonne ne doit pas accepter les NULL ? En d'autres termes, DEFAULT ne rend-il pas NOT NULL redondant ?

Answer 1

DEFAULT est la valeur qui sera insérée en l'absence d'une valeur explicite dans une instruction d'insertion/mise à jour. Supposons que votre DDL n'ait pas le paramètre NOT NULL contrainte :

ALTER TABLE tbl ADD COLUMN col VARCHAR(20) DEFAULT 'MyDefault'

Vous pourriez alors publier ces déclarations

-- 1. This will insert 'MyDefault' into tbl.col
INSERT INTO tbl (A, B) VALUES (NULL, NULL);

-- 2. This will insert 'MyDefault' into tbl.col
INSERT INTO tbl (A, B, col) VALUES (NULL, NULL, DEFAULT);

-- 3. This will insert 'MyDefault' into tbl.col
INSERT INTO tbl (A, B, col) DEFAULT VALUES;

-- 4. This will insert NULL into tbl.col
INSERT INTO tbl (A, B, col) VALUES (NULL, NULL, NULL);

Alternativement, vous pouvez aussi utiliser DEFAULT en UPDATE déclarations, selon le SQL-1992 standard :

-- 5. This will update 'MyDefault' into tbl.col
UPDATE tbl SET col = DEFAULT;

-- 6. This will update NULL into tbl.col
UPDATE tbl SET col = NULL;

Remarque : toutes les bases de données ne prennent pas en charge toutes ces syntaxes standard SQL. L'ajout du NOT NULL provoquera une erreur avec les déclarations 4, 6 alors que 1-3, 5 sont toujours des déclarations valables. Donc pour répondre à votre question : Non, elles ne sont pas redondantes.

Answer 2

Même avec une valeur par défaut, vous pouvez toujours remplacer les données de la colonne avec null .

El NOT NULL ne vous permettra pas de mettre à jour cette rangée après qu'elle ait été créée avec null valeur

Answer 3

Mon professeur de SQL m'a dit que si vous spécifiez à la fois un DEFAULT valeur et NOT NULL ou NULL , DEFAULT doit toujours être exprimée avant NOT NULL o NULL .

Comme ça :

ALTER TABLE tbl ADD COLUMN col VARCHAR(20) DEFAULT "MyDefault" NOT NULL

ALTER TABLE tbl ADD COLUMN col VARCHAR(20) DEFAULT "MyDefault" NULL

Answer 4

Je dirais que non.

Si la colonne accepte les valeurs nulles, rien ne vous empêche d'insérer une valeur nulle dans le champ. Pour autant que je sache, la valeur par défaut ne s'applique qu'à la création d'une nouvelle ligne.

Si l'option not null est activée, vous ne pouvez pas insérer une valeur nulle dans le champ, car une erreur se produira.

Considérez-le comme un mécanisme de sécurité pour éviter les valeurs nulles.

Answer 5

En d'autres termes, DEFAULT ne rend-il pas NOT NULL redondant ?

Non, il n'est pas redondant. Pour étendre la réponse acceptée. Pour la colonne col qui est nullable awe peut insérer NULL même lorsque DEFAULT est défini :

CREATE TABLE t(id INT PRIMARY KEY, col INT DEFAULT 10);

-- we just inserted NULL into column with DEFAULT
INSERT INTO t(id, col) VALUES(1, NULL);

+-----+------+
| ID  | COL  |
+-----+------+
|   1 | null |
+-----+------+

Oracle a introduit une syntaxe supplémentaire pour ce type de scénario afin de remplacer le NULL explicite par le NULL par défaut. DEFAULT ON NULL :

CREATE TABLE t2(id INT PRIMARY KEY, col INT DEFAULT ON NULL 10);
-- same as
--CREATE TABLE t2(id INT PRIMARY KEY, col INT DEFAULT ON NULL 10 NOT NULL); 

INSERT INTO t2(id, col) VALUES(1, NULL);

+-----+-----+
| ID  | COL |
+-----+-----+
|  1  |  10 |
+-----+-----+

Ici, nous avons essayé d'insérer NULL mais nous avons obtenu par défaut.

db<>démonstration

ON NULL

Si vous spécifiez la clause ON NULL, Oracle Database attribue la valeur de la colonne DEFAULT lorsqu'une instruction INSERT ultérieure tente d'attribuer une valeur qui vaut NULL.

Lorsque vous spécifiez ON NULL, la contrainte NOT NULL et l'état de contrainte NOT DEFERRABLE sont implicitement spécifiés.