How to create, check, and delete MSSQL NOT NULL constraints
NOT NULL constraints require data to be entered when entering or modifying.
NOT NULL constraint features
- An ERROR occurs when NULL is entered in a column to which the NOT NULL constraint is applied.
- In other words, a value always exists in the column.
- Data Integrity: NOT NULL constraints help maintain data integrity.
- For example, you can maintain integrity by applying a NOT NULL constraint to columns that store required input information such as user ID, name, and address.
- When inserting data into a column with a NOT NULL constraint, an error will occur if a value is not specified for the column.
- To prevent this, NOT NULL is set as the default and is mainly used.
- ALTER TABLE can be changed using the ALTER TABLE statement to add or remove a NOT NULL constraint to a column in an already existing table.
How to use NOT NULL constraint
Automatically sets CHECK on column while creating TABLE.
- When creating a table, you can use the NOT NULL constraint after the column and datatype.
- The NOT NULL constraint is a constraint frequently used when creating tables.
- Mainly used in columns related to keys.
- Please practice using syntax code and source code.
-- syntax code
CREATE TABLE [table name] (
[column1] [dataType],
[column2] [dataType] NOT NULL,
[column3] [dataType] NOT NULL
);
-- 1. When creating a TABLE, automatically set NOT NULL to the column.
USE sampleDB;
DROP TABLE IF EXISTS AurumGuideForNotNull;
CREATE TABLE AurumGuideForNotNull (
UserId int,
UserNm varchar(255) NOT NULL,
UserAge int NOT NULL,
UserAddress varchar(500) NOT NULL
);
-- Check if the NOT NULL constraint is working.
insert into AurumGuideForNotNull(UserId,UserNm,UserAge,UserAddress)
values(202401,'john',10,NULL);
Create NOT NULL constraint on an existing column.
- Similar to other constraints, you can specify not nul in a column through the alter command.
- The not nul condition is created after the datatype reserved word.
-- syntax.
ALTER TABLE [table name] ALTER COLUMN [column1] [dataType] NOT NULL;
USE sampleDB;
DROP TABLE IF EXISTS AurumGuideForNotNull;
CREATE TABLE AurumGuideForNotNull (
UserId int,
UserNm varchar(255),
UserAge int,
UserAddress varchar(500)
);
ALTER TABLE AurumGuideForNotNull ALTER COLUMN UserAddress VARCHAR(500) NOT NULL;
When an existing column contains NULL values.
- If the column you want to change is null, the property cannot be changed to NOT NULL.
- As a solution, you need to add a value to the existing column.
- This is an example of an error occurring.
- Try using images to generate errors through practice.
How to resolve NULL value in column.
- It would be nice to include a not null constraint when creating a table, but if the table contains null values, additional work is required.
- You must check the existing data and delete or modify the row.
- If modification is necessary, change the value of the corresponding column using the NOT NULL constraint.
- Understand and practice the example source first.
USE sampleDB;
DROP TABLE IF EXISTS AurumGuideForNotNull;
CREATE TABLE AurumGuideForNotNull (
UserId int,
UserNm varchar(255),
UserAge int ,
UserAddress varchar(500)
);
insert into AurumGuideForNotNull(UserId,UserNm,UserAge,UserAddress)
values(202401,'john',10,NULL);
insert into AurumGuideForNotNull(UserId,UserNm,UserAge,UserAddress)
values(202402,'john1',10,NULL);
-- Check for null values in existing columns.
select *
from AurumGuideForNotNull
where UserAddress is null;
-- null value in existing column => Changed to ''.
update AurumGuideForNotNull
set UserAddress = ''
where UserAddress is null;
-- change to existing column.
ALTER TABLE AurumGuideForNotNull ALTER COLUMN UserAddress VARCHAR(500) NOT NULL;
- This is an explanation of the execution process.
How to check NOT NULL constraint creation.
- You can use the EXEC SP_HELP [table name] command..
- Use the INFORMATION_SCHEMA.COLUMNS schema.
- When finding the constraints of one table, use the sp_help command.
- Use INFORMATION_SCHEMA.COLUMNS to find which tables in the database use constraints.
-- instruction code
EXEC SP_HELP AurumGuideForNotNull;
-- INFORMATION_SCHEMA.COLUMNS
SELECT TABLE_CATALOG
,TABLE_SCHEMA
,TABLE_NAME
,IS_NULLABLE
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'AurumGuideForNotNull';
Delete the created NOT NULL constraint.
- If you have created a Not null constraint, you can also drop it.
- Please practice using the Not null constraint syntax and source code.
ALTER TABLE [table name] ALTER COLUMN [column1] [dataType];
USE sampleDB;
DROP TABLE IF EXISTS AurumGuideForNotNull;
CREATE TABLE AurumGuideForNotNull (
UserId int,
UserNm varchar(255) NOT NULL,
UserAge int NOT NULL,
UserAddress varchar(500) NOT NULL
);
-- remove not null
ALTER TABLE AurumGuideForNotNull ALTER COLUMN UserAddress VARCHAR(500);
Not null constraint finalization
- Not null constraints are mainly used when creating tables.
- When adding a Not null constraint during operation, check the existing data and change the constraint.
Leave a comment