SQL Constraints are rules used to limit the type of data that can go into a table, to maintain the accuracy and integrity of the data inside table.
Constraints provide a standard mechanism to maintain the accuracy and integrity of the data inside a database table.
A Not Null constraint ensures that the column in the table cannot have Null values, however it may have duplicate values.
This means if NOT NULL
constraint is applied on a column then you cannot insert a new row in the table without adding a non-NULL
value for that column.
Datatype | Action | Values |
---|---|---|
NUMBER | Error Produce | blank, space, special character, NULL |
NUMBER | Allow Only | O or 1 to 9 |
VARCHAR2 | Error Produce | blank, space, special character, NULL |
VARCHAR2 | blank | NULL, space, special character |
CREATE TABLE table_name( column_name datatype[(size)] NOT NULL, column_name datatype[(size)] NOT NULL, .... );
SQL> CREATE TABLE std_info( no NUMBER(3,0) PRIMARY KEY, name VARCHAR(30) NOT NULL, address VARCHAR(70) ); ----------------------------- Table created. -----------------------------
Now, let's Describe(view) the table std_info
we have just created
SQL> DESCRIBE std_info; Name Null? Type ---------------------------- --------- ---------------------------- NO NOT NULL NUMBER(3) NAME NOT NULL VARCHAR2(30) ADDRESS VARCHAR2(70)
ALTER TABLE statement to add NOT NULL constraint in existing table column.
ALTER TABLE table_name MODIFY column_name datatype[(size)] NOT NULL;
SQL> ALTER TABLE std_info MODIFY address VARCHAR2(70) NOT NULL; -------------- Table altered.
Now, let's Describe(view) the table std_info
we have just created
SQL> DESCRIBE std_info; Name Null? Type ---------------------------- --------- ---------------------------- NO NOT NULL NUMBER(3) NAME NOT NULL VARCHAR2(30) ADDRESS NOT NULL VARCHAR2(70)