0

I would like to know if it is possible to insert a 'null' string in a column which has a NOT NULL constraint.

I'm using PostgreSQL, although this probably applies to other database management systems as well.

The query would look like the below

INSERT INTO originators(originator, id, regexp)
VALUES ('null', 1, -1);

This is the table design:

CREATE TABLE originators
(
      originator    varchar(30)    NOT NULL
    , id            int            NOT NULL
    , regexp        int            NOT NULL
);

Here the originator column has a NOT NULL constraint, so there should be no NULL values in the table.

However, when I query the table, I'm seeing this:

╔════════════╦════╦════════╗
║ originator ║ id ║ regexp ║
╠════════════╬════╬════════╣
║ NULL       ║  1 ║     -1 ║
╚════════════╩════╩════════╝

How is this possible?

0

1 Answer 1

6

'NULL' is not the same as NULL, the first one is an string just like any other string, so your constraint (NOT NULL) won't work.

I think you are working with a programming language (like PHP or Java) that you put your value inside a string with a variable like

INSERT INTO originators(originator, id, regexp) 
VALUES ('$variable', $id, $regexp); 

so in this case you have to correct your code to prevent the quotes, or if you can't do that you constraint should be CHECK(upper(originator) != 'NULL').

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.