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.
2 Answers
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.
-
2I like to say that without a primary key you don't have data, you have junk. Sep 24, 2020 at 13:42
-
2In MySQL, if you don't create one, a "hidden" one is created for you!– VéraceMar 21, 2021 at 16:31
-
SQLite always creates a hidden primary key
rowid, unless explicitly disabled :) Aug 22, 2022 at 9:26
Yes, it is possible that a table doesn't have primary key in SQLite as shown below:
CREATE TABLE person (
name TEXT
);
rowid. If you expicitly disablerowidand don't specify aPRIMARY KEYcolumn,CREATE TABLE t1(name TEXT) WITHOUT ROWID;, it errors out:Parse error: PRIMARY KEY missing on table t1. SQLite version 3.39.2 2022-07-21 15:24:47.WITHOUT ROWID, though you might specifyPRIMARY KEYon a column, it's really just aUNIQUEcolumn constraint and the true primary key is stillrowid. SincePRIMARY KEYcolumns are just unique, and not really primary key, they can also be null. Hence it's preferred to specifyNOT NULLthe constraint.