How to insert the foreign key into a subtype table in SQL Server

supertype and subtype relationships examples sql
entity subtype example
what is an entity supertype and why is it used
supertype and subtype in erwin
subtyping data model
multiple foreign keys sql
nullable foreign key sql
one-to-many foreign key null

I have a supertype table called Student, and its subtype table called OtherStudents. I am wondering how to write a stored procedure that will insert the foreign key value into OtherStudents table every time a new record with a student type Other is inserted into Student.

Student table:

    CREATE TABLE [dbo].[Student]
    (
        [STUD_ID] [INT] IDENTITY(1000009,1) NOT NULL,
        [STUD_NAM] [VARCHAR](30) NOT NULL,
        [STUD_EMAIL] [VARCHAR](50) NULL,
        [CAMP_NAM] [VARCHAR](50) NULL,
        [CAMP_ZIP] [INT] NULL,
        [STUD_TYP] [CHAR](5) NOT NULL,

        CONSTRAINT [PK_Student] 
            PRIMARY KEY CLUSTERED ([STUD_ID] ASC)
    )
    GO

    ALTER TABLE [dbo].[Student] WITH CHECK 
        ADD CONSTRAINT [FK_Student_Campus] 
            FOREIGN KEY([CAMP_NAM], [CAMP_ZIP]) REFERENCES [dbo].[Campus] ([CAMP_NAM], [CAMP_ZIP])
    GO

    ALTER TABLE [dbo].[Student] CHECK CONSTRAINT [FK_Student_Campus]
    GO


`OtherStudents` table:

    CREATE TABLE [dbo].[OtherStudents]
    (
        [OSTUD_ID] [INT] IDENTITY(1000009,1) NOT NULL,
        [STUD_ST] [VARCHAR](30) NULL,
        [STUD_APT] [VARCHAR](5) NULL,
        [STUD_CITY] [CHAR](6) NULL,
        [STUD_STATE] [CHAR](2) NULL,
        [STUD_ZIP] [INT] NULL,
        [RPT_ATTM] [VARBINARY](4000) NULL,
        [RPT_EYESCR] [VARBINARY](4000) NULL,
        [DATE_LASTPASS] [DATE] NULL,
        [DATE_LASTVSP] [DATE] NULL,
        [STUD_ID] [INT] NULL,

        CONSTRAINT [PK_OtherStudents] 
            PRIMARY KEY CLUSTERED ([OSTUD_ID] ASC)
    )
    GO

    ALTER TABLE [dbo].[OtherStudents] WITH CHECK 
        ADD CONSTRAINT [FK_OtherStudents_Student] 
            FOREIGN KEY([STUD_ID]) REFERENCES [dbo].[Student] ([STUD_ID])
    GO

    ALTER TABLE [dbo].[OtherStudents] CHECK CONSTRAINT [FK_OtherStudents_Student]
    GO

I wrote two stored procedures:

        ALTER PROCEDURE [dbo].[BusPassStudents_Insert]
            (@STUD_ST VARCHAR(30),
             @STUD_APT VARCHAR(5),
             @STUD_CITY CHAR(6),
             @STUD_STATE CHAR(2),
             @STUD_ZIP INT,
             @RPT_ATTM AS VARBINARY(4000) = NULL,
             @RPT_EYESCR AS VARBINARY(4000) = NULL,
             @DATE_LASTPASS DATE,
             @DATE_LASTVSP AS DATE = NULL)
        AS
        BEGIN
            SET NOCOUNT ON

            INSERT INTO dbo.OtherStudents (STUD_ST, STUD_CITY, STUD_STATE, STUD_ZIP, RPT_ATTM, RPT_EYESCR,DATE_LASTPASS, DATE_LASTVSP)
            VALUES (@STUD_ST, @STUD_CITY, @STUD_STATE, @STUD_ZIP, @RPT_ATTM, @RPT_EYESCR, @DATE_LASTPASS, @DATE_LASTVSP)
        END

        ALTER PROCEDURE [dbo].[Stud_InsertNew]
            (@STUD_NAM VARCHAR(30),
             @STUD_EMAIL VARCHAR(50),
             @CAMP_NAM VARCHAR(50),
             @CAMP_ZIP INT,
             @STUD_TYP CHAR(5))
        AS
        BEGIN
            SET NOCOUNT ON

            INSERT INTO dbo.Student(STUD_NAM, STUD_EMAIL, CAMP_NAM, CAMP_ZIP, STUD_TYP)
            VALUES (@STUD_NAM, @STUD_EMAIL, @CAMP_NAM, @CAMP_ZIP, @STUD_TYP);

            DECLARE @STUD_ID INT
            SET @STUD_ID = SCOPE_IDENTITY()
            SELECT @STUD_ID
            WHERE @STUD_TYP = 'Other'

        END

Here is my C# code I have:

int campzip = int.Parse(ddlCamp.SelectedValue.Trim());
int StudentZip = int.Parse(txtStdZip.Text);
DateTime DateLastPass = DateTime.ParseExact(txtDateLastPass.Text, "yyyy-MM-dd", null);

using (var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("cis-laredoConnectionString")))
{
    connection.Open();

    var cmd = new SqlCommand("dbo.Stud_InsertNew", connection);
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.AddWithValue("@STUD_NAM", txtStdName.Text);
    cmd.Parameters.AddWithValue("@STUD_EMAIL", txtStdEmail.Text);
    cmd.Parameters.AddWithValue("@CAMP_NAM", ddlCamp.SelectedItem.ToString());
    cmd.Parameters.AddWithValue("@CAMP_ZIP", campzip);
    cmd.Parameters.AddWithValue("@STUD_TYP", "Other");

    int getStudID = (int) cmd.ExecuteScalar();
    cmd.Dispose();

    connection.Close();

    connection.Open();
    var cmd2 = new SqlCommand("BusPassStudents_Insert", connection);
    cmd2.CommandType = CommandType.StoredProcedure;

    cmd2.Parameters.AddWithValue("@STUD_ID", getStudID);
    cmd2.Parameters.AddWithValue("@STUD_ST", txtStdStreet.Text);
    cmd2.Parameters.AddWithValue("@STUD_APT", txtStdApt.Text);
    cmd2.Parameters.AddWithValue("@STUD_CITY", txtStdCity.Text);
    cmd2.Parameters.AddWithValue("@STUD_STATE", txtStdState.Text);
    cmd2.Parameters.AddWithValue("@STUD_ZIP", StudentZip);
    cmd2.Parameters.AddWithValue("@RPT_ATTM", fuAttend.FileBytes);
    cmd2.Parameters.Add("@DATE_LASTPASS", SqlDbType.Date).Value = DateLastPass;

    cmd2.ExecuteNonQuery();
    cmd2.Dispose();

    connection.Close();
}

I am trying to use ExecuteScalar to retrieve the inserted STUD_ID in Student table and post it back into the OtherStudents table.

I am getting error:

System.InvalidCastException: 'Specified cast is not valid.'

Any help would be appreciated!

You have to select something from your stored procedure to get the scalar value in the C# which is you missed, so in your Stud_InsertNew you can select the STUD_ID and use ExecuteScalar() in C# code as:

int getStudID = (int)cmd.ExecuteScalar();

Changes in stored procedure:

ALTER PROCEDURE [dbo].[Stud_InsertNew]
    (@STUD_NAM VARCHAR(30),
     @STUD_EMAIL VARCHAR(50),
     @CAMP_NAM VARCHAR(50),
     @CAMP_ZIP INT,
     @STUD_TYP CHAR(5))
AS
BEGIN
    SET NOCOUNT ON

    INSERT INTO dbo.Student(STUD_NAM, STUD_EMAIL, CAMP_NAM, CAMP_ZIP, STUD_TYP)
    VALUES (@STUD_NAM, @STUD_EMAIL, @CAMP_NAM, @CAMP_ZIP, @STUD_TYP);

    // Need this line which returns the latest inserted Student Id
    SELECT TOP 1 [STUD_ID]
    FROM dbo.Student
    ORDER BY [STUD_ID] DESC
END

After a comment from Mitch Wheat and also found here, that the best way to use SCOPE_IDENTIY() if you want to get the latest inserted records primary key's value, so you just need to change the SELECT statement to this:

DECLARE @STUD_ID INT
SET @STUD_ID = SCOPE_IDENTITY()
SELECT @STUD_ID

Implementing the Base Table Structures, on, the design is geared specifically for implementation on SQL Server 2005. Dealing with categories and subtypes is a key element in determining how logical The syntax of the statement for adding foreign key constraints is pretty simple:. SQL FOREIGN KEY Constraint. A FOREIGN KEY is a key used to link two tables together. A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

This is how I'd like to approach this.

  1. Convert two procedures into a single one by passing all the required parameters.

  2. Make use of the "inserted" table data to get the information that was just Inserted into table A and store the reference in table B.

*(i) You may want to pass the rest of the parameters required to store the data in table B. I haven't included them in the stored procedure.

(ii) I'm trying to insert data into table B if only the check on birthday passes. In your case the predicate/condition will differ.

USE SOMEDB;


CREATE TABLE A
(Id Integer IDENTITY(1,1),
[Name] VARCHAR(20),
[Birthday] DATE
)

create table B
(
B_Id INTEGER IDENTITY(10001,1),
A_Id Integer NULL,
ColumnX VARCHAR(20),
ColumnY varchar(20))

CREATE PROCEDURE [uspInsertIndividualDetails]
(@name varchar(20),
@birthday date
)
AS
BEGIN

CREATE TABLE #tmpXYZ(iD INT,birthday date);

INSERT INTO A([Name],[Birthday])
OUTPUT inserted.Id,inserted.Birthday into #tmpXYZ
select @name, @birthday


IF exists (select 1 from #tmpXYZ where Birthday >'2018-01-01')
begin
INSERT INTO B(A_Id,ColumnX, ColumnY)
SELECT  Id,'A Value', 'Some Other Value' FROM #tmpXYZ
end

END
execute [uspInsertIndividualDetails] @name ='John Doe', @birthday = '1972-10-01'

SELECT * FROM a
SELECT * FROM b
execute [uspInsertIndividualDetails] @name ='John DoeJr', @birthday = '2018-10-01'

SELECT * FROM a
SELECT * FROM b

Hope this helps.

Dealing with subtypes and supertypes in Database design , CREATE TABLE super ( xxx_id not null primary key , classifier int not null check (classifier = 1) , foreign key (classifier,xxx_id) references super (classifier In this case, you probably want to ensure you look at keeping critical rows as SQL Server has a little-implemented feature know as Sparse  Similarly, you can also delete a row in the vendor_groups table without updating or deleting the corresponding rows in the vendors table that results in orphaned rows in the vendors table. To enforce the link between data in the vendor_groups and vendors tables, you need to establish a foreign key in the vendors table.

You can do this without making database connection twice by making one stored procedure which combines the two stored procedure's input parameters as:

Reformed stored procedure:

ALTER PROCEDURE [dbo].[Stud_InsertNew]
    (@STUD_NAM VARCHAR(30),
     @STUD_EMAIL VARCHAR(50),
     @CAMP_NAM VARCHAR(50),
     @CAMP_ZIP INT,
     @STUD_TYP CHAR(5),
     // Parameters for other student
     @STUD_APT VARCHAR(5),
     @STUD_CITY CHAR(6),
     @STUD_STATE CHAR(2),
     @STUD_ZIP INT,
     @RPT_ATTM AS VARBINARY(4000) = NULL,
     @RPT_EYESCR AS VARBINARY(4000) = NULL,
     @DATE_LASTPASS DATE,
     @DATE_LASTVSP AS DATE = NULL)
AS
BEGIN
    DECLARE @OTHERCONSTANT NVARCHAR(MAX)

    SET @OTHERCONSTANT = 'Other' // You can set to a text that coming from C#

    SET NOCOUNT ON

    INSERT INTO dbo.Student(STUD_NAM, STUD_EMAIL, CAMP_NAM, CAMP_ZIP, STUD_TYP)
    VALUES (@STUD_NAM, @STUD_EMAIL, @CAMP_NAM, @CAMP_ZIP, @STUD_TYP);

    IF(@STUD_TYP = @OTHERCONSTANT)
    BEGIN
        // Need this line which returns the latest inserted Student Id
        DECLARE @STUD_ID  INT = NULL
        SET @STUD_ID = SCOPE_IDENTITY()

        INSERT INTO dbo.OtherStudents (STUD_ST, STUD_CITY, STUD_STATE, STUD_ZIP, RPT_ATTM, RPT_EYESCR,DATE_LASTPASS, DATE_LASTVSP)
        VALUES (@STUD_ST, @STUD_CITY, @STUD_STATE, @STUD_ZIP, @RPT_ATTM, @RPT_EYESCR, @DATE_LASTPASS, @DATE_LASTVSP)
    END
END

C# code:

using(var connection = new System.Data.SqlClient.SqlConnection(Helper.CnnVal("cis-laredoConnectionString"))) {
 connection.Open();
 var cmd = new SqlCommand("dbo.Stud_InsertNew", connection);
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.AddWithValue("@STUD_NAM", txtStdName.Text);
 cmd.Parameters.AddWithValue("@STUD_EMAIL", txtStdEmail.Text);
 cmd.Parameters.AddWithValue("@CAMP_NAM", ddlCamp.SelectedItem.ToString());
 cmd.Parameters.AddWithValue("@CAMP_ZIP", campzip);
 cmd.Parameters.AddWithValue("@STUD_TYP", "Other");

 cmd.Parameters.AddWithValue("@STUD_ST", txtStdStreet.Text);
 cmd.Parameters.AddWithValue("@STUD_APT", txtStdApt.Text);
 cmd.Parameters.AddWithValue("@STUD_CITY", txtStdCity.Text);
 cmd.Parameters.AddWithValue("@STUD_STATE", txtStdState.Text);
 cmd.Parameters.AddWithValue("@STUD_ZIP", StudentZip);
 cmd.Parameters.AddWithValue("@RPT_ATTM", fuAttend.FileBytes);
 cmd.Parameters.Add("@DATE_LASTPASS", SqlDbType.Date).Value = DateLastPass;

 cmd.ExecuteNonQuery();
 cmd.Dispose();

 if (connection.State == ConnectionState.Open) {
  connection.Close();
 }
}

Would it be considered as a bad practice to have multiple nullable , UQ1 is the "super key" used a foreign key on the subtype tables; Each subtype SQL developers and users may therefore be unclear about what a nullable products or even between different servers using the same product could give of simplicity and accuracy it is therefore better to leave nulls out than put them in. In Object Explorer, right-click the table that will be on the foreign-key side of the relationship and click Design. The table opens in Table Designer. From the Table Designer menu, click Relationships. In the Foreign-key Relationships dialog box, click Add.

Modeling Supertypes and Subtypes: Part 3, Because the model is now connected to SQL Server, we design in the ON DELETE CASCADE clause to the referencing table's foreign key definition. This snippet from the AFTER insert trigger above shows that the action  I have a supertype table called Student, and its subtype table called OtherStudents.I am wondering how to write a stored procedure that will insert the foreign key value into OtherStudents table every time a new record with a student type Other is inserted into Student.

Pro SQL Server 2005 Database Design and Optimization, FOREIGN KEY constraint, applying to CREATE TABLE statement, 560 foreign key constraints adding, 240 FOREIGN KEY constraints in SQL Server definition 139 functions basing constraints on, 283, 285 creating for interoperability, 570–​571 entities categorization relationships, 57 generic entity in subtypes purpose of,  In this case, the foreign key definition starts with CONSTRAINT, followed by the name of the foreign key, followed by FOREIGN KEY, followed by the column that the foreign key constraint will be applied to (inserted in parentheses). We then see the same REFERENCES clause that we saw in the previous example.

Defining a one-to-one relationship in SQL Server - sql-server, In the child table, the primary key also serves as the foreign key to the parent the subtype tables, making it part of the PK & FK, and put a CHECK constraint on​  Foreign key constraints are an integral part of SQL Server database design. These are used to maintain integrity among related data in different tables. While implementing update and delete operations on values in the parent table (referenced table with primary key) we have to consider the impact on related values in the child table.

Comments
  • Hi and welcome to SO. It is not clear to me what you are trying to accomplish. And images of tables are not helpful. Why? meta.stackoverflow.com/questions/285551/…
  • In [Stud_InsertNew] stored proc check for @STUD_TYP= 'other' then INSERT INTO OtherStudents
  • Thank you for the replies, I have updated the images to the DDL statements. Hope that would be better!
  • @NicoleWoo It would better if you explain your requirement well! Refer my previous comment, is that what you wanted?
  • @PrashantPimpale Sorry for the confusion, I am super new to Sql. What I am trying to do is each time a new record is added into Student table, the STUD_ID auto-generated will be retrieved and inserted in the foreign key column of the OtherStudents table. I did some research saying that OUTPUT inserted.STUD_ID might be helpful, but I am not sure how to do it. I am now trying the ExecuteScalar() in C#, but still getting errors saying 'the specified cast is not valid'. Hope these would help you understand my requirement!
  • It didn't give me error anymore. But it returns me 3 duplicated records in Student table each time I submit and no value returned to the OtherStudents table...did I mess something up? lol
  • I see...I forgot to change cmd to cmd2 in c# for the BusPassstudent insert. I will try it again.
  • this solution is flawed. Use scope_identity() or preferably OUTPUT clause to retrieve inserted Student Id
  • @MitchWheat yes, exactly that's why posted another answer, Is that fine or still need to improve?
  • Thank you for the refined code. I have changed to the SCOPE_IDENTITY() and also added where STUD_TYP = "Other".