0

Scenario is that I have 2 tables of the same structure, however I only want to allow php permissions to update table B, while table A can only be updated via DBMS. These 2 tables are merged into a single php array, so I would like to set primary key ranges to seperate them at this point to avoid conflict of primary key (a simple autoincrement integer for best indexing). As far as I know the simplest would be to constrain table A to have primary key auto increment values from 1000000 to 1999999 and then table B 2000000 upwards. Is this possible to constrain min-max autoincrement values (I know I can start them at a given integer so asking if there is a simple 'max' to put on table A). This simple configuration would ensure integrity.

Would an 'after_insert' type trigger work to remove the new row and throw an SQL error ?

Datadimension
  • 872
  • 1
  • 12
  • 31
  • There is no max like you want. But why not use a prefix on your is while querying? If there is no relation between both, you can even use a UNION query – Leroy May 27 '21 at 19:22
  • 1
    What have you tried to get this done ? – Luuk May 27 '21 at 19:24
  • @Leroy - thanks, using is does not guarantee integrity but your comment gave me an idea to use a trigger - not sure how I would do this to delete any row above 1999999 and throw an sql error (via the DBMS not php) – Datadimension May 27 '21 at 19:39
  • @Luuk - thanks, not tried anything for this constraint as thats what I am asking. If I have these tables, php can read and merge both of them, overriting same pk values. I want a constraint whether via SQL to throw an error or via PHP when inserting a new row into table A - basically if the autoincrement is too high the insert is rejected and an error – Datadimension May 27 '21 at 19:41
  • I would make the primary key a compound key on two columns — the auto-increment plus a single-byte column, which is always 0 for the first table, and always 1 for the second table. Then you don't care about restricting the range of the auto-inc in either table. They will always have distinct primary keys. – Bill Karwin May 27 '21 at 19:44
  • SO knows how to create a trigger to preventing a table update, see: https://stackoverflow.com/questions/24/throw-an-error-preventing-a-table-update-in-a-mysql-trigger – Luuk May 27 '21 at 19:50

1 Answers1

0

You could create one table with id as mediumint (max 8 388 607 or twice as much for unsigned):

create table tableA( id mediumint(5) not null auto_increment, `test` varchar(5), primary key (id)) ;

and second with int and auto_increment value set over mediumint max:

create table tableB( id int(5) not null auto_increment, `test` varchar(5), primary key (id)) auto_increment=8388608 ;

https://dev.mysql.com/doc/refman/8.0/en/integer-types.html

But i think that much more elegant would be to utilize auto_increment_increment mechanism.

auto-increment-increment = 2 //global for all tables in mysql.ini
SET @@auto_increment_increment=2; //run-time just for one session

Set in tableA first auto_increment=1 and in tableB auto_increment=2 and You will never collide. One table will have odd ids and second will have even ids. This way You do not have to worry about reaching id limit.

JFK
  • 56
  • 3