Jump to content

Please please help. Composite primary keys?


Recommended Posts

I cant believe how little success I have had in discovering the answer to my problem on Google over the last hours of trawling... I have an ID number (salesrep_ID) that I want to use as the primary key, and I want it to be comprised of another field (delivery_id) and an auto increment number. i.e. delivery id = 1234 salesrep_id = 0011234 ....HOW? Oh god how? It seems the thing to do would be to create a new field that is autoincremented and then make salesrep_ID the composite primary key of the incremented field and delivery_id. But I want this to be displayed when I query "select * from salesreps" (salesreps is the table name): salesrep_ID0011234 NameJohn Smith Address123 Fake St delivery_id1234 This must be simple? I'm so confused :( My love and adoration for anyone who can help.

Edited by lucasjay
Link to post
Share on other sites

Ok, well I gave that a go - here is my trigger:

CREATE TRIGGER salesrep_id_create for insert ON salesrepsFOR EACH ROW	 set new.salesrep_id = concat(new.srid,new.delivery_id);

Where salesreps is the table, srid is an auto-incremented column, and delivery_id is a foreign key. Unfortunately this doesn't work because srid is auto-incremented and doesn't have a value until after the row is inserted. I have tried all the various combinations with various errors as a result: Here I found someone who had the same problem: http://mysqldatabaseadministration.blogspot.com.au/2006/03/mysql-triggers-accessing-value-of-auto.html So I don't think this is going to work :( Thanks for the suggestion though. I don't really get composite primary keys... Why can't I just make a composite primary key out of srid and delivery_id and display a column that is sriddelivery_id which displays the actual values of the composite keys? It seems odd that they exist but you can't actually see them when you select * from table...

Link to post
Share on other sites

Why can't you just concatenate the values when you select the data? If you have the autonumber and the other ID, why do you need another field that combines the two when you can just concatenate them when you select the data?

Link to post
Share on other sites

In this case, specifically, because it's for a university assignment, and the requirement that's getting me is as follows:"Salesreps have an ID code of which the last four characters give their delivery_id". So while I could select them and get that result, I didn't want to write any queries and instead just have the answer on the table. Maybe you're right though, I guess I could just create a view that combines the two columns into a new one. Still, it truly baffles me that want I was trying to achieve isn't possible at all...

Link to post
Share on other sites

I wouldn't say it's not possible at all, there are several workarounds on the link you posted. The reason why there's not a lot of discussion about this is probably because you're duplicating the data, you have 2 fields that you're combining into one. It's a good practice to avoid duplicating data, so most designers would keep those fields separate and combine them when they're needed instead of storing the duplicate data.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...