var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
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
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:
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.