CODEWITHSUNDEEP
mysqlsqlauto-incrementcreate-table
user6304394
user6304394

Reputation:

How to have an auto increment column in MySQL table without making it a primary key?

Here is my SQL for creating 2 tables with a respective primary and foreign keys. The problem is that I want to have an auto-increment column in my child table but I want to make some other column as a primary key. How can I fix this?

CREATE TABLE patient (
_id INT(5) UNSIGNED AUTO_INCREMENT NOT NULL,
pn VARCHAR(11) DEFAULT NULL,
first VARCHAR(15) DEFAULT NULL,
last VARCHAR(25) DEFAULT NULL,
dob DATE DEFAULT NULL,
PRIMARY KEY (_id)
);

CREATE TABLE insurance (
_id INT(5) UNSIGNED AUTO_INCREMENT,
patient_id INT(5) UNSIGNED NOT NULL,
iname VARCHAR(40) DEFAULT NULL,
from_date DATE DEFAULT NULL,
to_date DATE DEFAULT NULL,
PRIMARY KEY (patient_id),
CONSTRAINT fk_insurance FOREIGN KEY (patient_id)
REFERENCES patient(_id)
);

It gives me ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key and I don't know how to fix this. I am a beginner in this and I need help here thanks.

Upvotes: 0

Views: 96

Answers (1)

GMB
GMB

Reputation: 222672

Consider:

CREATE TABLE insurance (
    _id INT(5) UNSIGNED AUTO_INCREMENT,
    patient_id INT(5) UNSIGNED NOT NULL,
    iname VARCHAR(40) DEFAULT NULL,
    from_date DATE DEFAULT NULL,
    to_date DATE DEFAULT NULL,
    PRIMARY KEY (patient_id),
    KEY(_id),
    CONSTRAINT fk_insurance FOREIGN KEY (patient_id)
    REFERENCES patient(_id)
);

That is, the auto-incremented column must at least be a key. You can still use another column as the primary key if you like.

Upvotes: 1

Related Questions