Violation of primarary key-CodePudding

I am trying to create a many-to-many relationship between two tables Asset and Inventory, I created an intermediate table called Asset_Inventory

One or more assets can be in an inventory, and an inventory can be in one or more assets,but it does not allow me to insert data

Mistake

The data in row 2 was not committed. Error source Message: Violation of PRIMARY KEY CONSTRAINT PK_INVENTORY_ASSET. Cannot insert duplicate key in object dbo.Inventory_Asset. The duplicate key value (1,1).

-- 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)
)


-- TABLE ASSET
GO
CREATE TABLE Asset
(
    [asset_id] INT NOT NULL IDENTITY(1,1),
    [company_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 FK_ASSET_COMPANY FOREIGN KEY(company_id) REFERENCES Company(company_id),
    CONSTRAINT UQ_ASSET_CODE UNIQUE(code)
)

--TABLE INVENTORY_ASSET
CREATE TABLE Inventory_Asset
(
    asset_id INT,
    inventory_id INT,
    CONSTRAINT PK_INVENTORY_ASSET PRIMARY KEY (asset_id,inventory_id),
    CONSTRAINT FK_ASSET_ID FOREIGN KEY (asset_id) REFERENCES Asset(asset_id),
    CONSTRAINT FK_IVENTORY_ID FOREIGN KEY (inventory_id) REFERENCES Inventory(inventory_id)
)

UPDATED

it's hard for me to ask this question

N companies have many assets; you need to have control over them. Have a list of assets by company, to which employee it was assigned, or in which department of the company that asset is located. a total of assets by company etc..

What is the purpose of this?

I need to physically label (bar code) each asset with a unique code Cell phones, monitors, keyboards, servers, PCs, laptops, chairs, furniture, etc...

Once all the assets have been tagged in the company 1 company 2 company 3

I have to scan each code printed on the asset and make a monthly cut

I have to create a report with this information

In the company 1 an inventory was made in the month of January

Cut of the month of January:

20 monitors (10 dell , 10hp)
20 chairs
10 computers

the next month this cut is made again to see if there is the same quantity, if the 2 inventories of the month of January and February are not the same, some asset was stolen and to be able to identify this

an inventory can have one or more assets, and an asset can be in one or more inventories.

I think I can do this with the Inventory_Asset table

asset_id	iventory_id
1	1
1	1
2	1
3	1
1	1
1	2
2	2
1	2
6	2
1	3
4	3
3	3
1	3

Am I wrong with my solution?

--TABLE INVENTORY_ASSET
CREATE TABLE Inventory_Asset
(
    [inventory_id] INT,
    [asset_id] INT,
    CONSTRAINT FK_ASSET_ID FOREIGN KEY (asset_id) REFERENCES Asset(asset_id),
    CONSTRAINT FK_IVENTORY_ID FOREIGN KEY (inventory_id) REFERENCES Inventory(inventory_id)
)

CodePudding user response：

CONSTRAINT PK_INVENTORY_ASSET PRIMARY KEY (asset_id,inventory_id),

PRIMARY KEYS must be unique. That is why you can't add this -- since it conflicts with your primary key. If you don't want that to be unique but you do expect to use it as an index you can make an index that does not enforce uniqueness and make something else your primary key.

CodePudding user response：

The answers and comments here are all correct; but perhaps we need to review what a many-to-many relation can be used for.

Option 1: we just need a link between two entities

This is what you have done. Basically it will serve if you want to know in which Inventories a given Asset is, which Assets a given Inventory holds, etc. In this scenario when you have established the link once (Asset 1 is in Inventory 1) there's no reason to re-establish it with further records.

Option 2: we want to store some information about the relation

This is what I suspect you really wanted. Suppose for instance you want to store how many Assets of type 1 are in Inventory 1. In this case what you want is to add a "Quantity" column to your Inventory_asset table. Once more, if there are 3 Assets of type 1 in Inventory 1 you won't duplicate the records: you will just create one record and put 3 in the new column.

Option 3: we really need multiple records

Suppose you want to store not only how many Assets of a given type are in a given Inventory, but also on which date a few of them were put there. So you may have 2 type 1 Assets put in Inventory 1 on May, 1st, and other 3 of them on May, 15th. In this case the date must become part of the PK, so that the system (and you too!) can distinguish between the different records. You may also decide to create an auto-increment column and use it as a PK instead; but note that while it may be simpler to handle, it would allow for unvoluntarily creating duplicate records, so I wouldn't recommend it.