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

[baileigh]

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

[fbio]

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)