Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

how to set a column as unique indexer on Sqlite

I have 3 columns (_id, column1, column2) _id column has been set as autoincrement

In database there are some duplicate records, so I want to prevent duplicate records with setting column1 as unique indexer. How do I set a column as unique indexer on sqlite? Or how do I prevent duplicate records?

like image 890
Mustafa Güven Avatar asked Dec 17 '11 10:12

Mustafa Güven


People also ask

How do I create a unique field in SQLite?

Introduction to SQLite UNIQUE constraint To define a UNIQUE constraint, you use the UNIQUE keyword followed by one or more columns. You can define a UNIQUE constraint at the column or the table level. Only at the table level, you can define a UNIQUE constraint across multiple columns.

How do I index a column in SQLite?

1. Syntax. The CREATE INDEX command consists of the keywords "CREATE INDEX" followed by the name of the new index, the keyword "ON", the name of a previously created table that is to be indexed, and a parenthesized list of table column names and/or expressions that are used for the index key.

Does SQLite UNIQUE constraint create an index?

Yes, they are the same. A unique constraint in a table definition is exactly and precisely the same as an explicit unique index, save for what is different: an explicit UNIQUE index may contain expressions, a UNIQUE constraint in a table may only contain bare columns.


1 Answers

No magic, just SQL:

create table yourtablename (_id  integer primary key autoincrement, column1 text not null unique, column2 text); 

_id will not be duplicate in any way because it is primary key, column1 neither because it is unique.

like image 57
Noureddine AMRI Avatar answered Oct 06 '22 00:10

Noureddine AMRI