Auto Increment : SQL Server

Question

I have a table (Table1) with an auto_increment ID column as the primary key, and it's working well. I also have another table (Table2) with a foreign key using the ID column from Table1.

Now when I view records from Table2 I see NULL in the ID column and I don't get the auto generated numbers. I also tried with Identity(1,1) and see the same result.

How I can fix this?

As a follow-up, lets say I add a new column to an existing table and want to give it values from 1 to 20. How can I do that?

Answer 1

As Joel mentions, constraint values are not automatically inserted from one table to another. If the foreign key in Table2 is the primary key value from Table1, then you will need to capture @Table1's primary key value before inserting a new row into @Table2.

Here's a common way of doing this. You can run this example in 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 a table variable (@out) to capture the primary key value of Table1 upon a row insert:

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

Once we have these things we are ready to insert a new row into Table1 and capture its primary key value.

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

What's of note here is this guy:

OUTPUT inserted.pk_Table1 INTO @out

This allows us to capture any value inserted into Table1, but in this case, what we care about is pk_Table1.

If we view the resultset of @out:

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

We see the following:

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

Viewing the resultset of @Table1:

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

Returns:

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

Now that we've captured the primary key of Table1's new row, we can insert a new row into @Table2 with a value that ties it back to @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.' );

This guy...

( SELECT pk_Table1 FROM @out )

Returns the primary key value we captured when inserting a row into @Table1.

If we look at the resultset of @Table2:

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

We see:

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

And finally, we can join the two tables together based on their relationship:

/* 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;

Which returns:

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

Obviously, this isn't a deep dive into relating two tables based on a unique value, however, it's easy to understand.

Here is the full example you can run in 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;