|
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' );
Execute a ROLLBACK statement to undo the insert.
The DepartmentHeadID column has no default, but can allow NULL. A NULL is assigned to that column.
The order in which you list the column names must match the order in which you list the values. The following example produces the same results as the previous one:
INSERT INTO Departments ( DepartmentName, DepartmentID ) VALUES ( 'Western Sales', 703 );
Execute a ROLLBACK statement to undo the insert.
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:
| Value being inserted | Nullable | Not nullable | Nullable, with DEFAULT | Not nullable, with DEFAULT | Not nullable, with DEFAULT AUTOINCREMENT |
|---|---|---|---|---|---|
| <none> | NULL | SQL error | DEFAULT value | DEFAULT value | DEFAULT value |
| NULL | NULL | SQL error | NULL | SQL error | DEFAULT value |
| specified value | specified value | specified value | specified value | specified value | specified value |
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.
You can create constraints for a column or domain. Constraints govern the kind of data you can or cannot add.
For more information on constraints, see Using table and column constraints.
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 );
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.
For more information about defaults, see Using column defaults.