CODEWITHSUNDEEP
androidsqliteandroid-sqlite
user4570857
user4570857

Reputation:

SQLIte how to insert unique data on change of column value

I am using SQLite Database for my application. I have 4 columns- Student_Name,Student_Enroll, Student_Mob, Student_Address in my database. Now I can add new record if and only if one of four column value is different or all values are different. If all column values are same then no new record should be generated. Can you please guide me to solve this issue?

Upvotes: 0

Views: 2249

Answers (4)

Atlas_Gondal
Atlas_Gondal

Reputation: 2552

Solution 1: (Simple)

Define all columns as unique:

create table TableName (id  integer primary key autoincrement,
Student_Name text not null unique, 
Student_Enroll text not null unique, 
Student_Mob text not null unique);

You can add Student_Address as well, if you need to

Solution 2: (bit complex)

Use AND Operator with WHERE clause 

INSERT INTO TableName (Student_Name, Student_Enroll, Student_Mob)
SELECT varStudentName, varStudentEnroll, varStudentMob 
WHERE NOT EXISTS(SELECT 1 FROM TableName WHERE Student_Name = varStudentName OR Student_Enroll = varStudentEnroll OR Student_Mob = varStudentMob ); 

//If a record already contains a row, then the insert operation will be ignored.

You can find more information at the sqlite manual.

Live Example:

  1. Open SQLite Online

  2. Paste following code:

    INSERT INTO demo (id,name,hint)
SELECT 4, 'jQuery', 'is a cross-platform JavaScript library designed to simplify the client-side scripting of HTML' 
WHERE NOT EXISTS(SELECT 1 FROM demo WHERE name = 'jQuery' OR hint = 'is a cross-platform JavaScript library designed to simplify the client-side scripting of HTML' ); 

SELECT * from demo

  3. Hit RUN

This won't insert 4th record and if you modify both values of WHERE clause then record will be inserted.

Upvotes: 0

CL.
CL.

Reputation: 180080

To enforce that a set of columns must be unique, add a UNIQUE constraint:

create table Students (
 /* ID              INTEGER PRIMARY KEY, */
    Student_Name    TEXT,
    Student_Enroll  TEXT,
    Student_Mob     TEXT,
    Student_Address TEXT,
    UNIQUE (Student_Name, Student_Enroll, Student_Mob, Student_Address)
);

This allows new rows only if at least one of the four columns has a different value.

With a plain INSERT, attempting to insert a duplicate row will result in an error. If you simply want to ignore it instead, use INSERT OR IGNORE:

INSERT OR IGNORE INTO Students ...;

Upvotes: 4

AlexTa
AlexTa

Reputation: 5251

Despite of set your column as UNIQUE you also need to resolve the conflict created on each column when you try to insert new data.

To do so, define the behavior to solve the conflict:

"CREATE TABLE table (your columns here...(UNIQUE unique colums here...) ON CONFLICT REPLACE);"

Upvotes: 3

user8119020
user8119020

Reputation: 117

During Create Database line insert UNIQUE ...for each column to insert only unique record.

Upvotes: 0

Related Questions