Updating to v17 - "non-unique primary key"

I’m updating a database from v13.5 to v17.
There is one table which originally had no primary key, so we added one at some point. Now we have a situation where some records have a primary key (recordNumber") and some don’t (they are 0).
So in our update code we find all the records in that table that have recordNumber=0 and assign record numbers to them. But whenever the method tries to save a record with a newly-assigned recordNumber it doesn’t save and we get the “non-unique primary key was found in table ListItems” error.

I have checked and the recordnumber being assigned definitely IS DEFINITELY unique.
Of course there are still non-unique recordNumbers in the table - the ones that are still = 0 - but that shouldn’t cause this problem should it? As long as the record being saved is unique it should be OK, right?

What am I missing?
Do I need to get more sleep?

Pat

Unfortunately, that is incorrect. Primary key must be non-NULL, so you can not save a value, if some of the existing records are NULLs. It is not good enough that the non-NULL values are unique.

This may happen, for instance, if you add a new primary key field to a table that already has records, or opened a data file that was last used when the primary key did not exist.

The primary key wizard automatically populates those NULL fields when you use it to add a primary key, but that only happens once and only for the data file that was used at the time.

You need to remove that table’s primary key, fix the NULL issue, then restore that field as its primary key.

Thanks! That makes sense … But I’m struggling a bit with the syntax for this. I tried:
Begin SQL
ALTER TABLE ListItems drop primary key ;
End SQL
This gives the error when I run it:

Failed to execute ALTER TABLE command.

Error code: 1808 (SQLS)
Failed to execute ALTER TABLE command.
component: ‘SQLS’
task -10, name: ‘P_3’
Cannot alter primary key definition of table ListItems
The list of foreign key constraints is not empty for table: ListItems of database: CatBasev8b271

I also tried:
Begin SQL
alter table ListItems ;
drop primary key constraint (ListItems. RecordNumber) ;
End SQL

What’s the correct syntax for this?

Thanks!

Pat

I am not very well versed in SQL, so I am not entirely sure if this is the right answer.

The error code refers to the existence of a FOREIGN KEY, does that ring any bells? Perhaps you need to deal with the primary key of the foreign table first.

Alternatively, if it is not mandatory to journal this table, you could DISABLE LOG and drop this new field all together.

Before dropping a primary key constraint you will need to first drop the foreign key constraints on the other tables.

For example, if I’ve got a primary key on Table1 and a foreign key called FK_Table2_Table1 on Table 2, I’d have to do.

ALTER TABLE Table2 DROP CONSTRAINT FK_Table2_Table1;
ALTER TABLE Table1 DROP PRIMARY KEY;

You should be able to find foreign keys with

SELECT *
FROM _USER_CONSTRAINTS
WHERE RELATED_TABLE_NAME = 'ListItems'
    AND CONSTRAINT_TYPE = 'R'

Thanks guys.
There is no foreign key defined for the table. There are only 6 fields and I’ve checked them all. There is no relation between this table and any other table.

@Adam_Storck I tried to run this code:

ARRAY TEXT ($atConstraints;0)
Begin SQL
SELECT *
FROM _USER_CONSTRAINTS
WHERE RELATED_TABLE_NAME=‘ListItems’
AND CONSTRAINT_TYPE = ‘R’ into : $atConstraints ;
End SQL

  • Doesn’t work ): The array is empty. Any idea what’s wrong with my code?

Pat

Upfront: no, I don’t know why it fails for you.

In general:
when upgrading a database without PK to one with PK, there are several ways.

  • if you are 100% sure (100%, not 99.9999%) that an existing field is unique (also means all records have one), you can use that field as PK.

else

  • if you want it easy for developer and admin: add a new field, select either longint or UUID, BUT USE auto increment or Auto UUID, and set that as PK. 4D will automatically fill missing data and even reassign duplicated values.
    the disadvantage is that you have two kind of unique fields, one with untrustfull data and another one, unused in your code, used only by 4D’s logfile, ORDA, sync, etc.

  • if you want to use an existing one and are ready to do work, two ways:
    – ship a 4D v13 version which opens the data, check the content, fix the data and then do the update to the current version. Not nice, but a possible approach
    – export the data with either a v13 based structure or a v17/v18 based one, but without any PK attribute set, so without journal. This structure is only to export. Export as text only or as SQL dump (if you do not use object fields, which should be the case for v13 or older
    Then use your “new” structure to import the data. During the import process check and repair all PK fields.
    An alternative of that is to use a v17/18 based structure without journal/PK only to repair, but without import/export.

1 Like

Maybe it is a constraint on ListItems rather than another table.

Try this.

ARRAY TEXT($atConstraints;0)
Begin SQL
    SELECT CONSTRAINT_NAME
    FROM _USER_CONSTRAINTS
    WHERE TABLE_NAME='ListItems' AND CONSTRAINT_TYPE='R'
    INTO :$atConstraints
End SQL

Hi Pat,
if things have not changed since 2011, the “R” CONSTRAINT_TYPE is a relation created by SQL, by opposite to one created in structure editor where the resulting CONSTRAINT_TYPE is “4DR” (in structure editor, the “R” showed squares at each end while the “4DR” is the legacy arrow from many to 1). Since these tests, I gave up trying to use these “R” relations, thinking the lack of documentation means “use it at your own risk”.

Thus, in my bases, the only constraints I can see are “4DR” (legacy 4D relation)or “P” (primary), and the 4DR have DELETE_RULE none, CASCADE or RESTRICT as below:

From your code, I’ll try to query all constraints on that table, not a particular one, as the error tells you there is one.

I figured it would be ‘R’ type due to the error stating that there was a foreign key, but i guess that SQL might refer to a 4D relation as a foreign key too. So changing ‘R’ to ‘4DR’ is worth a try.

WHERE RELATED_TABLE_NAME='ListItems' AND CONSTRAINT_TYPE='4DR'

FYI here is my test code that works, and provides useful info:

C_TEXT(errText;errODBC)
C_LONGINT(errCode;errSQLServer)
ARRAY TEXT($atConstraintName;0)
ARRAY TEXT($atTableName;0)
ARRAY TEXT($atContrataintYpe;0)
Begin SQL
SELECT table_name, constraint_name, constraint_type
FROM _USER_CONSTRAINTS
WHERE CONSTRAINT_TYPE=‘4DR’
INTO :$atTableName , :$atConstraintName , :$atContrataintYpe
End SQL
SQL GET LAST ERROR(errCode;errText;errODBC;errSQLServer)

But does not help with the current problem … :unamused:

Hi Pat,
I maybe wrong but I’d get all constraints, by filtering ‘4DR’ you could miss something:

Begin SQL
  SELECT table_name, constraint_name, constraint_type
  FROM _USER_CONSTRAINTS
  /*WHERE CONSTRAINT_TYPE=‘4DR’*/
  INTO :$atTableName , :$atConstraintName , :$atContrataintYpe;
End SQL

BTW, you don’t need to catch SQL errors here, I think: debugger will tell…

Note also that constraints are defined by 2 tables, the constraint itself is defined in _USER_CONSTRAINTS, but the elements (fields) of it are in _USER_CONS_COLUMNS - I suppose this is due to primary keys (PK) as SQL allows to use more than 1 field to define a PK.

Hey Pat,
I have a client with an old database another developer converted to v17. His approach was basically what Thomas suggested: add a new field named UUID to every table and set it to auto generate. Period.

It works fine.

Yes - you do have the issue that the real PK is not the one the code thinks of and uses as the PK. It probably won’t matter. The old code, and its relations, work exactly as they did before. If you begin incorporating ORDA you can use the UUID to great effect. I thought it would be more of a problem when I began but it’s really a non-issue. And you avoid all this SQL stuff.

Pro-Tip: While you are adding the UUID field to all the tables you want to journal and use with ORDA take the opportunity to name the relations to and from those tables. Yes, it’s a PITA but it’s really crucial for happy ORDA code and once it’s done you never have to look back.

Hi Kirk,

each time I read “swich to UUID’s, it’s so easy” I think “not so simple”. I mean, if an old existing DB was made of primary keys only, yes. But if there’s already a bunch of foreign keys and relations, it becomes quite a big work… Do I miss something?

yes and no:

  • given how relations are drawn behind tables in structure editor, it’s that comfortable: :scream: :face_with_symbols_over_mouth: :nauseated_face:
  • using the component here, it took me half a day to name/verify all relations in a structure having 222 tables and 278 relations. I didn’t regret my DIY and use it each time I must rename for orda.

Not sure but the point I am making is that you don’t need to change the existing relations of a database to add a unique PK which
a) makes the table available for 4D to journal
b) makes the table available to ORDA

So, in this context the UUID is really perfect and leaves you the option of dealing with the relations or not. You know this wouldn’t even be a discussion were it not for the fact the existing ‘PK’ isn’t unique. Which is really a data problem. Whatever business logic the structure has is working well enough. The problem to be solved is identifying a PK without breaking everything. This approach will do that.

Arnaud,

I take your point. I use UUID for all my PKs but for some tables it is useful to have a human-readable identifier or sequential ID. In those cases I add a longint field with autoincrement. Often very handy and there is no reason you can’t use it as a field for relations. It’s not the PK but it doesn’t matter.

Right.
To update from <=13 to >=14 this could be the easiest and quickest step.
For tables with a smaller set of records (not your biggest ones, not millions), you could even decide to keep that forever, saying RAM is cheaper than your wages.

There are just two reasons why you might consider to optimise that:

  • having two fields, both supposed to be unique and identifiers, are a waste of memory, hard disk and CPU speed (two index needs to be updated).
  • keep your structure clean -> keep it readable

A structure refactoring could be easier when you have time, when the structure is already converted, not all in one step.

but for some tables it is useful to have a human-readable identifier

yes and no.
I learned that everything what the end user can see is something he/she wants to change (sooner or later).
A PK is a “must not change” value. This is not a recommendation, it’s law. If you modify an existing PK, you break the journal. Read break as destroy.
So I learned not to use invoice numbers, social insurance numbers or similar “unique” and “never changing” ID’s as PK.
And I learned that an UUID, as it is “ugly” is ignored from the end user (if I missed to hide it upfront).
I understand that it is not so readable for developer either. With classic language I learned to copy&paste to test, with ORDA I don’t need to care (learn to assign entities, not PKs).

If I create a new UUID field and try to make it the PK, it won’t allow it because the field contains duplicate values …

Hi Pat,
you have to populate that new field before making it the PK, 4D never fills a new field “by itself”.