varchar and auto increment togetter

Question

i am working on my own database, trying to learning as much as i can on my days off only by research and Google's stuff!! I am doing a simply table, however there is few doubts. I really appreciate for any help!

I did try to use VARCHAR instead INT to get the reservation_code from the form but i cannot use AUTO_INCREMET on it. I get error! Reservation code should looks like this: 123AB. Whats should i do?

CREATE TABLE flight
   (
    reservation_code VARCHAR(5) NOT NULL AUTO_INCREMENT,
    flying_from VARCHAR(20) NOT NULL,
    flying_to VARCHAR(20) NOT NULL,
    PRIMARY KEY (reservation_code) 
    );



CREATE TABLE passenger
   (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(20) NOT NULL,
    last_name VARCHAR(20) NOT NULL,
    sex VARCHAR(1) NOT NULL,
    PRIMARY KEY (id),
    FOREING KEY (reservation_code) REFERENCES flight(reservation_code), 
    FOREING KEY (flight_date) REFERENCES flight_detail(flight_date),
    FOREING KEY (bank_card) REFERENCES passenger_details(bank_card) 
    );

CREATE TABLE passenger_details
   (
   bank_card INT NOT NULL AUTO_INCREMENT,
   email VARCHAR(20) NOT NULL,
   mobile INT NOT NULL,
   PRIMARY KEY (bank_card) 
   );

CREATE TABLE flight_detail
   (
   flight_date DATE NOT NULL AUTO_INCREMENT,
   PRIMARY KEY (flight_date)       
   );

First of all please indicate (in tag as well) what database you're using (looks like MySQL to me). And second - how do you imagine autoincrement of the varchar? What would the logic be? — Yuriy Galanter, Dec 01 '13 at 00:57
Sure, its mysql! So, on html form, there is a field-text(reservation code) where user will type! This reservation code was generated from another system(another company) and this code will always increase! E.g: 001AB then 002AB, 003AB till the possibility ends! — Hugo, Dec 01 '13 at 01:16
If the code is generated on another system at another company, why do **you** need to increment it? It was generated by that other company; you should just be *storing it*. — Ken White, Dec 01 '13 at 01:25

score 0 · Accepted Answer · edited May 23 '17 at 12:05

0

You can't use AUTO_INCREMENT for solving that kind of problems. Instead you might use triggers. Anyway, there is solution already for your task How to make MySQL table primary key auto increment with some prefix.

Update: About resolving your issue with foreign keys. At first you have incorrect syntax in your code in question above FOREING must be FOREIGN. For creating foreign keys itself you need create appropriate columns and indexes in child table, as I see in your comment you just use FOREIGN statement without creating corresponding structure. This statement alone won't create necessary structure in your table, so you need to create that structure yourself explicitly before creating foreign keys. For example, for creating relationship between passenger and flight you need invoke:

CREATE TABLE flight(
   reservation_code VARCHAR(10),
   PRIMARY KEY (reservation_code)
);

CREATE TABLE passenger (
   id INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(20) NOT NULL,
   last_name VARCHAR(20) NOT NULL,
   sex VARCHAR(1) NOT NULL,
   reservation_code VARCHAR(10),

   PRIMARY KEY (id),
   INDEX (reservation_code),
   FOREIGN KEY (reservation_code)
   REFERENCES flight(reservation_code)
 );

See it in action here http://sqlfiddle.com/#!2/34ead

Also please read more about foreign keys in MySQL here

edited May 23 '17 at 12:05

Community

1
1

answered Dec 01 '13 at 01:16

Alexander Myshov

2,881
2
20
31

Perfect, but when 000AB become 999 then the character must change as well! Like: 999AB, 999AC, 999AD! Actually i have no idea what to do to make aleatory the sequence 999 when the characters ABCDE.. ends! – Hugo Dec 01 '13 at 01:25
So, then you need to look at this question http://stackoverflow.com/questions/6526008/composite-alphanumeric-primary-key-and-auto-increment. It will give you some guidances. – Alexander Myshov Dec 01 '13 at 01:37
Actually i still get stuck to make de Fk! CREATE TABLE passenger ( id INT NOT NULL AUTO_INCREMENT, name VARCHAR(20) NOT NULL, last_name VARCHAR(20) NOT NULL, sex VARCHAR(1) NOT NULL, PRIMARY KEY (id), FOREIGN KEY (reservation_code) REFERENCES flight(reservation_code), FOREIGN KEY (flight_day) REFERENCES flight_detail(flight_day), FOREIGN KEY (bank_card) REFERENCES passenger_details(bank_card) ); ERROR 1072 (42000): Key column 'reservation_code' doesn't exist in table. how is it possible if the column is in there? – Hugo Dec 01 '13 at 13:53
thats i just realized this morning! I made something like this: CREATE TABLE passenger_details ( bank_card INT(20) NOT NULL AUTO_INCREMENT, email VARCHAR(20), mobile INT(15) NOT NULL, passenger_id INT NOT NULL, PRIMARY KEY (bank_card), FOREIGN KEY (passenger_id) REFERENCES passenger(p_id) ); – Hugo Dec 02 '13 at 10:23

score 0 · Answer 2 · answered Dec 02 '13 at 02:55

0

Create a table to store your custom sequence with type is Char or varchar. Make sure only one client can get next sequence at a time in your code by using Thread locking.

answered Dec 02 '13 at 02:55

LHA

9,398
8
46
85

varchar and auto increment togetter

2 Answers2