如何为2键主键创建标识列

Question

这是我虚构的桌子：

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])
}

在我的.Net代码中，每次我把灯放进邮政时，我都会插入一个基于MAX索引的索引。这是一个foreach Lamp in Post, insert into Post-Lamp(postId, index, lampId)，但这似乎是非常错误的。我如何为灯索引建立一个增量列，以尊重每个后Id的变化？

编辑：.Net代码

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

Answer 1

嗯，也许你可以用一个视图来解决这个问题。

首先创建带有和标识列的链接表，例如：

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

现在使用row_number()窗口函数创建一个视图来计算每个post的索引。

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

只要您不尝试更新计算的列，这个视图是可更新的，如果它是实际的链接表(INSERT、UPDATE、DELETE、SELECT)，就可以使用它。

在db<>fiddle找一个演示。

(旁注:如果一盏灯一次不应该在两个不同的柱子上，如果我们说的是真实世界的灯具，这似乎是现实的，请考虑连接表中对灯的唯一约束。)

Answer 2

你的问题标题听起来像是你想要一个composite primary key，但我认为你想通过你的例子实现的只是一个有两个外键的表，如下所示：

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

Answer 3

两者有什么区别？

路灯-Id外键引用灯(灯-Id)和灯-索引int不为空?？

我对标题有点困惑，但似乎您应该查看SCOPE_IDENTITY()。它返回插入到同一作用域中标识列中的最后一个标识值。在插入Lamp表中插入值的存储proc中，在插入lamp后用Scope_Identity()设置变量，然后在Lamp-Post查询中设置该变量。