Re: the secrecy - understood and not an issue. Your current explanation might be able to provide a more focused direction. Pseudo data that's way off the mark usually results in suggestions that are not practical and questions that are not pertinent. You could do as you are proposing as in
and if the 2 serial fields are composite, then the table would handle the data as mentioned. Presumably, the carton sn field would have to be a unique index on its own as well. However, this isn't the normalized approach. The correct approach would be a linking table that represents the combination of cartons and sn's. Presumably there is a table of phone sn's but rather than mock that as well, I just put those sn's directly in the linking table. In actual practice, the phone table sn id's would be used so as to not repeat the sn's.
tblCN ID CNsn ItemSNa ItemSNb 1 00111 S123 S0123 2 00222 S345 S0234 3 00333 S456 S0345 4 00444 S567 S0456 5 00555 S678 S0567 6 00666 S789 S0678
This is the carton sn table:
tblCN ID CNsn 1 00111 2 00222 3 00333 4 00444 5 00555 6 00666
this is the linking table. CNsn_FK: fk in a fld name is oft used to indicate that the field is a field in some other table (usually that table's PK field).
In this case, a unique index on carton field isn't required, but the composite on CNsn_FK and ItemSN would mean that the same sn can't go into the same carton 2x. This doesn't ensure that S123 can't go into carton 1 and 2. I don't think anything proposed thus far addresses that possibility as it would require more robust checking. As for what event, such validation has to occur before updating. AfterUpdate is too late.
tblCNSN ID CNsn_FK ItemSN 1 1 S123 2 1 S0123 3 2 S345 4 2 S0234 5 3 S456 6 3 S0345 7 4 S567 8 4 S0456 9 5 S678 10 5 S0567
The main issue with idea #1 is that should a 3rd item end up going into the carton, you have to modify the table and likely every form, report and query that uses it. While this exact issue may never arise, the concept of side by side sn's fails the acid test and should be avoided in most cases.