Several Primary Key in SQLite3

Question

Several Primary Key in SQLite3

Navigation

#1 by (2 votes)
#2 by (1 votes)

2

Hello, I want a table in SQLite that has 2 fields as primary key and also a field% co_of% that is unique integer. What I've been looking for is something like this:

create table t (id integer, name string, phone string, nick string PRIMARY KEY(name, phone);

So far so good, but the field id is not created as an integer id . I should have defined it as:

create table t (id integer PRIMARY KEY AUTOINCREMENT, .......)

But then I can not put the other 2 fields as autoincrement . For the field primary key in principle I will not search, I only want it to be a single field. I have tried different variations and it tells me that the table id has more than one t .

Thanks for the collaboration.

sql sqlite3

asked by Spidvmp 11.08.2016 в 18:44

source

2 answers

2

I would recommend you use the ID as the primary key and use the uniqueness restriction in name and phone:

See link

create table t (
 id integer PRIMARY KEY AUTOINCREMENT, 
 name string, 
 phone string, 
 nick string );

CREATE UNIQUE INDEX i1 ON t(phone, name);

answered by 11.08.2016 в 19:25

Error connecting Oracle SQL Developer 4.1.2.20 to Oracle XE 11.2 in UBUNTU Floating tables in the database model? [closed]

score 1 · Accepted Answer

We start from the following data:

Only one primary key can exist in a table.
PRIMARY KEY AUTOINCREMENT is an alias of ROWID

The first one prevents you from defining your id field as autoincremented and, later, you use it as a primary key next to name and phone , but you forget that a UNIQUE INDEX is functionally the same as a PRIMARY KEY:

create table t (
  id integer PRIMARY KEY AUTOINCREMENT,
  name string,
  phone string,
  nick string,
  UNIQUE INDEX(id, name, phone)
);

Functionally you have solved your problem with the unique key, but you also have to take into account that sqlite maintains a record identifier automatically that you can use to replace your id that is referenced by the fields rowid , oid or _rowid_ .

You could omit this field from the declaration and use as primary key name and phone :

create table t (
  name string,
  phone string,
  nick string,
  PRIMARY KEY(name, phone)
);

Implicitly the rowid will be unique and therefore, functionally, it would be part of the primary key.

You must remember that, if you use * in your queries, you must explicitly obtain that field using, for example:

SELECT rowid, * FROM t ...