How to use a randomly generated string as primary key in MySQL?

Question

I want to store data about passengers/guests, who book together, with a common reference number consisting of 6 alphanumeric characters. The primary key in the table would have to be (ref_number, number_of_guest) together (e.g. (ASDFJK; 1) refers to guest #1 within the reservation, (ASDFJK; 2) refers to guest #2 within the reservation). What I want is to create this reference number with a BEFORE INSERT ON trigger like SET NEW.ref = randomlyGeneratedString, but was sad to see that MySQL has only row level statements, therefore I can only add n different reference numbers to all people within the same reservation, but that just makes no sense.

What exactly is your question? Why does your reference number need to be 6 alphanumeric characters? — mba12
– mba12, Commented Apr 6, 2017 at 21:20
Ideally, you'd have one row in a booking table for the booking itself (and that table would contain the reference number); and separate table for the passengers/guests, that references the booking. — Uueerdo
– Uueerdo, Commented Apr 6, 2017 at 21:24
Let's suppose that I create a row in a table with that randomly generated reference, but how do I link that to the guests then? So if I have 3 guests in one reservation, I add 3 rows to the guest table, but how do I insert the reference number into the records of each of them? Again, the reference is create with a trigger generate_reference BEFORE INSERT ON reservations. I can solve the problem by simply generating the reference number in the reservation system and the website, but would prefer to do it within the database. It's such a shame that MySQL lacks statement level triggers... — Gabe
– Gabe, Commented Apr 7, 2017 at 20:31

Shakeer Mirza · Accepted Answer · 2017-04-13 06:34:53Z

1

I don't think you need a primary key, as usual primary key as auto increase,so you can add a field save,then you can do everything.

edited Apr 13, 2017 at 6:34

Shakeer Mirza

5,1182 gold badges20 silver badges42 bronze badges

answered Apr 7, 2017 at 2:35

Leal Li

2571 gold badge2 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use a randomly generated string as primary key in MySQL?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related