5

I am creating a relation between tables which is necessary to auto increment two columns id_quotation and seq_quotation. The column id_quotation, I am referencing into another table (tb_core_process_id_quotation) where I already increment it.

This table below (tb_core_process_customer_data) will be used by other tables to catch commons and main customers data. To make it is necessary this three validation keys: cpf_cnpj, id_quotation and seq_quotation that are common to entire tables in this database.

tb_core_process_customer_data query:

CREATE TABLE tb_core_process_customer_data(
cpf_cnpj VARCHAR(255) NOT NULL,
id_quotation INT NOT NULL,
seq_quotation INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255),
dt_birth DATE,
cd_insurance_type INT,
PRIMARY KEY (cpf_cnpj, seq_quotation, id_quotation),
FOREIGN KEY (cd_insurance_type) REFERENCES tb_nm_insurance_type(cd_insurance_type),
FOREIGN KEY (id_quotation) REFERENCES tb_core_process_id_quotation(id_quotation)
);

tb_core_process_id_quotation query:

CREATE TABLE tb_core_process_id_quotation(
id_quotation INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY (id_quotation)
);

So, I am having difficult to relation this three keys and to make this validation. When I try to create tb_core_process_customer_data the follow message shows me off:

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

Aipi
  • 2,076
  • 4
  • 18
  • 27

1 Answers1

5

MySQL uses the key on the auto_increment field to make look ups faster. You placed your auto_increment field as the 2nd field in your pk, therefore MySQL cannot use the pk on its own to check the value of the auto increment, hence the error message.

There are a number of ways to remediate the issue.

  1. Add a separate key (does not have to be unique or pk) on the seq_quotation field.

  2. The auto increment value will be unique anyway, therefore you can make it alone as pk and add another index on the other 2 fields. Packaging other fields with an auto_increment in a unique index or pk is a bit pointless - unless you use MySQL. (see notes below)

  3. I'm not sure if it works, but you may try to move seq_quotation to the 1st field in the current PK.

Note

If your intention was to use the auto increment to have an incrementing number per group, then

  1. make sure you either use myisam or bdb table type,
  2. or in case of innodb do not use auto_increment, use a trigger instead (see this question on SO for details)
Community
  • 1
  • 1
Shadow
  • 33,525
  • 10
  • 51
  • 64