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

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`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;

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:
Type_Code,Machine_Code,Clinic_Code,Name
A001,1,xxx,yyy
A001,2,abc,def
A001,3,aaa,bbb
A005,1,qqq,rrr
A005,2,ddd,eee

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.