The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-17. For the latest, interactive version please visit https://mariadb.com/kb/.

Auto Increment Option in Maria DB

Hi:

This is my first day using Maria DB and pardon me if my question is elementary.

I am trying to use the Auto_increment option: CREATE TABLE synonym ( meddra_version_no VARCHAR(6) NOT NULL, synonym_id BIGINT NOT NULL AUTO_INCREMENT, llt_id BIGINT NOT NULL, update_user_id VARCHAR(100) NOT NULL, update_timestamp TIMESTAMP NOT NULL DEFAULT now(), CONSTRAINT xpksynonym PRIMARY KEY (meddra_version_no,synonym_id), CONSTRAINT R_79 FOREIGN KEY (meddra_version_no) REFERENCES meddra_version (meddra_version_no), CONSTRAINT R_67 FOREIGN KEY (meddra_version_no, llt_id) REFERENCES llt (meddra_version_no, llt_id) )

[ma-3.1.3][10.4.6-MariaDB]Incorrect table definition; there can be only one auto column and it must be defined as a key Execution Failed!

Does this mean that the column that is defined to auto-increment cannot be part of a composite PK? I even tried creating an unique index just on Synonym_id but that does not seem to work either. So, do I have to use the sequence to generate the value of the column?

Venki

Answer Answered by Ian Gilfillan in this comment.

See AUTO_INCREMENT - the auto_increment column must be a key (not necessarily primary or unique), but if it consists of multiple columns, the AUTO_INCREMENT column must be the first column.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.