Page 1 of 1

How to disable Null PK?

PostPosted: Thu Dec 07, 2023 12:41 am
by 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
nullpk-1.png (166.58 KiB) Viewed 7754 times


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

Re: How to disable Null PK?

PostPosted: Thu Dec 07, 2023 9:10 am
by 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.

Re: How to disable Null PK?

PostPosted: Thu Dec 07, 2023 11:16 am
by 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.

Re: How to disable Null PK?

PostPosted: Fri Dec 08, 2023 1:33 am
by john1598360627
hilgers wrote: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!

Re: How to disable Null PK?

PostPosted: Mon Dec 11, 2023 12:37 pm
by 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.

Re: How to disable Null PK?

PostPosted: Sat Feb 24, 2024 2:55 am
by john1598360627
omar wrote: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.

Re: How to disable Null PK?

PostPosted: Sat Feb 24, 2024 2:58 am
by 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.

Re: How to disable Null PK?

PostPosted: Sun Feb 25, 2024 6:22 pm
by hilgers
you are not alone - Servoy believes all my VFP fields are nullable as well...

Re: How to disable Null PK?

PostPosted: Thu Feb 29, 2024 2:41 am
by john1598360627
hilgers wrote: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.

Re: How to disable Null PK?

PostPosted: Fri Mar 01, 2024 12:42 am
by kwpsd
Created Support Ticket: SVY-18996

Re: How to disable Null PK?

PostPosted: Fri Mar 08, 2024 10:42 am
by 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.

Re: How to disable Null PK?

PostPosted: Tue Apr 09, 2024 1:21 am
by 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
warning-filter.png (93.57 KiB) Viewed 3376 times

Re: How to disable Null PK?

PostPosted: Wed Apr 10, 2024 12:56 am
by 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.