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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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 RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
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