CODEWITHSUNDEEP
androidsqliteunique
Hacettepe Hesabı
Hacettepe Hesabı

Reputation: 186

How can be unique each row in sqlite table. Not just a column of row

I have a sqlite table with two column like name and surname .I want name and surname must be different not just name.

My table code is 

private static final String MAKE_TABLE_NAME_SURNAME = "CREATE TABLE " +
 TABLE_DISEASE_SEMPTOM + "(" + KEY_DSID  " INTEGER PRIMARY KEY AUTOINCREMENT," +
 NAME + " TEXT, " + SURNAME + " TEXT) ";

I can't use

private static final String MAKE_TABLE_NAME_SURNAME = "CREATE TABLE " +
 TABLE_DISEASE_SEMPTOM + "(" + KEY_DSID  " INTEGER PRIMARY KEY AUTOINCREMENT," +
 NAME + " TEXT UNIQUE, " + SURNAME + " TEXT UNIQUE) ";

Because if I use this code then I can't add same name but different Surname or same Surname but different name. How can ignore if only both of them is same.

Upvotes: 2

Views: 110

Answers (2)

MikeT
MikeT

Reputation: 56953

I believe the following is what you want :-

private static final String MAKE_TABLE_NAME_SURNAME = "CREATE TABLE " +
   TABLE_DISEASE_SEMPTOM + "(" + KEY_DSID  " INTEGER PRIMARY KEY AUTOINCREMENT," +
   NAME + " TEXT, " + SURNAME + " TEXT, UNIQUE(" + NAME + "," + SURNAME +")) ";

That is you are defining a combination of the name column and the surname columns as the UNIQUE constraint.

For example consider :-

DROP TABLE IF EXISTS TABLE_DISEASE_SEMPTOM;
CREATE TABLE IF NOT EXISTS TABLE_DISEASE_SEMPTOM (KEY_DSID INTEGER PRIMARY KEY AUTOINCREMENT, NAME TEXT, SURNAME TEXT, UNIQUE(NAME,SURNAME));
INSERT INTO TABLE_DISEASE_SEMPTOM (NAME,SURNAME) VALUES('Fred','Bloggs'),('Fred','Smith'),('Mary','Smith'),('Jane','Doe');
INSERT OR IGNORE INTO TABLE_DISEASE_SEMPTOM (NAME,SURNAME) VALUES('Fred','Bloggs'); -- will not be inserted as duplicate
SELECT * FROM TABLE_DISEASE_SEMPTOM;

The first 4 inserts work, the last (on it's own) is ignored (as it's a duplicate). Thus the final query produces :-

enter image description here

The log is :-

INSERT INTO TABLE_DISEASE_SEMPTOM (NAME,SURNAME) VALUES('Fred','Bloggs'),('Fred','Smith'),('Mary','Smith'),('Jane','Doe')
> Affected rows: 4
> Time: 0.117s


INSERT OR IGNORE INTO TABLE_DISEASE_SEMPTOM (NAME,SURNAME) VALUES('Fred','Bloggs')
> Affected rows: 0
> Time: 0.117s

i.e. the last insert inserted no rows

Upvotes: 1

Joe
Joe

Reputation: 2243

This check is done on the server/app side. (check your variables before trying to insert them into your database)

Upvotes: 1

Related Questions