Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Create custom "auto-increment" Compound Primary Key?

Tags:

sql

sql-server

I have a set of parent-child tables (1 to many relationships). I'm building the tables, and have some doubts about the use of PKs and auto-increment.

Parent table has an autonumber PK (is used for storing sales ticket header). One record here means on ticket.

Child table is used for storing ticket details. One record here is one line item in the ticket (e.g. coke, mars bar, etc)

I understand that PK for child table should have 2 fields:

  1. Parent tables's PK
  2. A number that makes the line item unique within this ticket

If I use IDENTITY, it will not "restart" after parent's PK changes.

I'll 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 Parent Table; Col2 resets after change in Col1; Col1 composed with Col2 are unique.

Does SQL Server implement this use of keys? Or should I need to code it?

like image 858
ryback Avatar asked Apr 03 '12 04:04

ryback


People also ask

Can auto increment be a primary key?

Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

How do I make my primary key AutoNumber?

In the Navigation Pane, right-click the table to which you want to add the primary key, and click Design View. Tip: If you don't see the Navigation Pane, press F11 to display it. Locate the first available empty row in the table design grid. In the Data Type field, click the drop-down arrow and click AutoNumber.

How do you make a primary key auto increment in SQL Developer?

Right click on the table and select "Edit". In "Edit" Table window, select "columns", and then select your PK column. Go to Identity Column tab and select "Generated as Identity" as Type, put 1 in both start with and increment field. This will make this column auto increment.

How can create existing primary key column auto increment in SQL Server?

If you're looking to add auto increment to an existing table by changing an existing int column to IDENTITY , SQL Server will fight you. You'll have to either: Add a new column all together with new your auto-incremented primary key, or. Drop your old int column and then add a new IDENTITY right after.


2 Answers

Just 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 has been to allow multiple inserts and delayed inserts. HTH

Another option is using an instead-of-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 doesn't seem to be possible to set the OrderPosNo "not null" because multiple inserts would lead to a duplicate key. Therefor I couldn't use a primary key and used a clustered index instead.

like image 69
Manfred Sorg Avatar answered Oct 14 '22 06:10

Manfred Sorg


You don't have a one-to-many relationship. You have a many-to-many relationship. A parent can have many items. A coke can belong to more than one parent.

You want three tables. The in-between table is sometimes called a junction table.

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

Note: In the wiki article they only show two columns in the junction table, I believe a best practice is for that table to also have a unique auto-incrementing field.

Note: The two joining fields are usually made a unique index.

like image 29
Steve Wellens Avatar answered Oct 14 '22 06:10

Steve Wellens