MySQL – NOT NULL Constraint
NOT NULL Constraint
By default, a column can hold NULL values. The “NOT NULL constraint” enforces a column to NOT accept 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.
This constraint specifies that the column cannot have NULL or empty values. The below statement creates a table with NOT NULL constraints.
- mysql> CREATE TABLE Student(Id INTEGER, LastName TEXT NOT NULL, FirstName TEXT NOT NULL, City VARCHAR(35));
Execute the queries listed below to understand how it works:
- mysql> INSERT INTO Student VALUES(1, ‘Hanks’, ‘Peter’, ‘New York’);
- mysql> INSERT INTO Student VALUES(2, NULL, ‘Amanda’, ‘Florida’);
You can run this query and see the result.
The first INSERT query executes correctly, but the second statement fails and gives an error that says column LastName cannot be null.
Not null constraints are a great way to add another layer of validation to your data. Sure, you could perform this validation in your application layer, but shit happens: somebody will forget to add the validation, somebody will remove it by accident, somebody will bypass validations in a console and insert nulls, etc.
The only way to really be sure is to enforce it in your column definition. If you’re validating nulls on the database layer as well, you’re protected