lucasjay
-
Posts
12 -
Joined
-
Last visited
Content Type
Profiles
Forums
Events
Posts posted by lucasjay
-
-
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...
-
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.
Please please help. Composite primary keys?
in SQL
Posted
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...