Oracle | NOT NULL

---

1. La contrainte NOT NULL
2. Renforcer la contrainte NOT NULL en utilisant la contrainte CHECK

---

1. La contrainte NOT NULL
   CREATE TABLE table_not_null_1 ( id1 NUMBER(1,0) NOT NULL -- le nom de la contrainte NOT NULL sera générer dynamiquement par Oracle "SYS_C007034" , id2 NUMBER(1,0) CONSTRAINT cc_table_not_null_1_id2 NOT NULL -- le nom de la contrainte NOT NULL est explicite "CC_TABLE_NOT_NULL_1_ID2" );
   

   • Vérifier la création de la contrainte :
     SELECT constraint_name, constraint_type, generated, search_condition FROM user_constraints WHERE table_name = 'table_not_null_1';
     Résultat de la requête : +------------------------+-----------------+----------------+-------------------+ | CONSTRAINT_NAME | CONSTRAINT_TYPE | GENERATED | search_condition | +------------------------+-----------------+----------------+-------------------+ | SYS_C007034 | C | GENERATED NAME | "ID1" IS NOT NULL | +------------------------+-----------------+----------------+-------------------+ | CC_TABLE_NOT_NULL_1_ID2 | C | USER NAME | "ID2" IS NOT NULL | +------------------------+-----------------+----------------+-------------------+
     
   • Vérifier la nullabilité des colonnes de la table :
     SELECT column_name, CASE WHEN nullable = 'N' THEN 'NO' ELSE 'YES' END AS nullable FROM user_tab_columns WHERE table_name = 'table_not_null_1';
     Résultat de la requête : +-------------+----------+ | COLUMN_NAME | NULLABLE | +-------------+----------+ | ID1 | NO | +-------------+----------+ | ID2 | NO | +-------------+----------+
     
   • Insérer des valeurs nulles dans la table :
     insert into table_not_null_1(id1,id2) values(null,1); insert into table_not_null_1(id1,id2) values(1,null);
     Résultat de la requête : Error starting at line 1 in command: insert into TABLE_NOT_NULL_1(ID1,ID2) values(null,1) Error report: ORA-01400: cannot insert NULL into ("HR"."TABLE_NOT_NULL_1"."ID1") *Cause: *Action: Error starting at line 2 in command: insert into TABLE_NOT_NULL_1(ID1,ID2) values(1,null) Error report: ORA-01400: cannot insert NULL into ("HR"."TABLE_NOT_NULL_1"."ID2") *Cause: *Action:
2. Renforcer la contrainte NOT NULL en utilisant la contrainte CHECK
   CREATE TABLE table_not_null_2 ( ID1 NUMBER(1,0) CONSTRAINT CC_table_not_null_2_ID1 CHECK ( ID1 IS NOT NULL ) );
   

   • Vérifier la création de la contrainte :
     SELECT constraint_name, constraint_type, generated, search_condition FROM user_constraints WHERE table_name = 'table_not_null_2';
     Résultat de la requête : +------------------------+-----------------+----------------+-------------------+ | CONSTRAINT_NAME | CONSTRAINT_TYPE | GENERATED | SEARCH_CONDITION | +------------------------+-----------------+----------------+-------------------+ | CC_table_not_null_2_ID1 | C | USER NAME | ID1 IS NOT NULL | +------------------------+-----------------+----------------+-------------------+
     
   • Vérifier la nullabilité des colonnes de la table :
     SELECT column_name, CASE WHEN nullable = 'N' THEN 'NO' ELSE 'YES' END AS nullable FROM user_tab_columns WHERE table_name = 'table_not_null_2';
     Résultat de la requête : +-------------+----------+ | COLUMN_NAME | NULLABLE | +-------------+----------+ | ID1 | YES | +-------------+----------+
     
   • Insérer des valeurs nulles dans la table :
     insert into table_not_null_2(ID1) values(null);
     Résultat de la requête : Error starting at line 1 in command: insert into table_not_null_2(ID1) values(null) Error report: ORA-02290: check constraint (HR.CC_TABLE_NOT_NULL_2_ID1) violated *Cause: The values being inserted do not satisfy the named check *Action: do not insert values that violate the constraint.