newbie db design question

  • 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,

  • This was removed by the editor as SPAM

  • Thanks for posting your issue and hopefully someone will answer soon.

    This is an automated bump to increase visibility of your question.

  • 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
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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
  • pietlinden wrote:

    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
    The Scary DBA
    Author of: SQL Server 2022 Query Performance Tuning, 6th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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