Auto populating FKs

Question

Okay so I am playing around with setting up a new small DB. After laying it all out on the white board I have started to create it in SQL Server. I have gotten all my tables created with their associated PKs and FKs. However when I go to INSERT data into these tables I would like my PKs and FKs to auto populate which I thought I had setup during table creation. For example lets say I have 3 columns, one is PK, and another is a FK. When I INSERT INTO this table a new record, I would like to only have to specify information for the 3rd column name that is a non PK/FK and have the PK auto increment, and allow for a FK to be null.

The CREATE statement I used was:

CREATE TABLE CONSOLE
(
     consoleID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
     consoleName VARCHAR(25) NOT NULL,
     userID int FOREIGN KEY REFERENCES USER(userID)
);

So when I go to add a record I would like to use:

INSERT INTO CONSOLE
VALUES ('Xbox');

Answer 1

I hope that you will be populating the FK value later, by updating the table. Because, the RDBMS will not automatically populate FK value for you.

Create the table with default value for FK as NULL, as given below.

CREATE TABLE CONSOLE(
    consoleID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    consoleName VARCHAR(25) NOT NULL,
    userID int FOREIGN KEY REFERENCES USER(userID) DEFAULT NULL 
);

When you insert into the table, specify the column name. Now, the PK will be auto incremented value and FK will be NULL.

INSERT INTO CONSOLE(ConsoleName)
VALUES ('Xbox');

EDIT

I see from comments that you want to auto populate PK and FK. Auto populating FK is not possible. You can do that through two means:

1. Write a AFTER INSERT trigger, which will find the right FK value and INSERT into the table.
2. Write a stored procedure with parameters for insert. Get the value for ConsoleName and find the right FK value and INSERT into the table as given below.

DECLARE @rightUserId INT; -- Find value for this INSERT INTO CONSOLE(ConsoleName, UserId) VALUES ('Xbox',@rightUserId);