How to disable Null PK?

[john1598360627]

I'm getting warning's about database tables that are allowing null's in their PK.

When I try to disable allowing nulls for the PK, it just resets back to allowing them when I save.

How do I disable allowing nulls for the PK? Do I need to use an external database editor to do this?

For some context, these are quite old database tables. From Visual Fox Pro. Purely setup to import old data, and not for active use.

nullpk-1.png (166.58 KiB) Viewed 13768 times

nullpk-2.png (131.62 KiB) Viewed 13768 times

[mboegem]

Hi John

I don't have any experience with Visual Fox Pro Databases, but for sure you need an external editor to change database definitions.
From within Servoy you are only able to create/delete tables and columns. Changing column definitions is not possible.

There are definitely Visual Fox Pro guru's around on this forum to guide you on how to do it.

[hilgers]

Hi John,

I'm (still) a FoxPro Developer and my recommendation is Database.NET https://fishcodelib.com/database.htm
This one differs from most other DB-Tools because of using the original VFP OleDB driver which can prevent many problems *IF* the Fox Database has some FoxPro code in stored procedures or record validations
It's astonishing what this tiny portable exe is able to do. The free version does most of the things you might need.

[john1598360627]

Hi John,

I'm (still) a FoxPro Developer and my recommendation is Database.NET https://fishcodelib.com/database.htm
This one differs from most other DB-Tools because of using the original VFP OleDB driver which can prevent many problems *IF* the Fox Database has some FoxPro code in stored procedures or record validations
It's astonishing what this tiny portable exe is able to do. The free version does most of the things you might need.

Great! Thank you, I'll check it out!

[omar]

Hi John, did you fix it? Best is to add the PK in VFP. You may need to recreate the table and import the data again. Even better is to upgrade your databse to PostgreSQL if possible. I can help you with that. I have done it for several VFP databases.

[john1598360627]

Hi John, did you fix it? Best is to add the PK in VFP. You may need to recreate the table and import the data again. Even better is to upgrade your databse to PostgreSQL if possible. I can help you with that. I have done it for several VFP databases.

Hasn't been fixed yet.

The VFP already has pk's, and Servoy can identify the pk too. The issue is that it's 'allowing nulls' for some reason.

[john1598360627]

When looking into the VFP database with Database .NET, it seems like the PK shouldn't allow nulls. As it has 'NOT NULL'.

Code: Select all

ALTER TABLE `acagecardweb` ADD
   `pk` Integer NOT NULL AUTOINC NEXTVALUE 1 STEP 1


Again though, in Servoy it shows 'Allow Null' to be checked ON... So I have no idea why there is a discrepancy.


Could the SQL in VFP be setup wrong, or that VFP is just too old for Servoy to detect this? It's just strange.

[hilgers]

you are not alone - Servoy believes all my VFP fields are nullable as well...

[john1598360627]

you are not alone - Servoy believes all my VFP fields are nullable as well...

Ah, so I'm not alone. I'll try and get a ticket posted to Servoy about this issue.

[kwpsd]

Created Support Ticket: SVY-18996

[rgansevles]

It seems that the driver just does ignores nullability.
When I create a table using another JDBC tool (squirrelsql) outside Servoy I see exactly the same.
I can create a table with non-nullable fields

Code: Select all

create table tst (tst_id integer not null, tst_f integer null, primary key (tst_id))

The metadata still says nullable for all columns.
When I insert nulls, it stores 0.

Code: Select all

insert into tst values (null, null)

Code: Select all

select * from tst -- returns  ( 0, 0 )

So it seems this is an issue with the driver / database and cannot be fixed in Servoy.

[john1598360627]

Hmmm so if there's no way for Servoy to fix the Warning... is there any way I can filter them out? Any way of unselecting or ignoring the the specific database?


Not really sure how to do that.

warning-filter.png (93.57 KiB) Viewed 9390 times

[john1598360627]

OKAY I figured it out.

I had to make a New Configuration + New Working Set. In which it contains all project modules EXCEPT resources, thereby ignoring the database warnings.

Then made the resources it's own Configuration + Working Set too, so I can look at it on it's own.