Create a custom "auto-increment" of the primary primary key?

Question

I have a set of parent-child tables (from 1 to many relationships). I build tables and doubt using PCs and auto-increment.

The parent table has a PK auto-dial number (used to store the title of the trade ticket). One entry here means a ticket.

The children table is used to store ticket information. One entry here is one item on the ticket (for example, coke, Mars bar, etc.).

I understand that PK for a child table must have 2 fields:

• PK Parent Tables
• A number that makes a position unique on this ticket

If I use IDENTITY , it will not restart after changing the parent PC.

I will show it with an example:

A) What SQL does

 Parent table Col1 Col2 1 1000 2 2543 3 3454 Note: Col1 is IDENTITY Child Table Col1 Col2 Col3 1 1 Coke 1 2 Mars Bar 2 3 Sprite 3 4 Coke 3 5 Sprite 3 6 Mars Bar Note: Col1 is taken from Parent Table; Col2 is IDENTITY 

B) What I want to achieve

 Parent table is the same as above Child Table Col1 Col2 Col3 1 1 Coke 1 2 Mars Bar 2 1 Sprite 3 1 Coke 3 2 Sprite 3 3 Mars Bar 

Note: Col1 is taken from the parent table; Col2 is reset after a change to Col1; Col1, consisting of Col2, is unique.

Does SQL Server use this key usage? Or do I need to encode it?

Answer 1

As an example:

 create table dbo.tOrders ( OrderID int not null identity primary key, CustomerID int not null ); create table dbo.tOrderPos ( OrderID int not null foreign key references dbo.tOrders, OrderPosNo int null, ProductID int null ); create clustered index ciOrderPos on dbo.tOrderPos (OrderID, OrderPosNo); go create trigger dbo.trInsertOrderPos on dbo.tOrderPos for insert as begin update opo set OrderPosNo = isnull(opo2.MaxOrderPosNo,0) + opo.RowNo from (select OrderID, OrderPosNo, RowNo = row_number() over (partition by OrderID order by (select 1)) from dbo.tOrderPos opo where OrderPosNo is null) opo cross apply (select MaxOrderPosNo = max(opo2.OrderPosNo) from dbo.tOrderPos opo2 where opo2.OrderID = opo.OrderID) opo2 where exists (select * from inserted i where i.OrderID = opo.OrderID); end; go declare @OrderID1 int; declare @OrderID2 int; insert into dbo.tOrders (CustomerID) values (11); set @OrderID1 = scope_identity(); insert into dbo.tOrderPos (OrderID, ProductID) values (@OrderID1, 1), (@OrderID1, 2), (@OrderID1, 3); insert into dbo.tOrders (CustomerID) values (12); set @OrderID2 = scope_identity(); insert into dbo.tOrderPos (OrderID, ProductID) values (@OrderID2, 4), (@OrderID2, 5); insert into dbo.tOrderPos (OrderID, ProductID) values (@OrderID1, 6); select * from dbo.tOrderPos; go drop trigger dbo.trInsertOrderPos; drop table dbo.tOrderPos; drop table dbo.tOrders; go 

The difficulty was to allow multiple inserts and delayed inserts. NTN

Another option is to use instead of the trigger:

 create trigger dbo.trInsertOrderPos on dbo.tOrderPos instead of insert as begin insert into dbo.tOrderPos (OrderID, OrderPosNo, ProductID) select OrderID, OrderPosNo = isnull( (select max(opo.OrderPosNo) from dbo.tOrderPos opo where opo.OrderID = i.OrderID), 0) + row_number() over (partition by OrderID order by (select 1)), ProductID from inserted i; end; 

Unfortunately, it seems that it is not possible to set OrderPosNo "not null", because several insertion elements will lead to duplicate keys. Therefore, I could not use the primary key and instead used a clustered index.

Answer 2

You do not have a one-to-many relationship. You have a many-to-many relationship. A parent can have many elements. Coke may belong to more than one parent.

You want three tables. An international table is sometimes called a conversion table.

http://en.wikipedia.org/wiki/Junction_table

Note. In the wiki article, they display only two columns in the join table, I believe that the best practice is that this table also has a unique field with auto-increment.

Note. Two union fields are usually assigned to a unique index.

Answer 3

You will have to code the logic for this yourself. You can facilitate the task by executing it using triggers and using the window functions (row_number () over (section in order parent_id by ...).

You can also let the primary key be just an identity column (parent_id doesn't have to be part of PK) and have a column "Sequence_Num" to track the int that you want to use reset with each parent_id. You can do this and still set the cluster index in cols parent_id / sequence_num.

IMHO the second option is better because it provides more flexibility without any major flaw. It also makes it easier to record a window function, since you can order a surrogate key (identity column) to preserve the insertion order when regenerating the num sequence. In both cases, you need to manage the sequencing of your sequenec_num column yourself.