SQL: How to add a NULL value into the table and bypass the constraint check?

Question

I have a table

CREATE TABLE user (
  user_id   INT,
  user_name VARCHAR(100),
  CONSTRAINT pk_user_id PRIMARY KEY (user_id)
);

How can I bypass the constraint and add a new user with NULL id?

INSERT INTO user (user_id, user_name) VALUES (NULL, 'John');

Would this be at all possible? Maybe there is some hack or something.

I'm just learning SQL and want to know some tricks around it. — JohnWinter, Jun 21 '15 at 22:52
Remove the `primary key` constraint. If the column can take a `NULL` value, then it is not a primary key. — Gordon Linoff, Jun 21 '15 at 22:52
Maybe I can do that insert if I write some stored procedure/trigger to avoid constraint check? Is it possible? — JohnWinter, Jun 21 '15 at 22:54
Primary key values need to be unique and not `NULL`; those are [the rules](http://www.postgresql.org/docs/8.1/static/ddl-constraints.html) and there is no way around it. Lift the constraint if you need to allow `NULL` values. — Mick Mnemonic, Jun 21 '15 at 23:04
In fact, if you could do that then it'd be a bug. PostgreSQL *relies* on the fact that there can't be nulls in a `NOT NULL` column when planning queries; it also uses the knowledge that `UNIQUE` columns are in fact unique. Why would you possibly want to do this anyway? What are you attempting to achieve with this? — Craig Ringer, Jun 21 '15 at 23:23

score 1 · Answer 1 · answered Jun 21 '15 at 23:23

1

If you want to use null values in column that meant to be unique, then use unique index instead of primary key.

It will allow you to store any number of rows with null user_id in it (just because null <> null). So be careful.

Cheers.

answered Jun 21 '15 at 23:23

c-tran

score 0 · Answer 2 · answered Jun 21 '15 at 23:17

0

You can't, with the table defined as listed.

You can't change the fact that a primary key cannot accept NULL as a value, but you could alter the table and make that field not be a primary key.

answered Jun 21 '15 at 23:17

LDMJoe

2 Answers2