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

Adblocker detected! Please consider reading this notice.

We've detected that you are using AdBlock Plus or some other adblocking software which is preventing the page from fully loading.

We don't have any banner, Flash, animation, obnoxious sound, or popup ad. We do not implement these annoying types of ads!

We need money to operate the site, and almost all of it comes from our online advertising.

Please add wikitechy.com to your ad blocking whitelist or disable your adblocking software.

×