AUTO_INCREMENT in composite key

Question

I have table as below

personalInfo

CREATE TABLE personalInfo(userid BIGINT AUTO_INCREMENT PRIMARY KEY)

patentInfo

CREATE TABLE patentInfo 
(
 userid BIGINT,
 patentId BIGINT AUTO_INCREMENT,
 FOREIGN KEY (userid) REFERENCES personalInfo(userid), 
 PRIMARY KEY (userid,patentId)
)

While creating table patentInfo, I am getting error as

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

What I want to do is add patent for Users. So I was trying to use patentId as AUTO_INCREMENT and keep compiste primary key as combination of patentId & userid.

Any idea how can I get this done?

Update 1:

My AIM is, I don't want to insert patentId in mysql query. mysql itself will create next number.

What is the difference between user and patient? – Damir Sudarevic Jul 04 '12 at 12:10 — Damir Sudarevic, Jul 04 '12 at 12:10

score 1 · Accepted Answer · answered Jul 04 '12 at 09:57

1

Since patentId is AUTO_INCREMENT, then the it should be the primary key.

answered Jul 04 '12 at 09:57

xdazz

158,678
38
247
274

1

`patentInfo` table with the PK `patentId` sounds right, why do you want to composite primary key when only one is enough? – xdazz Jul 04 '12 at 10:03
I got what you meant to say. Kept `patentId` as PK. Still just curious, is there any way to create composite key like that?? – Fahim Parkar Jul 04 '12 at 10:15

Damir Sudarevic · Answer 2 · 2012-07-04T12:55:27.543

I'll use a Person instead user -- suppose you have a clinic...

Patient is a Person
Doctor is a Person
Doctor may become (is a) a Patient too.

enter image description here

Keep all common personal information in Person
Only patient-specific columns in Patient
Only doctor specific in Doctor
The subtype is inclusive, so discriminator has {B= both}
Note how primary key propagates into sub-type tables.

To summarize, for your specific example, both tables should have UserID as the primary key. In the second table it is also a foreign key too.

score 0 · Answer 3 · answered Jul 04 '12 at 09:57

0

you cannot have to auto-increment values in one table. so You have to manally insert the userid in patentinfo table at code level by using last_insert_id() or something similar to that instead of doing this at db-level

answered Jul 04 '12 at 09:57

Faizan Ali

509
12
35

score 0 · Answer 4 · answered Mar 30 '13 at 19:54

It seems you can have a composite primary key with one auto_increment column provided the auto_increment column is the first clause to the primary key.

The following works on my machine

CREATE TABLE patentInfo 
(
  userid BIGINT,
  patentId BIGINT AUTO_INCREMENT,
  PRIMARY KEY (patentId, userid)
)

Where as

PRIMARY KEY (userid, patentId)

gives the error you are seeing.

Tested on MariaDB 10.0 YMMV

AUTO_INCREMENT in composite key

Update 1:

4 Answers4