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

Question

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?

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

If I understand what you are doing, you should insert a Post and a Lamp then use the id's generated in those tables to insert a Post-Lamp. — Crowcoder, Aug 20 '18 at 20:42
Every time that i add a Post-Lamp relation, i already add the lamp and the post id on a new row, but i need to add incremental column for arch Lamp in a post — Willian Soares, Aug 20 '18 at 20:53
To us (me), Lamp is not IN Post or vice versa but both are in Post-Lamp as the linking table. Thus your comment is a bit confusing that you try to insert Lamp in Post. You would insert Lamp in Post-Lamp right? — Mark Schultheiss, Aug 20 '18 at 20:56
"On my .Net code," perhaps show that so we gain insight into what you are trying to do. — Mark Schultheiss, Aug 20 '18 at 21:00
Does that index have to restart with 1 (or 0) for every post? Or is the actual value not important but the possibility to determine which lamp was added after which in a post? — sticky bit, Aug 20 '18 at 21:10
They have to restart for every post. If not possible, i will just make a simple identity column and then count a select for an specific post — Willian Soares, Aug 20 '18 at 21:13
@WillianSoares: Sounds like a feasible approach. You could also look into a solution using triggers, but as SQL Server has no row level triggers, that might also need and identity column. — sticky bit, Aug 20 '18 at 21:33

sticky bit · Accepted Answer · 2018-08-20T22:24:41.637

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.

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

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

This is what i want! Actually, the lamp table references a type of a lamp and the instance of a lamp inside a post only exists at the postlamp relation, but this view works fine and i will adapt on my project. — Willian Soares, Aug 21 '18 at 16:56

score 1 · Answer 2 · answered Aug 20 '18 at 20:57

1

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

answered Aug 20 '18 at 20:57

Kevin Kamonseki

141
1
6

Sorry, i forgot to say that i already have foreign keys referencing each table – Willian Soares Aug 20 '18 at 21:03
1

Table `PostLamp` should still have it's own primary key, which should probably be `PostId, LampId`. – Bacon Bits Aug 20 '18 at 22:21

J Sidhu · Answer 3 · 2018-08-20T21:26:24.270

0

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

edited Aug 20 '18 at 21:26

answered Aug 20 '18 at 20:58

J Sidhu

677
1
4
19

I can't have a Entity Framework beacuse the DB is huge and it's already done. I'm just making a update. – Willian Soares Aug 20 '18 at 21:10

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

3 Answers3