Results 1 to 1 of 1
  1. #1
    Join Date
    Sep 2013
    Posts
    1

    Unanswered: SQLite: UNIQUE Constraint on parent_id Using NULL

    EDIT
    After looking at the CREATE INDEX documentation on SQLite's website I saw the bottom that read:

    If the UNIQUE keyword appears between CREATE and INDEX then duplicate index entries are not allowed. Any attempt to insert a duplicate entry will result in an error. For the purposes of unique indices, all NULL values are considered to different from all other NULL values and are thus unique. This is one of the two possible interpretations of the SQL-92 standard (the language in the standard is ambiguous) and is the interpretation followed by PostgreSQL, MySQL, Firebird, and Oracle. Informix and Microsoft SQL Server follow the other interpretation of the standard.
    This is weak sauce, but at least there is a reason and I know I am not insane.

    ORIGINAL
    Hello dbforums Community,

    First off, I would like to say to hello to everyone and after looking around on your forums it seems like a very well kept community. I hope once I learn I can also give back by assisting others with their issues.

    Now as for my issue, I have a single table within my SQLite3 database. It is used to track a hierarchy within an application I am developing with C++. Below is the CREATE TABLE statement, and for simplicity sake I stripped it down to the bare essentials.

    Code:
    CREATE TABLE IF NOT EXISTS NODES(id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL COLLATE NOCASE, parent_id INTEGER REFERENCES NODES(id) ON DELETE CASCADE, UNIQUE(title, parent_id));
    So basically what I have here is a parent_id that references NODES.id, and a title of the node. To make it easy on myself I wanted to use constraints so that each title is unique under their parent. The only issue I am having is when the parent_id is NULL the UNIQUE constraint does not work. Below are some examples.

    Example 1: Works fine and raises an error.
    Code:
    INSERT INTO NODES(title, parent_id) VALUES('Hello', 10);
    INSERT INTO NODES(title, parent_id) VALUES('Hello', 10);
    Example 2: Does not work at all and will not raise an error.
    Code:
    INSERT INTO NODES(title, parent_id) VALUES('Hello', NULL);
    INSERT INTO NODES(title, parent_id) VALUES('Hello', NULL);
    I have been looking around the Internet for days and cannot find a solution to this, and to bypass this issue I had to hard code it into the C++ application using a SELECT statement. Is there some sort of PRAGMA I need to turn on or set, like I did with foreigh_keys, or is this by design? If so, could somebody please explain why this is?
    Last edited by OpenNox; 09-02-13 at 22:06. Reason: Sourcing Reason

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •