How to add not null constraint in sql | NOT NULL Constraint command in SQL - sql - sql tutorial - learn sql
- SQL NOT NULL constraint enforces to a column is always contain a value. This means that’s you can not insert NULL (blank) value in this field.
- NOT NULL constraint applied only at column level. You should manually define NOT NULL constraint because table column set NULL value.
- By default, a column can hold NULL values.
- This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.
- The following SQL ensures that the "ID", "LastName", and "FirstName" columns will NOT accept NULL values:
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialSQL NOT NULL Constraint Rules:
SQL NOT NULL Constraint Rules:
- A NULL values is different from a blank or zero.
- A NULL value can be inserted into the columns of any Datatype.
Example:
CREATE TABLE Student (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);NOT NULL Defined only at Column level
- Define NOT NULL constraint at column level with other column attributes,
CREATE TABLE table_name(
column_name datatype[(size)] NOT NULL,
column_name datatype[(size)] NOT NULL,
....
);
Example Statement :
SQL> CREATE TABLE emp_info(
no NUMBER(3,0) PRIMARY KEY,
name VARCHAR(30) NOT NULL,
address VARCHAR(70)
);
- Table created.
SQL> DESCRIBE emp_info;
| Name | Null? | Type |
|---|---|---|
| NO | NOT NULL | NUMBER(3) |
| NAME | NOT NULL | VARCHAR2(30) |
| ADDRESS | - | VARCHAR2(70) |
SQL> DESCRIBE emp_info;
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialADD NOT NULL constraint (ALTER TABLE)
ADD NOT NULL constraint (ALTER TABLE)
- ALTER TABLE statement to add NOT NULL constraint in existing table column.
Syntax:
ALTER TABLE table_name
MODIFY column_name datatype[(size)] NOT NULL;
Example Statement:
SQL> ALTER TABLE emp_info MODIFY address VARCHAR2(70) NOT NULL;
- Table altered.
SQL> DESCRIBE emp_info;
| Name | Null? | Type |
|---|---|---|
| NO | NOT NULL | NUMBER(3) |
| NAME | NOT NULL | VARCHAR2(30) |
| ADDRESS | NOT NULL | VARCHAR2(70) |
SQL> DESCRIBE emp_info;
DROP NOT NULL constraint (ALTER TABLE)
- ALTER TABLE statement to drop NOT NULL constraint in existing table column.
Syntax:
ALTER TABLE table_name
MODIFY column_name datatype[(size)] NULL;
Example Statement:
SQL> ALTER TABLE emp_info MODIFY address VARCHAR2(70) NULL;
- Table altered.
SQL> DESCRIBE emp_info;