How to make a identity column for a 2-key primary key

Here is my fictional tables:

Post
Post-Id int primary key identity,
Post-another-columns blahblahblah


Lamp
Lamp-Id int primary key identity,
Lamp-another-columns blahblahblah


Post-Lamp(n-n Relationship)
Post-Id FOREIGN KEY REFERENCES Posts(Post-Id),
Lamp-Id FOREIGN KEY REFERENCES Lamp(Lamp-Id),
Lamp-Index int not null, --(describe the Lamp index that is in this Post)
Post-Bar-another-columns blahblahblah,
CONSTRAINT PK_Post_Lamp PRIMARY KEY NONCLUSTERED ([Post-Id], [Lamp-Id])



On my .Net code, every time that i put a Lamp into a Post, i insert a index based at the MAX index that a Post has of the Lamps. It's a foreach Lamp in Post, insert into Post-Lamp(postId, index, lampId), but this seems very wrong.
How can i make a incremental column for the Lamp-Index that respects each change of Post-Id?

foreach Lamp in Post, insert into Post-Lamp(postId, index, lampId)

Edit: .Net code

int postId = await _repositories.Post.Add(post);

List<LampPost> lamps = model.Lamp.Select((l, i) => new Lamp = l, Index = i )
.Select(item => new LampPost

LampId= item.Lamp,
LampIndex = item.Index,
PostId = postId,
).ToList();

await _repositories.Lamp.Add(lamps);


3 Answers
3

Hmm, maybe you can easily solve this by using a view.

First create the linking table with and identity column, e.g.:

CREATE TABLE postlamp_t
(post integer,
lamp integer,
lampindex integer IDENTITY,
PRIMARY KEY (post
lamp),
FOREIGN KEY (post)
REFERENCES post
(id),
FOREIGN KEY (lamp)
REFERENCES lamp
(id));


Now create a view using the row_number() window function to calculate the index per post.

row_number()

CREATE VIEW postlamp
AS
SELECT post,
lamp,
row_number() OVER (PARTITION BY post
ORDER BY lampindex) lampindex
FROM postlamp_t;


As long as you don't try to update the calculated column, this view is updatable and you can use it like if it was the actual linking table (INSERT, UPDATE, DELETE, SELECT).

INSERT

UPDATE

DELETE

SELECT

Find a demonstration at db<>fiddle.

(Side note: If a lamp shouldn't be in two different posts at a time, which seems realistic if we're talking actual real world lamps, consider a unique constraint on the lamp in the linking table.)

Your question title makes it sound like you want a composite primary key, but I think what you are trying to achieve by your example is just a table with 2 foreign keys like this:

create table Posts(
PostId int,
OtherColumn varchar(50),
primary key (PostId)
)

create table Lamps(
LampId int,
OtherColumn varchar(50),
primary key (LampId)
)

create table PostLamp(
PostId int FOREIGN KEY REFERENCES Posts(PostId),
LampId int FOREIGN KEY REFERENCES Lamps(LampId),
)


PostLamp

PostId, LampId

Whats the difference between

Lamp-Id FOREIGN KEY REFERENCES Lamp(Lamp-Id) and Lamp-Index int not null ??

I'm little confused with the title, but seems like you should check out SCOPE_IDENTITY(). It returns the last identity value inserted into an identity column in the same scope. In your stored proc that inserts values in Lamp table set a variable with Scope_Identity() after inserting a lamp and then us that variable in Lamp-Post insert query..

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.