There is a table:

CREATE TABLE `test` ( `id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `id_kvitanc` VARCHAR(100) NOT NULL `id_magazin` VARCHAR(100) NOT NULL ) ENGINE = MYISAM

So the essence is as follows: id is a unique entry, id_magazin , id_magazin is the store ID from another table, numbers (1,2,3) id_kvitanc is the number of the check that created the store. Is it possible to make it so that if id_kvitanc=1 (for example, the first store), then it would be impossible to add an entry with id_kvitanc=500 , if id_kvitanc=500 already exists? (but for id_magazin=2 , maybe id_kvitanc=500 ) more precisely, so that id_kvitanc filled in uniquely auto-incrementally?

  • And why do you have id_kvitanc varchar? How do you imagine auto-increment filling the text field? And how does it differ from the id column? - Mike
  • For uniqueness, you need to add constraint UNIQUE(id_magazin, id_kvitanc) . Autoincrement on id_kvitanc , as on the id field you can’t do it, you will have to emulate it yourself in the code .. - Arnial
  • id_kvitanc is a foreign key, in the second receipt table it will be the primary key, and fill it with an auto increment. - Jean-Claude
  • Well, if id_kvitanc to do type int , something from this then it turns out? differs in that id is a unique entry in the table, and id_kvitanc can be the same value, but with different id_magazin, for example: id = 1, id_kvitanc = 500, id_magazin = 3 and id = 2, id_kvitanc = 500, id_magazin = 4 - Samoylenko Alexandr
  • read Fort - sql, Lynn Bailey - sql, there it is very well disclosed. - Jean-Claude

1 answer 1

For uniqueness, you need to add the constraint (constraint) UNIQUE(id_magazin, id_kvitanc) .

Unique two fields will give exactly the restriction you want.

  CREATE TABLE `test` ( `id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, `id_kvitanc` VARCHAR(100) NOT NULL, `id_magazin` VARCHAR(100) NOT NULL, UNIQUE (id_magazin, id_kvitanc) ) ENGINE = MYISAM -- это сработает без ошибок insert into test (id_magazin, id_kvitanc) VALUES (1, 1), (1, 2), (2, 1); --сдесь будет ошибка, в магазине 1 уже есть квитанция 1. insert into test (id_magazin, id_kvitanc) VALUES (1, 1);

Autoincrement on id_kvitanc , as on the id field, you can’t do it, you’ll have to emulate it yourself in the code.

For example, you can create a table in which to store the maximum receipt number.

 create table last_kvitanc( id_magazin VARCHAR(100) NOT NULL, max_id_kvitanc INT NOT NULL DEFAULT 0, UNIQUE (id_magazin) );

In the code that adds receipts:

  1. Get the id_kvitance value for the given id_magazine.
  2. Increase the max_id_kvitance value by 1 (here you need to make sure that the value has not changed since it was received).
  3. Save the receipt in the test table with the received id_kvitance.
  • Arnial, you save me again :) What you need! - Samoylenko Alexandr