0 Replies Latest reply on Sep 3, 2003 9:48 PM by William Houck

    Primary Key Generation with MySQL

    William Houck Newbie

      Using entity commands in jbosscmp-jdbc.xml, can I auto generate a primary key without modifying my tables' sql create statement or the app itself? Stated another way: Does JBoss/MySQL offer a service, independent of my specific app, that will auto generate a unique value for use as a primary key?

      I'm using jboss-3.2.1_tomcat-4.1.24 and MySQL 4.0.14.

      Could one of these examples be modified somehow to answer the above question?

      This auto-increment technique doesn't quite meet the challenge. Notice that the INNODB attribute "auto_increment" has been used on column "id".

      <entity-command name="mysql-get-generated-keys"/>

      CREATE TABLE `customer` (
      `id` int(10) unsigned NOT NULL auto_increment,
      PRIMARY KEY (`id`)
      ) TYPE=InnoDB;

      The auto-increment sql (see tag entity-command) in this example is for the PostgreSQL database. Is there a similar sql statement for use with MySQL? Notice that the create statement was not modified to auto generate the primary key.

      <entity-command name="pk-sql">
      SELECT nextval('generalSEQ')
      <!-- SELECT nextval('accountSEQ') -->

      CREATE TABLE AccountEJBTable (
      __PMPrimaryKey BIGINT,
      _contactInfo___PMPrimaryKey BIGINT,
      _creditCard___PMPrimaryKey BIGINT,
      status VARCHAR(255),
      CONSTRAINT pk_AccountEJBTabl PRIMARY KEY (__PMPrimaryKey));

      Finally, these techniques seem centered around pk's gen'd in sequence. What if I just want a guid generated, does that change the problem at all?