0

I can not find this any where (I am probably not asking the right questions). Would someone please point me in the right direction.

I am using a 2 column composite primary key in MySQL. I would like to display that value as a default value in a 3rd column in the same table and in another table. 

Col_1 value = ABC, 
Col_2 value = 123, 
PK          = ABC123, 

Col_3 default value = ABC123

Would anyone be able to explain how to do this?

I would prefer not to do this with a trigger or an insert statement if possible.

Thanks,

Ted

user1224675
  • 43
  • 4

3 Answers3

1

There aren't many situations where you really need the value to actually be in the column. You can get the same effect many times by creating the value in the select statement you use to fetch the data -- or in the php, ruby or whatever code you use to display the data.

For example:

SELECT Col_1, Col_2, CONCAT(NEW.Col_1,NEW.Col_2) as Col_3
FROM my_table;

This will resolve the issue for you while avoiding triggers and other fancy sql tricks.

I think this is a better approach as well in case someone wants to change the values of Col_1 or Col_2 -- if they change one of those values, then a column that held the combination of the two would also have to be updated. It makes things more complex than they need to be.

If you have some reason they must be in the database table, let us know.

Kevin Bedell
  • 13,254
  • 10
  • 78
  • 114
  • Hi Kevin, Thanks for the reply. I am using this PK as a unique memberID for a web app people will be using on their hand-helds. They will initially sign up when they are online using a simple PHP form. They will supply the ABC part and MySQL will supply an auto_increment part to make it unique. Once the entire PK is created it will be passed to the hand-held and stored as their memberID on the SQLite DB on the hand-held. If I do not create the complete PK somewhere how can I pass it to the hand-held? In 2 parts? – user1224675 May 10 '12 at 05:19
  • I see now what you are doing. I believe you can still push the key to the handheld using something like `SELECT CONCAT(NEW.Col_1,'-',NEW.Col_2) as the_key FROM my_table;` and then send it back to them. (Notice I added a hyphen '-' to make it easy to break the key back into pieces.) This has the added benefit of allowing you to keep primary key as only the Integer portion -- it will make accessing the data much faster. Then when the handheld sends you the key later, you simply break it apart in your code by splitting on the '-' and look up the two pieces. – Kevin Bedell May 10 '12 at 05:26
  • Kevin, I am having trouble getting my head around your idea. If you are interested you can take a look at this post, the accepted answer, as to how I am trying to create the memberID. [link](http://stackoverflow.com/questions/8021084/how-to-get-next-alpha-numeric-id-based-on-existing-value-from-mysql.) Thanks for your help. I will keep trying to catch up. The speed is not a major concern as the DB will never grow above 20K. The way I understand it the auto increment value will not be unique. EG 'abc001, abc002, def001, def002 etc.' – user1224675 May 10 '12 at 06:06
0

Use trigger. I haven't tested this yet but it's worth for you to try.

CREATE TRIGGER `auto_default` 
BEFORE INSERT ON `table` FOR EACH ROW 
SET NEW.Col_3=CONCAT(NEW.Col_1,NEW.Col_2);
Jonas T
  • 2,989
  • 4
  • 32
  • 43
  • Hello Jonas, Thanks for taking the time to reply. I thought there might be a simpler way than using triggers. Any other ideas? – user1224675 May 10 '12 at 05:09
0

I have not come across this . Probably you will have to use a trigger(you indicate this is not your choice) or has to build it into the application that does the Insert into the table .

When I scan through the material of MYSQL , it cleary states that "the default value must be a constant; it cannot be a function or an expression" .

For reference "http://dev.mysql.com/doc/refman/5.6/en/data-type-defaults.html"

For using a trigger the trigger sugested by Jonas should work ..

payyans
  • 96
  • 4
  • Hi payyans, I have read the page you refer to. The PK will be a constant. The only thing that makes this request different is that the constant value is coming from 2 columns, so I figured there must be a way to do this easily. You suggest ..."build it into the application that does the Insert into the table" ... I have done a similar thing in SQLite referencing a javascript variable as I use JS to create the tables, which works great. Is this the type of thing you have in mind, maybe with PHP? – user1224675 May 10 '12 at 05:06