0

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

Mysql

8
  • Does item_id is used in some FK relation? or this is sequental items enumeration simply? Does the row can be deleted from order_items?
    – Akina
    Aug 30, 2023 at 8:45
  • No, item_id is not referenced in another table as FK. Yes, the row can be deleted from order_items and if a row in Order table (parent table) is deleted the related row in order_items will be deleted thanks to delete cascade.
    – kacem
    Aug 30, 2023 at 8:59
  • If a row from order_items is deleted does the values in item_id must be recalculated for the enumeration to be sequental, or the holes in the values after the rows removing is a norma?
    – Akina
    Aug 30, 2023 at 9:06
  • No, item_id doesn't need to be recalculated . It doesn't matter if the sequence has holes ... 1-2-4-5
    – kacem
    Aug 30, 2023 at 14:20
  • If so then you do not need in this column at all - enumerate in a query.
    – Akina
    Aug 30, 2023 at 14:40

2 Answers 2

2

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.

0

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.

4
  • This is a terrible idea. It creates a race condition and introduces the real possibility of getting value clashes.
    – Sean Lange
    Aug 30, 2023 at 16:05
  • the normal way would be to do it with code not sql, but if you had to do it it'd work. you'd only get value clashes depending on how you use snapshots and transactions. but it'd be totally workable without any clashes.
    – Rostol
    Aug 30, 2023 at 16:10
  • The MAX(Column) + 1 is an antipattern. It can be forced to work but there are a number of caveats to that. There are far more problems with this type of approach than it is worth.
    – Sean Lange
    Aug 30, 2023 at 16:11
  • If you read the example this is the way MySQL does it, so it clearly can be done this way. If you think there is a problem with concurrency, as max is filtered by order_id, only if you have several processes concurrently writing -to the same order_id- you could have a clash. This is highly unlikely as normally the concurrency would be per order_id rather than per item_id. there are other ways to do it like top 1 sort by item_id desc but that needs an additional sort per insert.
    – Rostol
    Aug 30, 2023 at 22:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.