Nir Tzezana Nir Tzezana - 2 years ago 88
MySQL Question

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?

Answer Source

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:

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download