CODEWITHSUNDEEP
sqliteprimary-key
Tambarskjelve
Tambarskjelve

Reputation: 73

Non-unique primary keys in SQLite database

I am trying to recreate a word list database by The Norwegian Language Bank in SQLite from the following table diagram:

Norsk Ordliste table diagram

I assume PK, FK and PF are abbreviations for Primary Key, Foreign Key and Primary/Foreign Key respectively. Each table is a separate CSV file from this archive, but for simplicity's sake I will only address the BOYING table in this question. First I create the table:

CREATE TABLE IF NOT EXISTS "BOYING" (
  "LOEPENR" integer NOT NULL,
  "BOY_NUMMER" integer NOT NULL PRIMARY KEY,
  "BOY_GRUPPE" varchar(32),
  "BOY_TEKST" varchar(32),
  "ORDBOK_TEKST" varchar(32)
);

Then I add the list of elements (shortened):

INSERT INTO BOYING (LOEPENR,BOY_NUMMER,BOY_GRUPPE,BOY_TEKST,ORDBOK_TEKST) VALUES
(1,1,'substantiv','ent ub','entall_ubestemt'),
(2,2,'substantiv','ent be','entall_bestemt'),
(3,3,'substantiv','fl ub','flertall_ubestemt'),
(4,4,'substantiv','fl be','flertall_bestemt'),
(5,1,'verb, normal','inf','infinitiv');

The error is caused by fifth element having the same value as the first element, resulting in UNIQUE constraint failed: BOYING.BOY_NUMMER. Primary keys have to be unique, so how did The Norwegian Language Bank build this database, and what can I do to solve this problem?

Upvotes: 0

Views: 394

Answers (1)

Miles
Miles

Reputation: 399

The diagram might be a simplified version that does not reflect the full way they are using it. Primary keys only work if they are unique. In case the most logical column to use for this is not unique though, it can be combined with another column. If the combination of these column is unique, they can together become a 'composite key'.

In this case that might be BOY_NUMMER and BOY_GRUPPE together. To make this table:

CREATE TABLE IF NOT EXISTS "BOYING" (
  "LOEPENR" integer NOT NULL,
  "BOY_NUMMER" integer NOT NULL,
  "BOY_GRUPPE" varchar(32) NOT NULL,
  "BOY_TEKST" varchar(32),
  "ORDBOK_TEKST" varchar(32),
  PRIMARY KEY ("BOY_NUMMER", "BOY_GRUPPE")
);

Or, if LOEPNER is unique:

CREATE TABLE IF NOT EXISTS "BOYING" (
  "LOEPENR" integer NOT NULL PRIMARY KEY,
  "BOY_NUMMER" integer NOT NULL,
  "BOY_GRUPPE" varchar(32),
  "BOY_TEKST" varchar(32),
  "ORDBOK_TEKST" varchar(32)
);

Upvotes: 1

Related Questions