Norman Norman - 4 months ago 16
SQL Question

NULL constraint after attribute in CREATE TABLE?

I'm new to SQL and I'm trying to figure out what this NULL is doing. Here a simple example:

CREATE TABLE test (
bla VARCHAR NULL
);


So I tried to figure out wether this is set to be the default value, but it is null as default wether I put it there or not, right?

Also I wondered if it has to stay null (for whatever reason) but when I tried to insert a value it was possible anyway. So does it do anything?

Answer

From CREATE TABLE:

NULL

The column is allowed to contain null values. This is the default.

You could write:

INSERT INTO test(bla)
VALUES (NULL);
-- it holds NULL

INSERT INTO test(bla)
VALUES (default);
-- it holds NULL

INSERT INTO test(bla)
VALUES ('a');
-- it holds 'a'

You could also omit column:

CREATE TABLE test2(bla VARCHAR NULL, col2 INT NOT NULL);

INSERT INTO test2(col2) VALUES (1);
-- it contains NULL, 1

If you specify column as:

CREATE TABLE test(bla VARCHAR NOT NULL);

INSERT INTO test(bla) VALUES (NULL);
-- error

EDIT:

You don't have to specify NULL explicitly.

CREATE TABLE test(bla VARCHAR);

is the same as:

CREATE TABLE test (bla VARCHAR NULL);
Comments