How to copy auto-increment id value from one table to another

auto-incrementdatabase-designPHP

I have a database with two tables, A and B.

Table A contains the title of a book and has an auto-increment column, book_id, that I declared as primary key.

In table B, I want to retain the price of the book having the same book_id value. In this case, the book_id column will be constrained as a foreign key and I dont want to auto-increment it.

How do I do it using PHP?

Best Answer

Your schema doesn't even make sense. You need to have an authoritative bookid somewhere.

CREATE TABLE book (
  bookid  serial PRIMARY KEY
  -- stuff
);

CREATE TABLE book_title (
  bookid  int REFERENCES book,
  title   text
);

CREATE TABLE book_price (
  bookid  int REFERENCES book,
  price   float
);

That said, if everything is 1:1 with book, just put it all on the same table. You don't need a separate table for every column:

CREATE TABLE book (
  bookid  int serial,
  title   text,
  price   numeric(10,8)
);

Related Solutions

Mysql – Getting duplicate primary key for auto increment column

I reached the max possible value of an int column. Problem resolved by altering table and setting big int than int.

The signed range of MIDINT is –8388608 to 8388607. The unsigned range is 0 to 16777215

Reference: http://help.scibit.com/mascon/masconMySQL_Field_Types.html

PostgreSQL – Auto Increment Char Varying Column Prefixed with Characters

PostgreSQL does not have "auto-increment" fields in the sense of MySQL's AUTO_INCREMENT, but I'm guessing you mean SERIAL.

If so, yes, what you describe is possible, but please, please don't do this.

A SERIAL is just shorthand for a CREATE SEQUENCE and a default value. e.g.

CREATE TABLE blah(
   id serial primary key
);

is actually shorthand for:

CREATE SEQUENCE blah_id_seq;

CREATE TABLE blah (
    id integer primary key default nextval('blah_id_seq'::regclass)
);

ALTER SEQUENCE blah_id_seq OWNED BY blah.id;

You can use this knowledge to cast the nextval return to a character type, e.g.

CREATE TABLE thisiswrong (
    id text primary key default CAST(nextval('thisiswrong_id_seq'::regclass) AS text)
);

but again, I beg of you, do not do this. It's horrible. It's wrong. You will regret it. Whatever you are trying to do, there is a better way to do it than this.

I have intentionally not shown how to generate a formatted field like PAT000001, but you can use any expression in a DEFAULT, not just a CAST. So look at the to_char function or the format function for how to do this if you insist.

The correct way to do what you want to do is not to do it. Get the application to display codes like PAT000001 when it sees a primary key value for patient_id like 1. The user never needs to know that you're just storing integers.

If you want other prefixes like PAT, DOC, etc, use a composite primary key, e.g.

CREATE TABLE saner_ish (
    categorycode varchar(3) CHECK (length(categorycode) = 3),
    patient_id integer default nextval('saner_ish_patient_id'::regclass),
    PRIMARY KEY (categorycode, patient_id)
);

(possibly with an enum type, an IN list, or whatever for validation).

Then in the application turn PAT00001 into ('PAT', 1) for queries, and reverse it for display to the user.

Best Answer

Related Solutions

Mysql – Getting duplicate primary key for auto increment column

PostgreSQL – Auto Increment Char Varying Column Prefixed with Characters

Related Question