CODEWITHSUNDEEP
sqliteunique-constraint
Abhishek Verma
Abhishek Verma

Reputation: 426

Ambiguous error message when trying to insert NULL value for UNIQUE attribute in a table


 CREATE TABLE access_type(
 id NUMBER NOT NULL PRIMARY KEY,
 access_type VARCHAR(10) UNIQUE,
 access_value VARCHAR(2) UNIQUE
 );

Case I:

When I tried to insert NULL for access_value attribute, i got below output:

sqlite> insert into access_type (id,access_type,access_value) values (3,'execute',NULL);
Error: UNIQUE constraint failed: access_type.access_type

Case II:

When I tried to insert NULL for access_type attribute, i got below output:

sqlite> insert into access_type (id,access_type,access_value) values (3,NULL,'E');
Error: UNIQUE constraint failed: access_type.access_value

I want to know why sqlite3 is giving error for access_type when I am inserting NULL for access_value in the first case and giving error for access_value when inserting NULL for access_type. Please help

Upvotes: 0

Views: 100

Answers (1)

MikeT
MikeT

Reputation: 56958

Case 1 didn't fail because of the NULL (NULL is considred different to all other NULLs) it failed because there was already a row that has execute in the access_type column.

i.e. the messages tells you that the UNIQUE contrainst that failed (was in conflict) was the access_type column.

Case 2 likewise failed due to another row having 3 in the id column.

Perhaps consider the following (note the DROP TABLE so that it is rerunnable) :-

DROP TABLE IF EXISTS access_type;
CREATE TABLE IF NOT EXISTS access_type(
 id NUMBER NOT NULL PRIMARY KEY,
 access_type VARCHAR(10) UNIQUE,
 access_value VARCHAR(2) UNIQUE
 );
 insert into access_type (id,access_type,access_value) values (3,'execute',NULL);
 insert into access_type (id,access_type,access_value) values (4,NULL,'E');
 insert into access_type (id,access_type,access_value) values (5,NULL,NULL);

When run the messages are :-

insert into access_type (id,access_type,access_value) values (3,'execute',NULL)
> Affected rows: 1
> Time: 0.091s


-- insert into access_type (id,access_type,access_value) values (3,NULL,'E'); /* fails because id  3 has been used */
 insert into access_type (id,access_type,access_value) values (4,NULL,'E')
> Affected rows: 1
> Time: 0.095s


insert into access_type (id,access_type,access_value) values (5,NULL,NULL)
> Affected rows: 1
> Time: 0.107s

You probably want to review the use of UNIQUE as you are imposing quite a strict set of terms. At a guess you would want a number of rows to have execute as the access_type and you probably want the same access_value. What you possibly do not want is the same id and access_type and/or the same access_value.

So you might want ID 3 with an access type of exceute and ID 3 also having a row for a noop type. You then probably want a compound PRIMARY KEY or UNIQUE index.

As an example conisder the following adaptation :-

DROP TABLE IF EXISTS access_type;
CREATE TABLE IF NOT EXISTS access_type(
 id NUMBER NOT NULL /* PRIMARY KEY */ /* PRIMARY KEY IMPLIES UNIQUE */,
 access_type VARCHAR(10) /* UNIQUE */,
 access_value VARCHAR(2) /* UNIQUE */ , /*<<<<< added comma */
 PRIMARY KEY (id,access_type) /*<<<<< compound PRIMARY KEY */
 );
 insert into access_type (id,access_type,access_value) values (3,'execute',NULL);
 insert into access_type (id,access_type,access_value) values (3,'noop',NULL);
 insert into access_type (id,access_type,access_value) values (4,NULL,'E');
 insert into access_type (id,access_type,access_value) values (5,NULL,NULL);

However, if insert into access_type (id,access_type,access_value) values (3,'execute',NULL); were tried then it would fail.

You might also want to consider INSERT OR IGNORE in which case the UNIQUE constraint conflict would be a NOOP rather than a failure.

Upvotes: 3

Related Questions