You can add data to some columns in a row by specifying only those columns and their values. Define all other columns not included in the column list to allow NULL or have defaults. If you skip a column that has a default value, the default appears in that column.

Adding data in only two columns, for example, DepartmentID and DepartmentName, requires a statement like this:

INSERT INTO Departments ( DepartmentID, DepartmentName )
VALUES ( 703, 'Western Sales' );

DepartmentHeadID does not have a default value but accepts NULL. therefore a NULL is automatically assigned to that column.

Cancel these changes to the database by entering a ROLLBACK statement:

ROLLBACK;

While the column order you specify does not need to match the order of columns in the table, it must match the order in which you specify the values you are inserting.

Inserted values for specified and unspecified columns

Values are inserted in a row according to what is specified in the INSERT statement. If no value is specified for a column, the inserted value depends on column settings such as whether to allow NULLs, whether to use a DEFAULT, and so on. In some cases, the insert operation may fail and return an error. The following table shows the possible outcomes depending on the value being inserted (if any) and the column settings:

By default, columns allow NULL values unless you explicitly state NOT NULL in the column definition when creating a table. You can alter this default using the allow_nulls_by_default option. You can also alter whether a specific column allows NULLs using the ALTER TABLE statement. See allow_nulls_by_default option [compatibility] and ALTER TABLE statement.

Restricting column data using constraints

You can create constraints for a column or domain. Constraints govern the kind of data you can or cannot add. See Using table and column constraints.

Explicitly inserting NULL

You can explicitly insert NULL into a column by entering NULL. Do not enclose this in quotes, or it will be taken as a string. For example, the following statement explicitly inserts NULL into the DepartmentHeadID column:

INSERT INTO Departments
VALUES ( 703, 'Western Sales', NULL );

Using defaults to supply values

You can define a column so that, even though the column receives no value, a default value automatically appears whenever a row is inserted. You do this by supplying a default for the column. See Using column defaults.