Auto incrementing a foreign key value the same as the primary key of the parent table in SQL when inserting data?

Question

I have the following two connected tables as shown below in the Microsoft SQL Server.

When inserting data from visual studio, the foreign key in the Reservations table (Customer_ID) should be auto incremented and have the same value as the Primary key of the parent table Customer_Info (Customer_ID).

Should i do it via the SQL Statements in Visual Studio or it is possible to be done directly in the Database? How can i do it? Thanks

Visual Studio Windows Forms App Design:

The SQL Statement when button is clicked:

 var query = "insert into Customer_info(Customer_Name,Customer_Phone) " +
            "values('" + name + "','" + phone + "');" +
            " insert into Reservations(RV_DAY,Customer_ID,Table_Arrangement,RV_OT,RV_PW,RV_SIOC) " +
            "values('" + day + "',50,'" + tb + "','" + SR1 + "','" + SR2 + "','" + SR3 + "');";

Visual Studio Exception error:

Answer 1

It sounds like you have a slight misunderstanding of how these relationships works.

For the parent table, Customer_Info, your insert statements will look like this:

INSERT INTO Customer_Info
  (Customer_Name, Customer_Phone)
VALUES
  ('csandreas1', '555-5555');

Notice I didn't include the CustomerID field. As long as you have this set as an IDENTITY field, it will be auto-incremented in the way you describe.

Now here's an insert for the child table, Reservations:

INSERT INTO Reservations
  (RV_DAY, Customer_ID, Table_Arrangement, RV_OT, RV_PW, RV_SIOC)
VALUES
  ('', 5, '', '', '', '');

Note that I left all the other columns blank but Customer_ID, only because I don't really know what they are.

So, when inserting into the Reservations table, you will need to explicitly indicate which Customer_Info row is associated with that reservation.

SQL Server will validate during the insert process that there is really a row in Customer_Info with an ID of 5.

---

Regarding your code, you will need to execute the insert into Customer_Info first, and then get the resulting generated CustomerID, and then insert into the Reservation table.

You can get the inserted value using the SCOPE_IDENTITY() T-SQL function. In your example code above, you would replace "50" with "SCOPE_IDENTITY()" to insert the CustomerID for the just-added customer record.

---

As a VERY IMPORTANT side note regarding your .NET code, you should never concatenate user input with SQL strings. Use a SqlCommand object and then add parameters. There are many examples of this online, check one out here.

Doing what you're doing opens you up to the risk of SQL Injection attacks.