Pratik Pratik - 3 months ago 11
MySQL Question

Not able to create MYSQL table due to autoincrement duplicate

Below is my table schema.

CREATE TABLE `tbl_certificate`
(
`certificate_id` int(11) NOT NULL AUTO_INCREMENT,
`certificate_number` varchar(150) NOT NULL,
`vin_no_id` varchar(50) NOT NULL,
`product_id` int(11) NOT NULL,
`duration_id` int(11) NOT NULL,
`clientId` varchar(30) NOT NULL,
`first_name` varchar(50) NOT NULL,
`last_name` varchar(50) NOT NULL,
`state` varchar(256) NOT NULL,
`city` varchar(256) NOT NULL,
`mobile` varchar(30) NOT NULL,
`alternate_contact_number` varchar(30) NOT NULL,
`land_line` varchar(256) NOT NULL,
`address` varchar(300) NOT NULL,
`email_id` varchar(50) NOT NULL,
`registration_no` varchar(20) NOT NULL,
`make_id` varchar(255) NOT NULL,
`model_id` varchar(100) NOT NULL,
`vehical_sale_date` date NOT NULL,
`vehical_delivery_date` date NOT NULL,
`engine_number` varchar(30) NOT NULL,
`fuel` varchar(20) NOT NULL,
`odometer_reading` varchar(30) NOT NULL,
`color` varchar(32) NOT NULL,
`variant` varchar(16) NOT NULL,
`refered_by` int(11) NOT NULL,
`user_id` varchar(30) NOT NULL,
`created_date` date NOT NULL,
`complete_status` varchar(30) NOT NULL,
`price_amt` varchar(20) NOT NULL,
`servicetax_amt` varchar(20) NOT NULL,
`total_amt` varchar(20) NOT NULL,
`quotationNumber` bigint(20) NOT NULL,
`programstart` date NOT NULL,
`programend` date NOT NULL,
`uploaded_certificate` int(11) NOT NULL COMMENT '0-No,1-Yes',
`certificate_create_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`savedCertificateDate` datetime NOT NULL,
`purchaseCertificateDate` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`transaction_no` varchar(256) NOT NULL,
`expired` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


It throws an error like

Incorrect table definition; there can be only one auto column and it must be defined as a key

I am not getting what's issue, there is only one auto increment field name 'certificate_id'.

Please help.

Answer

You must include the PRIMARY KEY constraint in your DDL for an AUTO_INCREMENT.

CREATE TABLE `tbl_certificate`
(
    `certificate_id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ...
)

To be clear, AUTO_INCREMENT is basically just an attribute for indexed keys. It does not define an attribute as an index in the schema. So a column may be an indexed key without an AUTO_INCREMENT attribute, but it cannot have an AUTO_INCREMENT attribute if it is not indexed. See the MySQL documentation for more details.

Comments