CODEWITHSUNDEEP
linuxsqliteconstraints
taylorbuckeye
taylorbuckeye

Reputation: 11

Why am I getting a UNIQUE Constraint Failure in my SQLite3 table

I have been searching StackOverflow, SQLite.com, and Google for help, but haven't found what I'm looking for yet.

I need to combine two older tables into one new table, but the PRIMARY KEY and UNIQUE constraints seem to be fighting with each other (I am getting a Constraint Failed error once it gets to the INSERT statement). The script will run if I remove the UNIQUE constraint, but then I have duplicate CamIndex records. 

CREATE TABLE tmpTable(
RowID      INTEGER   PRIMARY KEY AUTOINCREMENT,
CamIndex   INTEGER   UNIQUE NOT NULL, 
LenIndex   INTEGER   NOT NULL,
CamDesc    TEXT,
LenDesc    TEXT
);

INSERT INTO tmpTable(
CamIndex,
LenIndex,
CamDesc,
LenDesc
)

SELECT
TblA.CAM_INDEX,
TblB.LEN_INDEX, 
TblA.CAM_DESC,
TblB.LEN_DESC

FROM TblA
LEFT JOIN TblB on TblA.CAM_INDEX = TblB.CAM_INDEX;

How do I make the CamIndex column UNIQUE and keep RowID as Primary key?

This didn't work either:

CREATE TABLE tmpTable(
RowID      INTEGER   PRIMARY KEY AUTOINCREMENT,
CamIndex   INTEGER   NOT NULL, 
LenIndex   INTEGER   NOT NULL,
CamDesc    TEXT,
LenDesc    TEXT,
UNIQUE(CamIndex)
);

I'm using SQLite so I can't add the UNIQUE constraint with the ALTER Table statement.

Upvotes: 0

Views: 776

Answers (1)

Makoto
Makoto

Reputation: 106430

Your assumption about the uniqueness of your data is invalid. Given what you had remarked in the comments, the result of your SELECT statement produces the same CamIndex for different combinations of LenIndex, CamDesc, or LenDesc. Given that, you cannot place a unique constraint on CamIndex.

You will need to reevaluate your SELECT query to ensure that you're not getting bogus values from it before you can constrain your table.

Upvotes: 1

Related Questions