CODEWITHSUNDEEP
sqlsqlite
Roonil
Roonil

Reputation: 536

SQLite UNIQUE constraint dependent on another column

I have a Java file that creates a subjects table in SQLite like so:

public static final String CREATE_SUBJECTS_TABLE = "create table " + SUBJECTS_TABLE_NAME +" (ID INTEGER PRIMARY KEY AUTOINCREMENT,SUBJECT TEXT,LEVEL INTEGER)";
db.execSQL(CREATE_SUBJECTS_TABLE);

If I wanted to make SUBJECT TEXT unique I could edit it to say SUBJECT TEXT UNIQUE, however this would mean I couldn't have the same subject at multiple levels. I want a way to be able to have the same subject at multiple levels, but not have a repeat of a subject with the same level. A UNIQUE constraint, but only within subjects with the same value in the level column.

I was wondering if there is a built in way to do this, or if I'll have to code it myself. If so, is there a common syntax that I should be using to achieve this?

Thanks

Upvotes: 0

Views: 94

Answers (1)

mu is too short
mu is too short

Reputation: 434665

Sounds like you actually want the (subject, level) pair to be unique. That would let you have duplicate subjects as long as the levels were different but duplicate subjects with the same level would not be allowed. A standard unique constraint supports this so you could add unique(subject, level) at the end of your table's definition:

public static final String CREATE_SUBJECTS_TABLE = "create table " + SUBJECTS_TABLE_NAME + " (..., unique(subject, level))";

Upvotes: 1

Related Questions