Updating to v17 - "non-unique primary key"

PS: as far as I know…

Right … so I am back to my original problem, which is that I need to:
a. Remove the PK
b. Update the records with unique values
c. Reinstate the primary key

All via code during the updating process.

But I can’t get the SQL code that removes/adds the PK to work.

Any suggestions anybody? @Thomas_Maul?

Pat

4D “repairs” wrong PK if auto assign is set when a database is migrated. It does not if auto assign is disabled.

I never used SQL to add/remove/modify PK, I use the user interface to design and maintain my structure. I’m not sure all needed options are exposed via SQL (such as enable journal). Possible, but I don’t know. If yes, same rules/order/procedure needs to be followed as if you would do it manually. So if the table is journaled, this needs to be disabled before a PK can be removed. Best to disable whole journal upfront. After filling the values, first index, then enable PK, then enable journal for this table, then enable journaling for database.
Use the interface for your first try, it will check and give alerts for the right order. Then this works, you can do the same by code to automate. If manual work fails for wrong order, automatic work will fail as well (just without clear error message)

Thanks for the clarification @Thomas_Maul.
I need to be able to manage the whole process via code. Otherwise, I would have to supply two versions of the app to every person who wants to upgrade. They would first have to upgrade using version “a” - with journalling disabled. This version would update the PKs as necessary. Then they would have to install and run version B - with journalling re-enabled. It’s a bit too much to ask :unamused:

Is there a way to disable journalling via code? I couldn’t find one.

Thanks!
Pat

It seems like a combination of factors has made the situation unnecessarily complicated. Normally, one only has to add an auto-generating UUID primary key field, and then, the first time a data file created prior to that field being added is opened with that structure file, all existing records are assigned a new unique value to make journaling work.

But to answer your question, you can disable or enable journaling with the command SELECT LOG FILE, you can disable or enable journaling for each table via SQL ALTER TABLE.

Hi Pat. For what it’s worth, I did some testing in v13, using SQL constraints (type ‘R’) and normal 4D relations (type ‘4DR’) and I only get the SQL error you are getting when I’ve got the type ‘R’ constraint. Having only 4DR constraints doesn’t prevent the primary key from being dropped.
image
The foreign key constraint shows up in _USER_CONSTRAINTS, and if I drop it I can then drop the primary key.


The foreign key also shows up in the structure explorer. I can delete it straight from the structure explorer as well.
image
So it’s curious that you aren’t getting anything when you search for the foreign key.

Another way to find a foreign key is to export the structure definition to XML and search for foreign_key="true".