Primary Key issues

J

James Corden

Member
I have installed Dadabik onto an existing SQL Server database. The Primary Keys on all the tables of this database are non-sequential. So for example, entries that have been added sequentially by date into one table have the following values in the Primary Key column.

201356
201358
201402
201422
201437
201451

I don't understand how these values are being generated or why they are non-sequential. I imagine this is a property of the existing SQL Server database or the program used to insert data into this database. The values are always increasing but apart from that there doesn't appear to be any pattern (i.e - increasing by the same amount each time). I think it might be a security issue.

I have set the same column to be the Primary Key column in the Data>Table and Views section of Dadabik.

However when I try to add new entries to a table using a simple form I get the error message that I have to enter a value for the Primary Key.

How do you use Dadabik on an existing database where there is already data in tables and the existing primary key is not sequential (i.e 1,2,3,4,5 etc.)? I can't set a new primary key column for each table because the primary key in a lot of the existing tables is used as a foreign key in other tables so this would mess up all my relationships.

In the new database, it would be acceptable to auto-increment the primary key from the most recent value in the primary key column based on the old data. So for example if the last entry in the primary key column (before entering data via Dadabik) was 1679 then it would be acceptable for the new primary key values to auto increment from this point (1680, 1681, 1682 etc.).

I have tried setting Extra to identity on the Data>Tables and Views section (as the help comment for Extra suggests that this is what you need to do if you want the Primary Key to auto-increment) but this resulted in the following error message which suggests Dadabik is trying to set up a new primary key on the table in question.

unexpected error drop_table_form.submit ajax error{"readyState":4,"responseText":"<p><b>[08] Error:</b> during query execution. alter table &quot;TECHNOL&quot; ADD PRIMARY KEY (&quot;PRIMARYKEY&quot;)<br/>The DBMS server said: SQLSTATE[42000]: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Table 'TECHNOL' already has a primary key defined on it.","status":200,"statusText":"OK"}

Dadabik is advertised as being suitable for use with existing databases so interfacing with the existing primary keys must be a common problem but I can't find any reference to this on the forum.

Thanks
 
eugenio

eugenio

Administrator
Staff member
I don't know why your values are non sequential, the most probable reasons is that some records have been deleted, but this is not a problem and has nothing to do with DaDaBIK, the database was already in such state, am I right?

I didn't understand if those fields are already "identity" or not, maybe you can double check it from sql server management studio or any tool you are using.

As a general rule, the PK value cannot be NULL, so:

- if it's identity, you should remove the field from the insert form, because the value is assigned automatically
- if it's not identity, you should keep the field in the insert form and assign a value manually

DaDaBIK also works with table without an identity PK but having an identity PK gives you some advantages because some features work well only with identity PKs.

Best,
 
J

James Corden

Member
eugenio said:
I don't know why your values are non sequential, the most probable reasons is that some records have been deleted, but this is not a problem and has nothing to do with DaDaBIK, the database was already in such state, am I right?

I didn't understand if those fields are already "identity" or not, maybe you can double check it from sql server management studio or any tool you are using.

As a general rule, the PK value cannot be NULL, so:

- if it's identity, you should remove the field from the insert form, because the value is assigned automatically
- if it's not identity, you should keep the field in the insert form and assign a value manually

DaDaBIK also works with table without an identity PK but having an identity PK gives you some advantages because some features work well only with identity PKs.

Best,
Click to expand...
Hi Eugenio
Thanks for your speedy reply.
In the existing database the PK is not identity.
Asking the user to manually assign a PK value when adding data is going to cause me lots of problems (for example how will the user know that the PK value they are adding is unique?). Is there any way around this. For example could Dadabik look up the most recent entry in the PK field (based on the date/time of entry) and add one to it and then insert this into the PK field?
Thanks
James
 
eugenio

eugenio

Administrator
Staff member
James Corden said:
Hi Eugenio
Thanks for your speedy reply.
In the existing database the PK is not identity.
Asking the user to manually assign a PK value when adding data is going to cause me lots of problems (for example how will the user know that the PK value they are adding is unique?). Is there any way around this. For example could Dadabik look up the most recent entry in the PK field (based on the date/time of entry) and add one to it and then insert this into the PK field?
Thanks
James
Click to expand...

There are identity fields that can do that for you, why you don't convert that field in identity?

Best,
 
J

James Corden

Member
Do you mean I should try to change the identity properties of the PK column in SQL Server or in Dadabik?

I tried setting Extra to Identity in Dadabik but received the error message described above
I have just tried setting Identity Specification>Is Identity to Yes in SQL Server Management Studio and received the following error

Thanks


1645784565242.png
 
J

James Corden

Member
Values were inserted with commercial software that integrates with the database. This is what I am trying to replace. No idea why it was not an identity originally.
Will have a look at the thread. It might be easier to convert database to MySQL.
On holiday all next week but thanks for help so far.
 
J

James Corden

Member
Can you think of any reason why I couldn't use a Calculated Field Function to look at the value of the Primary Key from the most recent entry (by date and time), increment this by a set amount and then insert this automatically into the existing PK column.
Seems like this could be a simpler approach.
 
eugenio

eugenio

Administrator
Staff member
You can do that, but it seems to me a complicated approach for something that the DBMS can do natively (and error free).
For sure this wouldn't be a best practice.

One possible issue (but maybe there are others) is the fact that if two user insert a record at the same time, one will get an error because they would get the same ID.
 
J

James Corden

Member
Hi Eugenio
I have had a look at the suggestions for changing the primary key column to an identity column in stack overflow. None of them seem ideal, they would require a lot of work to do this on all my tables and most are beyond my SQL programming abilities.

Hence it looks like the easier option is going to be to migrate the database to a mySQL database. Before I have a go at doing this I need to be reasonably confident this approach is going to let Dadabik work with the existing primary key columns. A few questions:

1) I already have my SQL Server database with Dadabik installed on top. When migrating to mySQL, should I migrate the original SQL Server database first and then install Dadabik on top of this again, or can I migrate the new SQL Server database that already has Dadabik installed? Any pros / cons to each approach?
2) Once I have migrated to mySQL, and converted the existing primary key columns to identity columns will Dadabik be able to auto-increment values into the existing primary key column? So for example, the current values in a PK column are ascending with variable increments as below:

201356
201358
201402
201422
201437
201451

What I would like to happen is for all the new values to auto-increment from the highest value in the PK column as below. This will ensure that each new PK value is unique and doesn't already exist in the column.

201356
201358
201402
201422
201437
201451
201452
201453
201454
201455

How / can I set Dadabik to do this?

Thanks
 
eugenio

eugenio

Administrator
Staff member
Hello,

1) you can take the option you prefer. If can migrate everything (including the dadabik_tables) if you have already configured your application, to avoid loosing your configuration or you can migrate all the tables except from the dadabik_ ones if it's ok for you to start from scratch

2) The way auto-increment values are managed is totally independent from DaDaBIK, it's the DBMS (mysql in this case) that assign the values.
Yes, it will start from the highest number + 1 but you can try youself, just create a mysql table (without dadabik) with an AI primary key, add some records manully assigning some random value to the PK and then add a record without specifying the PK, mysql will add the value for you.

Best,
 
J

James Corden

Member
Hi
I have now migrated my original database to mySQL. I chose to migrate the database without Dadabik installed.
However I am now unable to install Dadabik on the mySQL database. I am getting the error message below.
I have quadruple checked everything in config_custom.php.
PHP is running properly
mySQL is running properly as I can connect to the database with phpmyadmin and mysqlworkbench
Any suggestions?
Thanks
James

Current PHP version: 7.4.16 OK

mbsgtring extension: Installed

ioncube extension: Installed

ioncube extension version: 11.0 OK

Check DB connection: [06] Error: during database connection. Please check $host, $user, $pass and $db_name in your config.php
The DBMS server said: could not find driver There is a connection problem, check $host, $user, $pass, $db_schema, $db_name in config.php



I
 
J

James Corden

Member
Just to close this out for the benefit of others.
Converting to MySQL did enable me to set primary key to autoincrement from most recent primary key value in existing table :)
 
You must log in or register to reply here.
Top