Jump to content

lucasjay

Members
  • Posts

    12
  • Joined

  • Last visited

lucasjay's Achievements

Newbie

Newbie (1/7)

1

Reputation

  1. 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...
  2. 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...
  3. 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.
×
×
  • Create New...