SQL NOT NULL |
SQL Sample Code - SQL NOT NULL |
The NOT NULL constraint is usually defined using CREATE TABLE:
| CREATE TABLE table_name ( column_name1 column1_data_type, column_name2 column2_data_type NOT NULL, .... ); |
NULL basically means Blank.
See the SQL samples section below for an explanation of the NOT NULL constraint.
What does the NOT NULL constraint do? |
When creating a database table, columns allow NULL values by default. A column can be defined so that it cannot allow NULL values.
In other words, when adding rows to a column it will force the inputter to input a value into the row of that column.
This is achieved by using the NOT NULL constraint within a CREATE TABLE or ALTER TABLE statement.
Sample 1 – SQL NOT NULL constraint (using CREATE TABLE) |
In this SQL sample we will create a table called ‘tblCompany’ with 3 columns; Company_ID, Company_Name and Sales. We do not want values in the Company_Name column to be left blank (NULLs).
Use the CREATE TABLE statement below:
| CREATE TABLE tblCompany (Company_ID int Company_Name varchar(255) NOT NULL, Sales int); |
The table will look like this:
| Company_ID | Company_Name | Sales |
The ‘int’, ‘varchar(255)’ elements of the statement are the data types. To learn more about these and more go to the Data Type section of this website.
Example 2 – SQL NOT NULL constraint (using ALTER TABLE). |
Warning: Be careful when using the ALTER TABLE together with the NOT NULL constraint. If you already have NULL values in the column you want to change this will cause errors.
By default when we created the ‘tblCompany’ in Example1 above, the Sales column allows NULL values to be entered. We now want to ensure that values are entered into this column.
Use the ALTER TABLE statement below:
| ALTER TABLE tblCompany ( Sales int NOT NULL); |
The ‘int’, ‘varchar(255)’ elements of the statement are the data types. To learn more about these and more go to the Data Type sections of this website.
Related SQL Sample Code:
|
|