When creating a table, add a NOT NULL constraint to the column in the following form:
column_name Data_type
[constraint constraint_name] NOT NULL
Where constraint constraint_name represents the specified name for the constraint.
You can also add a NOT NULL constraint to a table that you have created, and you need to use the ALTER TABLE ... modify statement in the following form:
ALTER TABLE table_name MODIFY COLUMN_NAME [constraint constraint_name] NOT null;
Remove NOT NULL constraint
If you need to remove the NOT NULL constraint for a laceration in the table, you still use the ALTER TABLE...MODIFY statement, in the following form:
ALTER TABLE table_name MODIFY COLUMN_NAME NULL;
The following are the specific actions:
sql> CREATE TABLE person (
2 PID Number (4) NOT NULL,
3 pname varchar2 (20),
4 Psex char (2)
5);
The table is created.
sql> desc person;
is the name empty? Type
----------------------------------------- -------- -------------------
PID not NULL number (4)
PNAME VARCHAR2 (20)
Psex CHAR (2)
Sql> ALTER TABLE person modify pname NOT null;
The table has changed.
sql> desc person;
is the name empty? Type
----------------------------------------- -------- ------------------
PID not NULL number (4)
PNAME not NULL VARCHAR2 (20)
Psex CHAR (2)
Sql> INSERT into the person values (1, ' aaa ', ' female ');
1 rows have been created.
Sql> INSERT into the person values (1, ' AAA ', NULL);
1 rows have been created.
sql> INSERT into person values (1,null,null);
INSERT into person VALUES (1,null,null) *
An error occurred on line 1th:
ORA-01400: Could not insert NULL ("SYSTEM". " Person "." PNAME ")
Sql> ALTER TABLE person modify PNAME null;
The table has changed.
sql> desc person;
is the name empty? Type
----------------------------------------- -------- ------------------
PID not NULL number (4)
PNAME VARCHAR2 (20)
Psex CHAR (2)
sql> INSERT into person values (1,null,null);
1 rows have been created.
Sql>
Oracle Add NOT NULL constraint
