CODEWITHSUNDEEP
mysql
IggY
IggY

Reputation: 3125

Auto increment a non PK column if null (MySQL 5.5)

I have a table User 

CREATE TABLE IF NOT EXISTS `user` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `clientid`  ???
);

I'd like clientid to be either specified (many familly member can have the same clientid) or auto_incremented from the max clientid if not specified.

I tried different type but get an error : Incorrect table definition; there can be only one auto column and it must be defined as a key.

I'd like to keep the query and the database as simple as possible (not use trigger or complex queries if that's possible as they are not supported by my ORM)

Upvotes: 0

Views: 93

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Your data model seems off. If you have a column called clientid, then I would expect you table have a table called Clients (my tables are usually plural names). The table Clients would have an auto-incremented id. Your above table would refer to it using a foreign key relationship:

CREATE TABLE `clients` (
    clientid int not null auto_increment primary key,
    . . .
);

CREATE TABLE IF NOT EXISTS `user` (
  userid bigint(20) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  clientid int,
  constraint fk_user_client foreign key (clientid) references clients(clientid)
);

Upvotes: 3

Related Questions