How to create a reference in a many-to-many relationship with transact SQL?



  • I am creating my first database using only code (I don't like to use graphical interfaces, I feel that I am not learning to write T-SQL code, it is my personal opinion), I do not know how to create the reference between my master tables

    In theory it is a database to be able to carry out inventories that are in different companies

    my boards are:

    Inventory

    assets

    I Created an intermediate Inventory_Asset table to be able to create multiple asset records in a company's inventory, and a company can have multiple inventories.

    Inventory

    -- TABLE INVENTORY
    GO
    CREATE TABLE Inventory
    (
        [inventory_id] INT NOT NULL IDENTITY(1,1),
        [company_id] INT,
        [name] VARCHAR(50),
        [observations] VARCHAR(500),
        [date_created] DATETIME DEFAULT(GETDATE()),
        CONSTRAINT PK_INVENTORY PRIMARY KEY (inventory_id),
        CONSTRAINT FK_INVENTORY_COMPANY FOREIGN KEY(company_id) REFERENCES Company(company_id)
    )
    

    Asset

    -- TABLE ASSET
    GO
    CREATE TABLE Asset
    (
        [asset_id] INT NOT NULL IDENTITY(1,1),
        [assettype_id] INT,
        [assetcategory_id] INT,
        [assetmadeby_id] INT,
        [code] INT,
        [model_name] VARCHAR(50),
        [model_number] VARCHAR(50),
        [serial_number] VARCHAR(30),
        [price] DECIMAL(10,2),
        CONSTRAINT PK_ASSET_ID PRIMARY KEY(asset_id),
        CONSTRAINT UQ_ASSET_CODE UNIQUE(code)
    )
    

    Inventory_Asset

    --TABLE INVENTORY_ASSET
    CREATE TABLE Inventory_Asset
    (
        asset_id INT,
        inventory_id INT,
        CONSTRAINT PK_INVENTORY_ASSET PRIMARY KEY (asset_id,inventory_id)
    )
    

    I accept criticism and suggestions. I want to learn 🙂



  • You already have foreign key in your inventory table (referencing company table), so I am going to assume you want to add foreign key to already existing table. You can do that like this:

    ALTER TABLE Inventory_Asset
    ADD CONSTRAINT FK_Inventory_Asset_asset
    FOREIGN KEY (asset_id) REFERENCES dbo.Asset (asset_id)
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2