在两个表之间共享自动递增的主键 [英] Share auto-incremented primary key between two tables

问题描述

我需要分享两个表的id值，如

df

1

2

3

5

6

10

mf

4

7

8

9



我尝试过：



i为一个表创建了一个自动增量号

CREATE TABLE df （

id IDENTITY（1,1）

）;



CREATE TABLE mf（

id IDENTITY（1,1）

）;

i need to share the id value for both tables like
df
1
2
3
5
6
10
mf
4
7
8
9

What I have tried:

i have created a auto increment number for one table
CREATE TABLE df (
id IDENTITY(1,1)
);

CREATE TABLE mf (
id IDENTITY(1,1)
);

推荐答案

不，你不想这样做。 IDENTITY字段的概念是它是表 中行 的唯一标识符 - 并且多个表将具有不同的行具有不同的标识。

对于两个不同的表中的每一行，您希望或需要相同行标识的唯一时间是您的系统设计不当，创建需要一个表的两个表。解决方案很简单：将其设为一个表。如果你不这样做，那么当你开始生产时，SQL Server的多用户方面会咬你 - 而且很糟糕。

如果两个表之间有关联，那么这很简单：每个表都有一个单独的ID值（不必相同），但是一个表的列包含与另一个表的外键关系，并且包含第二个表中行的ID： br />

No, you don't want to do that. The idea of an IDENTITY field is that it is a unique identifier of a row within a table - and multiple tables will have different rows with different identities.
The only time you would want or need the same row identity for each row in two different tables is when you have badly designed your system, creating two tables where one is needed. The solution to that is simple: make it one table. If you don't, then the multiuser aspect of SQL Server is going to bite you - and badly - when you get to production.
If there is intended to be a correlation between the two tables, then that's simple: each table had a separate ID value (which doesn't have to be the same) but one table has a column which contains a Foreign Key relationship with the other and which contains the ID of the row in the second table:

CustNames
ID      INT, IDENTITY
CName   NVARCHAR(255)

ID      Cname
1       Jones Designs
2       Smiths Instruments
3       Joes Chips

CustAddresses
ID      INT, IDENTITY
Addr    NVARCHAR(1024)
CID     INT, FOREIGN KEY to CustNames.ID

ID      Addr                            CID
1       Smiths House, Smiths Lane       2
2       JonesTown                       1
3       High Street, Potato Town, Idaho 3

看看我的意思？

这篇关于在两个表之间共享自动递增的主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！