How do you insert a record with a nullable column ?

Question

How do you insert a record with a nullable column ?

Answer 1

you omit that column from the insert statement, or set it to NULL

Answer 2

if column2 is null

insert into table(column1, column3) values (1, 3)

or just include it

insert into table(column1, column2, column3) values (1, null, 3)

Answer 3

You guys got me thinking: move -1 to the null indicator, To insert a valid value, move 0 to the null indicator.

Answer 4

always name all columns in the INSERT. If the table schema changes, your INSERTs are more likely to not break or work wrong. As a result, so just put a NULL in:

INSERT INTO YourTable (col1, col2, col2) VALUES (1, null, 'A')

EDIT
there are many ways for problems to happen, here is just one example of how schema change breaks poorly coded INSERTs:

YourTable
col1  int      not null
col2  int      null
col3  char(1)  null

you code this in several places:

INSERT INTO YourTable VALUES (1, null, 'A')
--OR this                             ---<<<<EDIT2
INSERT INTO YourTable VALUES (1, 'A') ---<<<<EDIT2

you change your table to:

YourTable
col1  int      not null
col1a int      null  default(0)
col2  int      null
col3  char(1)  null

what happens with your code? what values go in what columns?

if you had coded it this way

INSERT INTO YourTable (col1, col2, col2) VALUES (1, null, 'A')

it will still work