June 11, 2022 at 7:25 am
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
supplier_id serial primary key references supplier
but it won't allow multiple primary key.
how then to proceed?
regards,
June 11, 2022 at 11:14 am
This was removed by the editor as SPAM
June 12, 2022 at 8:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
June 13, 2022 at 11:45 am
Serial is an integer. So you would use that data type in any foreign keys.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 13, 2022 at 3:52 pm
I'm pretty sure this is what you want. First, create the Supplier table with a primary key, and then reference that in the constraints of the Product table. (it's a whole lot easier to name your constraints on the front end than to try to figure out what they are when SQL Server assigns them a unique name that's a random bunch of characters).
CREATE TABLE Supplier (
supplier_id int identity primary key,
SupplierName VARCHAR(50) NOT NULL
);
GO
/* and then reference the supplier_id in the Products table */
CREATE TABLE product (
Product_id int identity,
ProductName VARCHAR(50) NOT NULL,
SupplierID INT
CONSTRAINT pkProduct PRIMARY KEY (Product_Id),
CONSTRAINT fkSupplierID FOREIGN KEY (SupplierID)
REFERENCES Supplier(Supplier_ID)
);
GO
June 13, 2022 at 6:23 pm
I'm pretty sure this is what you want. First, create the Supplier table with a primary key, and then reference that in the constraints of the Product table. (it's a whole lot easier to name your constraints on the front end than to try to figure out what they are when SQL Server assigns them a unique name that's a random bunch of characters).
CREATE TABLE Supplier (
supplier_id int identity primary key,
SupplierName VARCHAR(50) NOT NULL
);
GO
/* and then reference the supplier_id in the Products table */
CREATE TABLE product (
Product_id int identity,
ProductName VARCHAR(50) NOT NULL,
SupplierID INT
CONSTRAINT pkProduct PRIMARY KEY (Product_Id),
CONSTRAINT fkSupplierID FOREIGN KEY (SupplierID)
REFERENCES Supplier(Supplier_ID)
);
GO
So, that's T-SQL syntax and this is a PostgreSQL question. I like your answer, but it might not be workable here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
June 13, 2022 at 6:43 pm
Sorry, missed the n0t-so-fine print.
Not a clue about PostGRES. =(
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply