What is interesting about this situation can be solved using the MyISAM storage.
I answedred a question like this back in April 2012 : How can you have two auto-incremental columns in one table? You need to create one table whose sole purpose is the create sequences of work order for each site
CREATE TABLE site_workorder_seq
(
SiteID int not null,
SiteWorkorderNum int not null auto_increment,
PRIMARY KEY (SiteID,SiteWorkorderNum)
) ENGINE=MyISAM;
Here is a sample loading into this table:
mysql> DROP DATABASE david;
Query OK, 1 row affected (0.01 sec)
mysql> CREATE DATABASE david;
Query OK, 1 row affected (0.00 sec)
mysql> USE david
Database changed
mysql> CREATE TABLE site_workorder_seq
-> (
-> SiteID int not null,
-> SiteWorkorderNum int not null auto_increment,
-> PRIMARY KEY (SiteID,SiteWorkorderNum)
-> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO site_workorder_seq (SiteID) VALUES
-> (1),(1),(2),(3),(3),(3),(3),(4),(4),(4),
-> (5),(5),(4),(2),(2),(2);
Query OK, 16 rows affected (0.00 sec)
Records: 16 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM site_workorder_seq;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 2 | 4 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
| 3 | 4 |
| 4 | 1 |
| 4 | 2 |
| 4 | 3 |
| 4 | 4 |
| 5 | 1 |
| 5 | 2 |
+--------+------------------+
16 rows in set (0.00 sec)
mysql>
Let's look at the the last WorkorderNum from each site
mysql> SELECT SiteID,MAX(SiteWorkorderNum) SiteWorkorderNum
-> FROM site_workorder_seq GROUP BY SiteID;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
| 1 | 2 |
| 2 | 4 |
| 3 | 4 |
| 4 | 4 |
| 5 | 2 |
+--------+------------------+
5 rows in set (0.05 sec)
mysql>
Now, suppose you want to get the next SiteWorkorderNum for SiteID 3. You could do this:
INSERT INTO site_workorder_seq (SiteID) VALUES (3);
SELECT MAX(SiteWorkorderNum) INTO @nextworkordernum
FROM site_workorder_seq WHERE SiteID=3;
SELECT @nextworkordernum;
Let's run this and see what happens
mysql> INSERT INTO site_workorder_seq (SiteID) VALUES (3);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT MAX(SiteWorkorderNum) INTO @nextworkordernum
-> FROM site_workorder_seq WHERE SiteID=3;
Query OK, 1 row affected (0.00 sec)
mysql> SELECT @nextworkordernum;
+-------------------+
| @nextworkordernum |
+-------------------+
| 5 |
+-------------------+
1 row in set (0.03 sec)
mysql> SELECT * FROM site_workorder_seq;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 2 |
| 2 | 3 |
| 2 | 4 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
| 3 | 4 |
| 3 | 5 |
| 4 | 1 |
| 4 | 2 |
| 4 | 3 |
| 4 | 4 |
| 5 | 1 |
| 5 | 2 |
+--------+------------------+
17 rows in set (0.00 sec)
mysql> SELECT SiteID,MAX(SiteWorkorderNum) SiteWorkorderNum
-> FROM site_workorder_seq GROUP BY SiteID;
+--------+------------------+
| SiteID | SiteWorkorderNum |
+--------+------------------+
| 1 | 2 |
| 2 | 4 |
| 3 | 5 |
| 4 | 4 |
| 5 | 2 |
+--------+------------------+
5 rows in set (0.00 sec)
mysql>
As long as you use this one MyISAM apart from all InnoDB tables, you can generate workordernums per site to your hearts content.
I do agree with Aaron, that not creating a PK (or unique constraint) is a very strange requirement
But you could do something like this:
insert into b (id_b, id_a)
select 1, 2
where exists (select 1 from a where id_a = 2);
or alternatively:
insert into b (id_b, id_a)
select 1, id_a
from a where id_a = 2
If you want to insert multiple rows, you can use a values clause:
insert into b (id_b, id_a)
select *
from (
values
(1,2), (2,3), (3,4)
) as t(id_b, id_a)
where exists (select 1
from a
where a.id_a = t.id_a);
This will only make sure that this specific insert will insert correct data. It will not prevent others that don't follow this pattern to insert invalid data. And it is not safe in a multi-user environment where different transactions run the inserts concurrently.
Best Answer
You can use the following statement to synchronize the sequence with the current max value of the ID column:
Online example: https://rextester.com/GMK2630
You can run this manually after you have inserted rows with explicitly supplied values.
Note that this can still result in a wrong sequence value if this is done concurrently from multiple transactions because a transaction does not see the values inserted (but not committed) by other transactions.
But I would strong recommend to simply let the default do its job, adjusting the sequence like that is more a hack, rather than good coding practice.