Re: newbie db design question

On 2022-06-11 09:43:46 +0100, Ray O'Donnell wrote:
> On 11 June 2022 08:05:41 Rino Mardo <rino19ny(at)gmail(dot)com> wrote:
> 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
>
[...]
>
> 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)

Or somewhat simpler:

create table products (
product_id serial primary key,
description text,
supplier_id integer references supplier
);

(You need to create supplier before doing that, of course.)

hp

PS: I noticed that "products" is plural and "supplier" is singular. It's
genereally helpful to choose one convention and stick to it.

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp(at)hjp(dot)at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"