Stopping duplicate records in SQLite

Question

I'm creating a table in SQLite and need to stop certain duplicates before they are entered. For example:

This would be OK

[Product] = "Product A" & [Product Sub] = "Sub A"

[Product] = "Product B" & [Product Sub] = "Sub A"

However this would not be OK (and should not be saved):

[Product] = "Product A" & [Product Sub] = "Sub A"

Here is the code that I am using so far, but I don't know how to write the SQL correctly to achieve what I'm after:

CREATE TABLE IF NOT EXISTS [tblActivity_Sub] ([ActivityID_Sub] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, [Product] NVARCHAR(50) NOT NULL [Product Sub] NVARCHAR(50) NOT NULL UNIQUE)

Any help with this would be greatly appreciated (I am kind of new to this).

Thank you.

You can have unique constraints on multiple columns: http://stackoverflow.com/a/2701903/188595 — Michael Dunn, Mar 30 '14 at 13:53
That's perfect - thank you. As I'm new to this site, do I have to do anything else (that might help your ratings)? — AndyDB, Mar 30 '14 at 14:01
possible duplicate of [Sqlite table constraint - unique on multiple columns](http://stackoverflow.com/questions/2701877/sqlite-table-constraint-unique-on-multiple-columns) — CL., Mar 30 '14 at 15:37
It's fine. I think the question is a duplicate anyway, but otherwise, Samuel provided a proper, stand-alone answer. — Michael Dunn, Mar 31 '14 at 07:06

score 0 · Accepted Answer · answered Mar 30 '14 at 16:15

Add a unique constraint across the two columns.

CREATE TABLE IF NOT EXISTS [tblActivity_Sub] (
     [ActivityID_Sub] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, 
     [Product] NVARCHAR(50) NOT NULL,
     [Product Sub] NVARCHAR(50) NOT NULL,

     UNIQUE ([Product], [Product Sub]));

Stopping duplicate records in SQLite

1 Answers1