Can a database table not have a primary key

primary-keysqlite

Can a database table not have a primary key? SQLite lets you define a table with no primary key therefore there can be no insertion anomaly.

Best Answer

Can you create a database table without a primary key? Well, you just said you can in SQLite. And, I believe that holds true for almost every (if not every) major DBMS platform.

Should you create a database table without a primary key? No.

Every table should have some column (or set of columns) that uniquely identifies one and only one row. It makes it much easier to maintain the data.

It's true, without a primary key (or some unique key), you don't have an insertion anomaly if you go to insert the same data multiple times.

What you do have is a user table with 300 separate entries for a user named "bob", many of which have different values in the other columns. And, if you've tried to connect rows in some other table to user without a primary key/foreign key relationship - every row in message marked as sent_by "bob" ties back to every one of those 300 user entries. Oh, and there're 39 unique passwords for "bob" (157 of the rows all show the password "password", of course).

Without primary keys, things can get really messy, really fast.

Related Solutions

Modify legacy table schemas that have no primary key

I would add a BIGINT Identity column as the non-clustered primary key for any table that does not have a primary key. This will cause minimal disruption and use minimal storage space.

It will often improve performance especially for updates and deletes.

How to use CREATE TABLE … AS with Primary Key in SQLite

I suspect you're missing the difference between CREATE TABLE and CREATE TABLE AS (otherwise known as CTAS).

CREATE TABLE AS allows you to create a table from the resultset of a query.

For example:

CREATE TABLE PHILSUCKS AS ( SELECT PHIL, SUCKS FROM EGGS );

You could, instead of using CTAS, use a "normal" CREATE TABLE statement, then INSERT the rows manually. This allows you to specify the PRIMARY KEY and any constraints. eg:

CREATE TABLE PHILSUCKS
(
  PHIL   INTEGER PRIMARY KEY,
  SUCKS  INTEGER NOT NULL
);

INSERT INTO PHILSUCKS ( SELECT PHIL, SUCKS FROM EGGS );

Obviously, you can also create indexes etc too:

CREATE INDEX EGGSUCKING ON PHILSUCKS (SUCKS);

Hope that helps!

Best Answer

Related Solutions

Modify legacy table schemas that have no primary key

How to use CREATE TABLE … AS with Primary Key in SQLite

Related Question