January 30, 2021 at 5:43 pm
Is it possible to create a table with composite primary key(ID+Identity (1,1)) and identity will increment individually for each composite key value pair.
Ex:
ID IdentityCol
1 1
1 2
2 1
2 2
2 3
2 4
3 1
3 2
As more rows are inserted with ID identiy col value for that ID should increase.
ID+IdentityCol form composite primary key
So if I insert new row for ID 2 the new row values inserted should be 2,5.
How to define such a table.
January 31, 2021 at 6:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
January 31, 2021 at 6:12 pm
Is there anything stopping you trying it out for yourself?
February 1, 2021 at 2:20 am
No, there's no direct way to do that.
However, you could use a standard identity column for the second part of the key, and use ROW_NUMBER() to get a sequential number when you SELECT from the table.
cluster on: ( ID, $IDENTITY )
1 1
1 2
2 3
2 4
2 5
2 6
3 7
3 8
CREATE VIEW dbo.view1
AS
SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY $IDENTITY) AS ID2, *
FROM dbo.table1
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 1, 2021 at 5:10 am
My personal opinion is that this has all the ear-markings of a classic invoice/invoice detail or work-order/work-order detail problem and should be handled as two individual tables rather than 1... maybe even 3 if you include a bridge-table.
--Jeff Moden
February 1, 2021 at 9:14 am
Exactly , thats what you are looking at.
February 1, 2021 at 3:47 pm
There's a pattern for this for you don't have to do this weird nonrelational numbering. Entities that exist by themselves are called "strong"; entities that depend on a strong entity to exist are called "weak" and need a reference back to the strong entity. The usual skeleton for this design pattern is:
CREATE TABLE Invoices
(invoice_nbr CHAR(16) NOT NULL PRIMARY KEY,
<< attributes that apply to the invoice as a whole>>
..);
CREATE TABLE Invoice_Details
(invoice_nbr CHAR(16) NOT NULL
REFERENCES Invoices (invoice_nbr)
ON UPDATE CASCADE
ON DELETE CASCADE,
<< attributes that apply to each invoice item individually>>
..);
Please notice how declarative reference integrity (DRI) maintains the proper relationship among the invoices and their details without you writing any code. The more war you can make the database engine do, the safer your schema will be. It will also probably be a lot faster, too.
Looking at your previous posts, you really don't understand how relational databases work. Your mental model is still stuck in a pointer chain database. You don't understand the basic concept of the key in the relational model.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 1, 2021 at 4:29 pm
Great example except for the last paragraph. No need for that.
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 1, 2021 at 7:14 pm
Exactly , thats what you are looking at.
How's that? I see two columns with you trying to create a clustered index on one table... I don't see two tables there.
--Jeff Moden
February 2, 2021 at 1:14 am
My personal opinion is that this has all the ear-markings of a classic invoice/invoice detail or work-order/work-order detail problem and should be handled as two individual tables rather than 1... maybe even 3 if you include a bridge-table.
Yes, in fact, I just assumed this was the detail table. Often I just use ( parent_key, $IDENTITY ) for the key for these tables, but I could see some queries wanting to see a sequential number for the detail lines, so, in that context, the q made sense to me.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
February 2, 2021 at 5:42 am
Thanks, I will start with what you suggested.
FYI..All I trying to get was something quick and dirty...then build onto something more correct and in line with DB designs. I do understand DB designs etc..may not be a pro but I do understand. Thanks anyway.
Thanks again for the example.Thanks @mike01
February 2, 2021 at 6:26 am
There is a way to get sequential numbers for each child (lineitem) record, starting at some specific value.
Note, that on a very busy system, this could cause a bottleneck.
Create a table to track the last used ChildID per ParentID
CREATE TABLE dbo.NextChildKey (
ParentKeyID int NOT NULL PRIMARY KEY CLUSTERED
, LastChildID int NOT NULL
);
GO
Create a proc to update the last used ChildID, and return the next ChildID
CREATE PROCEDURE dbo.GetNextChildKey
@ParentKeyID int
, @NextChildID int OUTPUT
AS
BEGIN
INSERT INTO dbo.NextChildKey ( ParentKeyID, LastChildID )
SELECT @ParentKeyID, 0
WHERE NOT EXISTS (SELECT 1 FROM dbo.NextChildKey AS dst
WHERE dst.ParentKeyID = @ParentKeyID);
UPDATE dbo.NextChildKey
SET @NextChildID = LastChildID += 1
WHERE ParentKeyID = @ParentKeyID;
END;
GO
Finally, call the proc each time you need the next ChildID
DECLARE @ParentKeyID int = 1
, @NextChildID int;
EXEC dbo.GetNextChildKey @ParentKeyID = @ParentKeyID
, @NextChildID = @NextChildID OUTPUT;
SELECT ParentKeyID = @ParentKeyID
, NextChildID = @NextChildID;
February 2, 2021 at 7:10 pm
That's a really bad idea. Identity is proprietary profit that numbers the insertions to a table in the old Sybase family of early relational databases. It is by definition, not as an option, nonrelational because it's not an attribute. It means that when someone puts in a weak entity into schema A, there is no guarantee that the same insertion number will appear in schema B, or anywhere else in the universe. If the weak entities are truly entities, then they have a key. This is the fundamental principle of RDBMS.
Let's continue the pattern. We have a strong entity (invoice for booze), a weak entity (order item, a six pack), and a still weaker entity that makes up the weak entity (parts within the order item, bottles of beer). You can't get an identity as you go down the levels. I'd have to check it out, but I seem to remember that the aircraft industry requires that you be able to locate individual physical line numbers on invoices and documents. But these guys also want you to be able to trace back to the mine that provided the ore that made up an aircraft part. The identification numbers are insane long!
When I designed retail databases, I always had a pass that would look for duplicate detail lines on the invoices. The point was to add the quantities together and consolidate them ino a single entry. Duplicate entries were much easier for a clerk to fix than if they had been spread all over the invoice detail lines.
Please post DDL and follow ANSI/ISO standards when asking for help.
February 2, 2021 at 9:02 pm
>> Create a table to track the last used child_id [sic] per parent_id [sic] <<
The terms "child" and "parent" come from the old pointer chain/hierarchical databases. The correct terms in RDBMS are "referenced" and "referencing" tables. A table can self-reference or have circular references in the schema.
While this is a bad design, it's also being done poorly. If you want this sort of thing, why don't you just use the CREATE SEQUENCE statement and let the engine handle all of these details? Of course, since a key is an identifier, by its very nature, you would never use integer data types for it. You're doing this because it makes it look like pointer chains again! The ISO 11179 standards and the metadata committee will also hit you for putting metadata in your data element names. Declaring something to be a key in its name violates the principle of mixing data and metadata. The term "_key" doesn't tell us what the data element is, but how it's used in a particular table in one particular schema. Even worse, we never attach two attribute properties to one attribute (hey, why no go crazy and have "child_key_value_code" instead of following all those ISO rules?
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply