CODEWITHSUNDEEP
sqlpostgresql
erik-sn
erik-sn

Reputation: 2600

One column that is sequenced and shared across multiple tables?

I am new to SQL and have been researching this. I think the answer lies in relationships but I could use some direction on what exactly needs to be done.

I'll start with a hypothetical example. I have three tables - Trucks, Cars, Motorcycles. Each table has its own primary key (which I have working just fine), as well as their own columns most of which are unique to the vehicle.

What I'm looking to do is add another column to the tables, ordernumber. I would like ordernumber to auto-increment like a primary key does. However, I want it to increment if ANY of the three tables has a new entry inserted. So if I first add two entries to the car table, the ordernumber on the first truck entry would be 3.

I'm sorry if this is trivial (or a bad idea), could just use some direction on this. If it makes any difference I am working in postgresql.

Upvotes: 0

Views: 45

Answers (1)

yxre
yxre

Reputation: 3704

It is possible. Here is a sample

CREATE SEQUENCE vehicle_id_seq;

CREATE TABLE motorcycles (
    id      SERIAL,
    ....
    order_id INT4 DEFAULT nextval('vehicle_id_seq') NOT NULL
);

CREATE TABLE cars (
    id      SERIAL,
    .....
    order_id INT4 DEFAULT nextval('vehicle_id_seq') NOT NULL
);

Depending on the schema, it would probably be better to have a separate table for orders. This feels kinda work-aroundish, and it is always better to have a simple solution.

The order table could be:

CREATE TABLE orders (
    order_id SERIAL,
    (meta data for order)
);

Then each of your tables could have a foreign key constraint to reference the order.

Upvotes: 2

Related Questions