Weak entity Auto-increment composite primary key

Question

I have a problem with weak entity and auto-increment.

Here is my 2 tables :

CREATE TABLE [sales].[orders](
    [order_id] [int] IDENTITY(1,1) NOT NULL,
    [customer_id] [int] NULL,
    [order_status] [tinyint] NOT NULL,
    [order_date] [date] NOT NULL,
    [required_date] [date] NOT NULL,
    [shipped_date] [date] NULL,
    [store_id] [int] NOT NULL,
    [staff_id] [int] NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [order_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [sales].[orders]  WITH CHECK ADD FOREIGN KEY([customer_id])
REFERENCES [sales].[customers] ([customer_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [sales].[orders]  WITH CHECK ADD FOREIGN KEY([staff_id])
REFERENCES [sales].[staffs] ([staff_id])
GO

ALTER TABLE [sales].[orders]  WITH CHECK ADD FOREIGN KEY([store_id])
REFERENCES [sales].[stores] ([store_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO



CREATE TABLE [sales].[order_items](
    [order_id] [int] NOT NULL,
    [item_id] [int] NOT NULL IDENTITY(1,1),
    [product_id] [int] NOT NULL,
    [quantity] [int] NOT NULL,
    [list_price] [decimal](10, 2) NOT NULL,
    [discount] [decimal](4, 2) NOT NULL,
PRIMARY KEY CLUSTERED 
(
    [order_id] ASC,
    [item_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [sales].[order_items] ADD  DEFAULT ((0)) FOR [discount]
GO

ALTER TABLE [sales].[order_items]  WITH CHECK ADD FOREIGN KEY([order_id])
REFERENCES [sales].[orders] ([order_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [sales].[order_items]  WITH CHECK ADD FOREIGN KEY([product_id])
REFERENCES [production].[products] ([product_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

OrderItem is a weak entity. I would like for each unique value of FOREIGN KEY([order_id]) that the value of primary key [item_id] starts from 0 without using stored procedure.

Insteaf of :

[order_id]	[item_id]
1	1
1	2
2	3
3	4

I need this :

[order_id]	[item_id]
1	1
1	2
2	1
3	1

I think I saw that on MySQL it is possible to achieve this quite simply. Could you tell me, how I can achieve this in sql server 2019 please ?

Edit--example from mysql---------------------------------

Answer 1

I would like for each unique value of FOREIGN KEY([order_id]) that the value of primary key [item_id] starts from 0

No. SQL Server doesn't have a reasonable way to do that.

The way you have it is optimal. The difference is purely cosmetic.

Answer 2

You can do it the uncomfortable way using triggers, you can do it using Sequences (SQL Sequences), but you need to reset the sequence on each order_id.

The way I'd do it is solve it on insert. So you'd go:

INSERT INTO [Sales].[Orders] 
  ([order_id], [Item_id],[customer_id],...)
VALUES 
  (@order_id,
    (SELECT coalesce(max([Item_id]),0) + 1 
    FROM [Sales].[Orders] 
    WHERE [order_id]=@order_id)
   @customer_id, ...);

So basically for each insert it queries to find the last item_id inserted and adds 1 to it, if you want it to start at 0 change the coalesce to -1, as the +1 is needed to get the next id.

you could define a scalar function with the select coalesce subquery and put that as the default value for the item_id column. and it should work similar to the mysql example, but I don't think I ever tried it to be sure.