Primary and foreign key relations in several tables

Question

I have a question regarding SQL. I have the following SQL tables (Simplified for easy comprehension):

Client(clientId INT PRIMARY KEY)

Product (productId PRIMARY KEY)

Order (orderId PRIMARY, clientId which is foreign key to Client, productId which is foreign key to Product)

I also have a table called Inventory, which is supposed to contain the number of items in stock for each product. The primary key to Inventory is called productId (Primary Key), which references the foreign key in Order. Unfortunately, when I try to add to the Inventory table, it gives me the following error:

"The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Order_Iventory1". The conflict occurred in database "GestionInventaire", table "dbo.Inventory", column 'productId'."

I want the Inventory tables primary ID (productId) to be the foreign key in Order (productId), which comes from the Product table.

Any help is appreciated! If any further explanation is required I will try to elaborate further.

Answer 1

The primary key to Inventory is called productId (Primary Key), which references the foreign key in Order.

Why? To me, it would make more sense if the primary key in Inventory referenced the primary key in Product.

Answer 2

SQL example based on your above listed (SQL Server):

create table client (
  clientid int not null primary key
)
create table product (
  productid int not null primary key
)
create table [order] (
  orderid int not null primary key,
  clientid int not null foreign key references client (clientid),
  productid int not null foreign key references product (productid)
)

insert into client values (1)
insert into product values (1)

insert into [order] values (1,1,1)  -- should work
insert into [order] values (2,1,2)  -- should fail


create table inventory (
    productid int not null foreign key references product (productid),
    stock int not null default 0
)

insert into inventory values (1,10)  -- worked with no problems

Foreign key references check their values for existence in the parent table - when you see a foreign key constraint error it means the value you are trying to insert into the column with the foreign key constraint doesn't exist in the parent table it references.

Answer 3

It depends on how complex your stock information is for each product:

Storing only the latest set of stock numbers for each product

If you are simply storing the current stock number(s) for each product (e.g. a one-to-one mapping of Product to Inventory), then there's no need for Inventory to have it's own table, it would make more sense to store the number in stock directly in the Product table.

Whilst there may be a logical division in your code model between the product information and the inventory data, that doesnt necessarily mean its a good idea to store the data in multiple tables unless you're handling huge amounts of data and performance is becoming an issue. Even if it's stored in a single table, you can still seperate the data into multiple objects as and when you query it and build your model objects.

(That's not to say there aren't legitimate situations where it might be a good idea to do this, but I wouldn't consider it unless there were no better alternatives)

Storing multiple sets of stock numbers for each product

If on the other hand you're wanting to store a history of the stock levels (a one-to-many mapping from Product to Inventory) then it would make sense to have an Inventory table, but I'd suggest giving it it's own primary key and having a foreign key from productID to the Product table.

e.g. Inventory table containing "InventoryID" (primary key), "ProductID" (foreign key to Product.ProductID) and whatever columns you need to store the stock numbers.

Why Foreign key to Order table?

Either way it doesn't really make any sense for Inventory.productID to be a foreign key to Order.productID, since the stock is related to a product, not an order (unless you're doing live calculations based on the quantities in and out in which case the structure would be a bit more complex).