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

All we need is an easy explanation of the problem, so here it is.

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 🙂

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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)

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags: , ,

Leave a Reply