0

 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

Abhishek Verma
  • 396
  • 4
  • 14
  • Multiple nulls should be allowed. See https://stackoverflow.com/a/22699498/1261752. Are you certain that there are no other indexes or constraints on the table? – C Perkins Nov 11 '19 at 13:12
  • @C Perkins: Thank you for your comment. Yes, I checked multiple null values are allowed. Also, there are not other indexes or constraints on the table. @Mike T response is helpful. – Abhishek Verma Nov 13 '19 at 06:15

1 Answers1

3

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.

MikeT
  • 51,415
  • 16
  • 49
  • 68