Apply OS: Windows, Mac, Linux

Apply Navicat Product: All

Apply Navicat Version No.: Windows version 10.x or below, Mac version 8.x or above, Linux version 10.x or below


First of all, if the warning annoys you, it can be turned off!

Windows and Linux: Select Tools > Options and uncheck the Show primary key warning in Appearance > Data & Grid.

Mac: Select Navicat xxx > Preferences and uncheck the Show primary key warning in Grids tab.

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.

Have more questions?
Submit Ticket