Re: newbie db design question

On 11 June 2022 08:05:41 Rino Mardo <rino19ny(at)gmail(dot)com> 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,