时间戳

首页 > 解决方案 > 自动增量：SQL Server

sql-server - 自动增量：SQL Server

问题描述

我有一个表 ( Table1)，其中一个 auto_incrementID列作为主键，并且运行良好。我还有另一个表 ( Table2)，其中包含使用 Table1 中的 ID 列的外键。

现在，当我查看 Table2NULL中的记录时，我在ID列中看到并且没有得到自动生成的数字。我也尝试过Identity(1,1)并看到相同的结果。

我该如何解决这个问题？

作为后续，假设我在现有表中添加了一个新列，并希望为其赋予 1 到 20 的值。我该怎么做？

标签： sql-serversql-updateauto-incrementaltergeneric-foreign-key

解决方案

正如 Joel 所提到的，约束值不会自动从一个表插入到另一个表。如果 Table2 中的外键是 Table1 中的主键值，那么您需要在将新行插入到 @Table2 之前捕获 @Table1 的主键值。

这是执行此操作的常用方法。您可以在 SSMS 中运行此示例。

/* Create a mock-up of Table1 and Table2 */
DECLARE @Table1 TABLE ( 
    Table1_Value VARCHAR(50), pk_Table1 INT IDENTITY (1,1) NOT NULL PRIMARY KEY 
);

DECLARE @Table2 TABLE ( 
    fk_Table1 INT, Table2_Value VARCHAR(50), pk_Table2 INT IDENTITY (999,1) NOT NULL PRIMARY KEY 
);

声明一个表变量 (@out) 以在插入行时捕获 Table1 的主键值：

/* Declare an OUTPUT table to capture the PK of the value inserted into @Table1 */
DECLARE @out TABLE ( pk_Table1 INT );

一旦我们有了这些东西，我们就可以在 Table1 中插入一个新行并捕获它的主键值。

/* Insert a value into @Table1 and capture its PK */
INSERT INTO @Table1 ( Table1_Value ) 
OUTPUT inserted.pk_Table1 INTO @out
VALUES
    ( 'Table1_Value01' );

这里值得注意的是这个人：

OUTPUT inserted.pk_Table1 INTO @out

这允许我们捕获插入到 Table1 中的任何值，但在这种情况下，我们关心的是pk_Table1.

如果我们查看结果集@out：

/* Show the results captured in @out */
SELECT * FROM @out;

我们看到以下内容：

+-----------+
| pk_Table1 |
+-----------+
|         1 |
+-----------+

查看结果集@Table1：

/* Show the @Table1 resultset */
SELECT * FROM @Table1 ORDER BY pk_Table1;

回报：

+----------------+-----------+
|  Table1_Value  | pk_Table1 |
+----------------+-----------+
| Table1_Value01 |         1 |
+----------------+-----------+

现在我们已经捕获了 Table1 新行的主键，我们可以插入一个新行@Table2，其值将它与@Table1.

/* Insert a new value into @Table2 with a foreign key value pointing to @Table1 */
INSERT INTO @Table2 ( fk_Table1, Table2_Value )
    VALUES ( ( SELECT pk_Table1 FROM @out ), 'Table2 value related to Table1 value.' );

这家伙...

( SELECT pk_Table1 FROM @out )

返回我们在将行插入时捕获的主键值@Table1。

如果我们看一下结果集@Table2：

/* Show the @Table2 resultset */
SELECT * FROM @Table2 ORDER BY pk_Table2;

我们看：

+-----------+---------------------------------------+-----------+
| fk_Table1 |             Table2_Value              | pk_Table2 |
+-----------+---------------------------------------+-----------+
|         1 | Table2 value related to Table1 value. |       999 |
+-----------+---------------------------------------+-----------+

最后，我们可以根据它们的关系将两个表连接在一起：

/* Join the two tables based on their relationship */
SELECT
    t1.pk_Table1,
    t1.Table1_Value,
    t2.fk_Table1,
    t2.pk_Table2,
    t2.Table2_Value
FROM @Table1 AS t1
INNER JOIN @Table2 AS t2
    ON t1.pk_Table1 = t2.fk_Table1
ORDER BY
    t1.pk_Table1;

返回：

+-----------+----------------+-----------+-----------+---------------------------------------+
| pk_Table1 |  Table1_Value  | fk_Table1 | pk_Table2 |             Table2_Value              |
+-----------+----------------+-----------+-----------+---------------------------------------+
|         1 | Table1_Value01 |         1 |       999 | Table2 value related to Table1 value. |
+-----------+----------------+-----------+-----------+---------------------------------------+

显然，这并不是深入研究基于唯一值关联两个表，但是，它很容易理解。

这是您可以在 SSMS 中运行的完整示例：

/* Create a mock-up of Table1 and Table2 */
DECLARE @Table1 TABLE ( 
    Table1_Value VARCHAR(50), pk_Table1 INT IDENTITY (1,1) NOT NULL PRIMARY KEY 
);

DECLARE @Table2 TABLE ( 
    fk_Table1 INT, Table2_Value VARCHAR(50), pk_Table2 INT IDENTITY (999,1) NOT NULL PRIMARY KEY 
);

/* Declare an OUTPUT table to capture the PK of the value inserted into @Table1 */
DECLARE @out TABLE ( pk_Table1 INT );

/* Insert a value into @Table1 and capture its PK */
INSERT INTO @Table1 ( Table1_Value ) 
OUTPUT inserted.pk_Table1 INTO @out
VALUES
    ( 'Table1_Value01' );

/* Show the results captured in @out */
SELECT * FROM @out;

/* Show the @Table1 resultset */
SELECT * FROM @Table1 ORDER BY pk_Table1;

/* Insert a new value into @Table2 with a foreign key value pointing to @Table1 */
INSERT INTO @Table2 ( fk_Table1, Table2_Value )
    VALUES ( ( SELECT pk_Table1 FROM @out ), 'Table2 value related to Table1 value.' );

/* Show the @Table2 resultset */
SELECT * FROM @Table2 ORDER BY pk_Table2;

/* Join the two tables based on their relationship */
SELECT
    t1.pk_Table1,
    t1.Table1_Value,
    t2.fk_Table1,
    t2.pk_Table2,
    t2.Table2_Value
FROM @Table1 AS t1
INNER JOIN @Table2 AS t2
    ON t1.pk_Table1 = t2.fk_Table1
ORDER BY
    t1.pk_Table1;

推荐阅读