CODEWITHSUNDEEP
phpmysqlunique
Nir Tzezana
Nir Tzezana

Reputation: 2342

ID auto increase but not unique

I have a table that I want to have an id that will auto increase itself but not be primary or unique.

Is this possible?

Upvotes: 1

Views: 77

Answers (3)

Danny Beckett
Danny Beckett

Reputation: 20806

You should really create another table, in that case.

E.g.

CREATE TABLE `Calls` (
  `Id`     INT(10)      AUTO_INCREMENT,
  `From`   VARCHAR(100) NOT NULL,
  `To`     VARCHAR(100) NOT NULL,

  PRIMARY KEY (`Id`)
) ENGINE=INNODB;

CREATE TABLE `CallHistory` (
  `Id`     INT(15)      AUTO_INCREMENT,
  `CallId` INT(10)      NOT NULL,
  `Text`   VARCHAR(255) NOT NULL,

  PRIMARY KEY (`Id`),
  KEY `CallHistory_Calls_idx` (`CallId`),
  CONSTRAINT `CallHistory_Calls`
    FOREIGN KEY (`CallId`)
    REFERENCES `calls` (`Id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE
) ENGINE=INNODB;

Here's a demo on SQLFiddle.

A benefit of this is that if you delete a row from Calls, the rows in CallHistory will also be deleted.

Running this query:

SELECT `Calls`.`Id`,
       `Calls`.`From`,
       `Calls`.`To`,
       `CallHistory`.`Text`

FROM   `Calls`, `CallHistory`
WHERE  `Calls`.`Id` = `CallHistory`.`CallId`;

Gives results something like this:

Upvotes: 2

Zaheer Ahmed
Zaheer Ahmed

Reputation: 28588

Yes, you need to set auto_increment constraint:

CREATE TABLE `test` (
  `testID` int(11) NOT NULL, //primary key here
  `testInc` int(11) NOT NULL AUTO_INCREMENT, //here is your non-primary auto increment column
  PRIMARY KEY (`testID`),
  KEY `testInc` (`testInc`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

and if you want this to be unique also then you may add unique constraint:

ALTER TABLE `test` ADD CONSTRAINT ux_unique_constraint UNIQUE `testInc`

Upvotes: 0

MaGnetas
MaGnetas

Reputation: 5108

This should work:

id int NOT NULL AUTO_INCREMENT

Anyway I don't see how it wouldn't stay unique unless you update existing values later

Upvotes: 0

Related Questions