Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004

    Unanswered: Automatic Composite Key generation

    Greetings, MySQL community

    I have created a table called MACHINE which consists of the fields below :

    Machine_Code : Integer (AUTO_INCREMENTING)
    Type_Code : Char(5)
    Clinic_Code : Char(5)
    Name : Char(20)

    Type_Code & Clinic_Code are foreign keys belonging to other tables. I was wondering if I can create a composite key comprising of Type_Code + Machine_Code. That is e.t.c. if Type_Code = 'A001' then the first machine would be A001000, the second A001001 e.t.c. Do I need some sort of stored procedure to do that?

    thx, in advance

    George Papadopoulos

  2. #2
    Join Date
    Jul 2004
    Dundee, Scotland
    you may need a little more than what the mysql auto_increment can do. I put together the following example. Notice that Type_Code comes first, then Machine code which uses auto_increment.

    auto_increment starts at '1' each time a new Type_Code is inserted, not zero.

    CREATE TABLE `machine` (
    `Type_Code` varchar(5) NOT NULL default '',
    `Machine_Code` int(11) NOT NULL auto_increment,
    `Clinic_Code` varchar(5) default NULL,
    `Name` varchar(20) default NULL,
    PRIMARY KEY (`Type_Code`,`Machine_Code`)

    An example mysql insert statement:
    INSERT INTO `machine` ( `Type_Code` , `Machine_Code` , `Clinic_Code` , `Name` )
    VALUES ('A001', '', 'abc', 'def');

    what the table data might look like:

    Is that close to what you need?

    As you can see, the other thing we can't do via mysql is pad the Machine_Code field with zero's. This could be done when retreiving the data form the table for presentation purposes.

    I haven't used stored procedures with mysql. you might need to check your mysql version number then access the mysql online manual to see if that stuff is available.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts