Apply OS: Windows, macOS, Linux

Apply Navicat Product: Navicat for MySQL, Navicat for PostgreSQL, Navicat for Oracle, Navicat for SQL Server, Navicat for SQLite, Navicat for MariaDB, Navicat Premium

Apply Navicat Version No.: All

 

Navicat warns you of this because when updating a table that does not have a primary key you may get unexpected results. Lets look at the following sample.

Take the following table as an example :

Name	Surname	Age
Pete	McKensey	24
John	Slaid	34
Pete	McKensey	44
Mary	Joplen	26

This table does not have a primary key. If you update Pete's age to 44 the following statement would be issued to the server :

UPDATE "table" SET age=44 WHERE name='Pete' and surname='McKensey' and age=24;

This would execute correctly and change the first record's age value. Now there will be two records in the table with a Pete McKensey age 44 record. If you now try to update the third record's age to 55 the following statement will be sent to the server:

UPDATE "table" SET age=55 WHERE name='Pete' and surname='McKensey' and age=44;

This statement will then change both the first and third record's age value to 55.

You should notice that the data in this table has now become inconsistent in that the first and third records have exactly the same values and there is no way to distinguish the two from each other. This happens because the table is not in first normal form (any book on relational databases will explain this) which means that there are not a field or group of fields that one can use to uniquely identify a record in the table.

This is the reason why Navicat, in previous versions, required a primary key for updating tables. If this table had an ID field that is also the primary key the statements would look as follows :

UPDATE "table" SET age=44 WHERE ID=1;
UPDATE "table" SET age=55 WHERE ID=3;

These statements would cause no inconsistency in the data.

We have received many queries about this and decided that any table must be updateable and that only a warning will be displayed if a table does not have a primary key.