CODEWITHSUNDEEP
sql
Yves
Yves

Reputation: 12497

How do you insert a record with a nullable column?

How do you insert a record with a nullable column ?

Upvotes: 1

Views: 4030

Answers (4)

KM.
KM.

Reputation: 103589

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

Upvotes: 3

Yves
Yves

Reputation: 12497

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

Upvotes: 0

Martin
Martin

Reputation: 11041

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)

Upvotes: 5

northpole
northpole

Reputation: 10346

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

Upvotes: 8

Related Questions