CODEWITHSUNDEEP
sqlite
mjb
mjb

Reputation: 7969

Sqlite, is Primary Key important if I don't need auto-increment?

I only use primary key integer ID for it's "auto-increment function".

What if I don't need an "auto-increment"? Do I still need primary key if I don't care the uniqueness of record?

Example: Lets compare this table:

create table if not exists `table1`
(
    name text primary key,
    tel text,
    address text
);

with this:

create table if not exists `table2`
(
   name text,
   tel text,
   address text
);

table1 applies primary key and table2 don't. Is there any bad thing happen to table2?

I don't need the record to be unique.

Upvotes: 4

Views: 1468

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94939

SQLite is a relational database system. So it's all about relations. You build relations between tables on keys.

You can have tables without a primary key; it is not necessary for a table to have a primary key. But you will almost always want a primary key to show what makes a record unique in that table and to build relations.

In your example, what would it mean to have two identical records? They would mean the same person, no? Then how would you count how many persons named Anna are in the database? If you count five, how many of them are unique, how many are mere duplicates? Such queries can be done properly, but get overly complicated because of the lacking primary key. And how would you build relations, say the cars a person drives? You would have a car table and then how to link it to the persons table in your example?

There are cases when you want a table without a primary key. These are usually log tables and the like. They are rare. Whenever you are creating a table without a primary key, ask yourself why this is the case. Maybe you are about to build something messy ;-)

Upvotes: 3

CL.
CL.

Reputation: 180080

You get auto-incrementing primary keys only when a column is declared as INTEGER PRIMARY KEY; other data types result in plain primary keys.

You are not required to declare a PRIMARY KEY. But even if you do not do this, there will be some column(s) used to identify and look up records. The PRIMARY KEY declaration helps to document this, enforces uniqueness, and optimizes lookups through the implicit index.

Upvotes: 2

Related Questions