Re: newbie db design question

["Ray O'Donnell" <ray@xxxxxxxxxxxx>]

On 11 June 2022 08:05:41 Rino Mardo <rino19ny@xxxxxxxxx> wrote:

hello!

really noob question here. i have this experimental database design:

create table products (

product_id serial primary key,

description text,

supplier_id????) ;

create table supplier (

supplier_id serial primary key,

description text) ;

the products table should be linked to the supplier table via "supplier_id" column. i can't find out what would be the data type of supplier_id in table products to do that. i tried

Hi there,

"serial" isn't a real type - it's just a shortcut for "integer not null default nextval(....)". Read about it here:

https://www.postgresql.org/docs/current/datatype-numeric.html#DATATYPE-SERIAL

So the supplier_id column in the first table should be of type "integer".

supplier_id serial primary key references supplier

but it won't allow multiple primary key.

how then to proceed? 

You need a foreign key.... add this to the first table:

constraint <constraint name> foreign key (supplier_id) references supplier(supplier_id)

I hope this helps.

Ray.

regards,