Meritshot Tutorials
SQL Tutorial
-
SQL SyntaxSQL Syntax
-
SQL ORDER BY ClauseSQL ORDER BY Clause
-
Introduction to SQLIntroduction to SQL
-
SQL SELECT DISTINCTSQL SELECT DISTINCT
-
SQL Logical OperatorsSQL Logical Operators
-
SQL WHERE ClauseSQL WHERE Clause
-
SQL SELECT StatementSQL SELECT Statement
-
SQL DELETE StatementSQL DELETE Statement
-
SQL INSERT INTOSQL INSERT INTO
-
SQL Null ValuesSQL Null Values
-
SQL Update StatementSQL Update Statement
-
SQL Select TopSQL Select Top
-
SQL Aggregate FunctionsSQL Aggregate Functions
-
SQL LIKE and WildcardsSQL LIKE and Wildcards
-
SQL IN and SQL BETWEENSQL IN and SQL BETWEEN
-
SQL JOINSSQL JOINS
-
SQL Group BySQL Group By
-
SQL HavingSQL Having
-
SQL EXISTSSQL EXISTS
-
SQL SELECT INTOSQL SELECT INTO
-
SQL INSERT INTO SELECTSQL INSERT INTO SELECT
-
SQL CASE STATEMENTSQL CASE STATEMENT
-
SQL NULL FunctionsSQL NULL Functions
-
SQL Stored ProceduresSQL Stored Procedures
-
SQL User-Defined FunctionsSQL User-Defined Functions
-
SQL CommentsSQL Comments
-
SQL OperatorsSQL Operators
-
SQL Database Creation and ManagementSQL Database Creation and Management
-
SQL CREATE DATABASE StatementSQL CREATE DATABASE Statement
-
SQL CREATE TABLE StatementSQL CREATE TABLE Statement
-
SQL DROP DATABASE StatementSQL DROP DATABASE Statement
-
SQL DROP TABLE StatementSQL DROP TABLE Statement
-
SQL ALTER TABLE StatementSQL ALTER TABLE Statement
-
SQL NOT NULL ConstraintSQL NOT NULL Constraint
-
SQL UNIQUE ConstraintSQL UNIQUE Constraint
-
SQL PRIMARY KEY ConstraintSQL PRIMARY KEY Constraint
-
SQL FOREIGN KEY ConstraintSQL FOREIGN KEY Constraint
-
SQL CHECK ConstraintSQL CHECK Constraint
-
SQL DEFAULT ConstraintSQL DEFAULT Constraint
-
SQL IndexesSQL Indexes
-
SQL Date FunctionsSQL Date Functions
-
SQL ViewsSQL Views
-
SQL InjectionSQL Injection
-
SQL Data Types OverviewSQL Data Types Overview
-
SQL AUTO_INCREMENTSQL AUTO_INCREMENT
-
SQL Keywords ReferenceSQL Keywords Reference
SQL NOT NULL Constraint
The NOT NULL constraint in SQL is used to ensure that a column cannot have a NULL value. This means that when you insert or update data in a table, the column must contain a valid, non-empty value. This constraint is important for maintaining data integrity and ensuring that essential information is always present.
Syntax
CREATE TABLE table_name (
column_name datatype NOT NULL,
…
);
- column_name: The name of the column to which the NOT NULL constraint is applied.
- datatype: The type of data that the column will store (e.g., INT, VARCHAR).
Example:
1 Creating a Table with NOT NULL Constraint
Suppose you want to create a table called Employees where the EmployeeID and FullName columns must not be NULL:
CREATE TABLE Employees (
EmployeeID INT NOT NULL,
FullName VARCHAR(100) NOT NULL,
HireDate DATE,
Salary DECIMAL(10, 2)
);
In this table, EmployeeID and FullName cannot be NULL, meaning every employee must have a unique ID and a full name.
2 Adding NOT NULL Constraint to an Existing Column
If you have an existing table Students and want to ensure that the Email column cannot be NULL, you can alter the table:
ALTER TABLE Students
MODIFY COLUMN Email VARCHAR(100) NOT NULL;
This command updates the Email column in the Students table to require a non-null value.
3 Inserting Data with NOT NULL Constraint
When inserting data into the Employees table, you must provide values for the EmployeeID and FullName columns:
INSERT INTO Employees (EmployeeID, FullName, HireDate, Salary)
VALUES (1, ‘Amit Sharma’, ‘2024-08-15’, 50000.00);
Here, EmployeeID and FullName are provided, fulfilling the NOT NULL constraint.
4 Updating Data to Meet NOT NULL Constraint
If you need to update the Salary column in the Employees table while ensuring that no column with NOT NULL constraints is left without a value:
UPDATE Employees
SET Salary = 55000.00
WHERE EmployeeID = 1;
In this case, the Salary column, which does not have a NOT NULL constraint, is updated, while EmployeeID and FullName are already guaranteed to be non-null.
Tips to Remember
- Enforce Data Integrity: Use NOT NULL constraints to ensure that essential data is always provided.
- Consider Application Logic: Ensure that application logic aligns with NOT NULL constraints to prevent errors during data insertion or updates.
- Combine with Other Constraints: Often used in combination with other constraints like PRIMARY KEY or UNIQUE for better data integrity.
Frequently Asked Questions
Q1: Can I apply NOT NULL to columns that already contain NULL values?
A1: No, you must first update or remove NULL values from the column before applying the NOT NULL constraint.
Q2: What happens if I try to insert a NULL value into a column with a NOT NULL constraint?
A2: SQL will return an error, preventing the insertion of a row with a NULL value in that column.
Q3: Can I remove a NOT NULL constraint from a column?
A3: Yes, you can use ALTER TABLE to modify the column and remove the NOT NULL constraint.
Q4: How do I check if a column has a NOT NULL constraint?
A4: Use a DESCRIBE or SHOW COLUMNS command to inspect the column properties and constraints.
Q5: Is it possible to have a NOT NULL constraint on a column that allows default values?
A5: Yes, a column can have a NOT NULL constraint and also have a default value specified, ensuring that the column always has a valid value.
