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 tutorial

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 tutorial

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;

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to How to add not null constraint in sql | NOT NULL Constraint command in SQL