Ad

How Can Be Unique Each Row In Sqlite Table. Not Just A Column Of Row

- 1 answer

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.

Ad

Answer

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

Ad
source: stackoverflow.com
Ad