How Unique Index work with Nullable columns

Image for post
Image for post
Photo by Chris Brignola on Unsplash

Stranger Problem I met with unique index

import Knex from ‘knex’
const knex = Knex(…)
// create the table
knex.schema.createTable(‘People’, function(t) {
t.string(‘name’).notNullable()
t.string(‘title’)
t.unique([‘name’, ‘title’])
})
// insert two records with different values, it is fine
knex(‘People’).insert({name: ‘ron’, title: ‘Mr.’})
knex(‘People’).insert({name: ‘aaron’, title: ‘Dr.’})
// insert two records with same values, the second throw exception
knex(‘People’).insert({name: ‘jon’, title: ‘Mr.’})
knex(‘People’).insert({name: ‘jon ‘, title: ‘Mr.’}) // exception throw as expected
// insert two records with same values, the second throw exception
knex(‘People’).insert({name: ‘don’})
knex(‘People’).insert({name: ‘don ‘}) // exception did’t throw as expected
name title
don null
don null

Why it is happening?

create table Book (
name varchar(100) not null,
title varchar(100)
)
create unique index book_name_title_unique on Book(name, title)
insert into Book (name) values(‘alice’)
insert into Book (name) values(‘alice’)
## Cannot insert duplicate key row in object ‘dbo.Book’ with unique index ‘book_name_title_unique’. The duplicate key value is (alice, <NULL>).
name: RequestError
code: EREQUEST
number: 2601
lineNumber: 3
state: 1
class: 14
serverName: c74fc9e569aa
originalError: [object Object]
precedingErrors:
create unique index book_name_title_unique on Book(name, title) 
where name is not null and title is not null

How about other kind of databases?

create table Book (
name varchar(100) not null,
title varchar(100)
);
create unique index book_name_title_unique on Book(name, title);
insert into Book (name) values(‘alice’);
insert into Book (name) values(‘alice’);

Sum up

Written by

Full Stack Dev

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store