I'm currently trying the inheritance system with PostgreSQL but I have a problem with the auto-increment index in my child tables.
I have three tables: "Currency", "Crypto" and "Stable"
CREATE TABLE IF NOT EXISTS public.currency
(
id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
name VARCHAR(30) UNIQUE NOT NULL,
symbol VARCHAR(10) UNIQUE NOT NULL,
);
CREATE TABLE IF NOT EXISTS public.stable (id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY) INHERITS (public.currency);
CREATE TABLE IF NOT EXISTS public.crypto (id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY) INHERITS (public.currency);
I insered my data like this:
INSERT INTO public.stable (name, symbol) VALUES ('Euro', '€'), ('Dollar', '$'), ('Tether', 'USDT');
INSERT INTO public.crypto (name, symbol) VALUES ('Bitcoin', 'BTC'), ('Ethereum', 'ETH'), ('Litcoin', 'LTC');
But this is my problem: I would like to have a unique identifier that increments itself through my parent table "Currency". When I select, I have (take a look in my id: 1,2,3,1,2,3):
But, Is it possible to have something like this instead (1,2,3,4,5,6):
Is it a problem in my primary key?
Thank you
CodePudding user response:
We can try to use create sequence to set row numbers for sharing between multiple tables.
define a new sequence generator
create sequence n_id;
Then we can use this sequence
as below, sharing this sequence
for those three tables.
create sequence n_id;
CREATE TABLE IF NOT EXISTS currency
(
id INT default nextval('n_id') PRIMARY KEY,
name VARCHAR(30) UNIQUE NOT NULL,
symbol VARCHAR(10) UNIQUE NOT NULL
);
CREATE TABLE IF NOT EXISTS stable (id INT default nextval('n_id') PRIMARY KEY) INHERITS (currency);
CREATE TABLE IF NOT EXISTS crypto (id INT default nextval('n_id') PRIMARY KEY) INHERITS (currency);