none
How best to populate the foreign key of a child table during insert?

    Question

  • ParentTable has

    PTID int Identity(1,1),  [primary key]

    ParentName varchar(50)

    And ChildTable has

    CTID int Identity(1,1), [primary key],

    PTID int,                     [foreign key]

    ChildName varchar(50)

    When I insert a child for PTID = 1, I want the PTID in the child table to contain the PTID of the parent (ie: 1).

    I can think of different ways to do this but what is the best practice?

    Thanks,

    Bill

    Wednesday, June 20, 2012 9:26 AM

Answers

  • Bill

    You need first to insert that ID into a Parent table and then by using trigger or stored procedure to insert the data into a Child table

    CREATE TABLE GF --GRANDFATHERS
    (
     [ID] INT NOT NULL PRIMARY KEY,
     [NAME]CHAR(1) NOT NULL 
    )
    INSERT INTO GF VALUES (1,'A')
    INSERT INTO GF VALUES (2,'B')
    INSERT INTO GF VALUES (3,'C')

    CREATE TABLE F --FATHERS
    (
     [ID] INT NOT NULL PRIMARY KEY,
     GFID INT NOT NULL FOREIGN KEY REFERENCES GF([ID])ON DELETE CASCADE ON UPDATE CASCADE,
     [NAME]CHAR(2) NOT NULL 
    )
     
    INSERT INTO F VALUES (1,1,'AA')
    INSERT INTO F VALUES (2,1,'AA')
    INSERT INTO F VALUES (3,2,'BB')
    INSERT INTO F VALUES (4,2,'BB')
    INSERT INTO F VALUES (5,2,'BB')
    INSERT INTO F VALUES (6,3,'CC')

    CREATE TABLE C --CHILD
    (
     [ID] INT NOT NULL PRIMARY KEY,
     FID INT NOT NULL FOREIGN KEY REFERENCES F([ID])ON DELETE CASCADE ON UPDATE CASCADE,
     [NAME]CHAR(3) NOT NULL 
    )

    INSERT INTO C VALUES (1,1,'AAA')
    INSERT INTO C VALUES (2,1,'AAA')
    INSERT INTO C VALUES (3,2,'AAA')
    INSERT INTO C VALUES (4,3,'BBB')
    INSERT INTO C VALUES (5,4,'BBB')
    INSERT INTO C VALUES (6,4,'BBB')
    INSERT INTO C VALUES (7,5,'BBB')
    INSERT INTO C VALUES (8,5,'BBB')
    INSERT INTO C VALUES (9,5,'BBB')
    INSERT INTO C VALUES (10,6,'CCC')
    INSERT INTO C VALUES (11,6,'CCC')
    INSERT INTO C VALUES (12,6,'CCC')


    SELECT * FROM GF
    SELECT * FROM F
    SELECT * FROM C


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Wednesday, June 20, 2012 9:49 AM

All replies

  • You have to populate the parentTable then the FK in the child. This is the order for your input!

    Many Thanks & Best Regards, Hua Min

    Wednesday, June 20, 2012 9:40 AM
  • Bill

    You need first to insert that ID into a Parent table and then by using trigger or stored procedure to insert the data into a Child table

    CREATE TABLE GF --GRANDFATHERS
    (
     [ID] INT NOT NULL PRIMARY KEY,
     [NAME]CHAR(1) NOT NULL 
    )
    INSERT INTO GF VALUES (1,'A')
    INSERT INTO GF VALUES (2,'B')
    INSERT INTO GF VALUES (3,'C')

    CREATE TABLE F --FATHERS
    (
     [ID] INT NOT NULL PRIMARY KEY,
     GFID INT NOT NULL FOREIGN KEY REFERENCES GF([ID])ON DELETE CASCADE ON UPDATE CASCADE,
     [NAME]CHAR(2) NOT NULL 
    )
     
    INSERT INTO F VALUES (1,1,'AA')
    INSERT INTO F VALUES (2,1,'AA')
    INSERT INTO F VALUES (3,2,'BB')
    INSERT INTO F VALUES (4,2,'BB')
    INSERT INTO F VALUES (5,2,'BB')
    INSERT INTO F VALUES (6,3,'CC')

    CREATE TABLE C --CHILD
    (
     [ID] INT NOT NULL PRIMARY KEY,
     FID INT NOT NULL FOREIGN KEY REFERENCES F([ID])ON DELETE CASCADE ON UPDATE CASCADE,
     [NAME]CHAR(3) NOT NULL 
    )

    INSERT INTO C VALUES (1,1,'AAA')
    INSERT INTO C VALUES (2,1,'AAA')
    INSERT INTO C VALUES (3,2,'AAA')
    INSERT INTO C VALUES (4,3,'BBB')
    INSERT INTO C VALUES (5,4,'BBB')
    INSERT INTO C VALUES (6,4,'BBB')
    INSERT INTO C VALUES (7,5,'BBB')
    INSERT INTO C VALUES (8,5,'BBB')
    INSERT INTO C VALUES (9,5,'BBB')
    INSERT INTO C VALUES (10,6,'CCC')
    INSERT INTO C VALUES (11,6,'CCC')
    INSERT INTO C VALUES (12,6,'CCC')


    SELECT * FROM GF
    SELECT * FROM F
    SELECT * FROM C


    Best Regards,Uri Dimant SQL Server MVP,http://sqlblog.com/blogs/uri_dimant/

    Wednesday, June 20, 2012 9:49 AM
  • first populate parent and insert from parent to child.


    SQL Champ
    Database Consultants NY

    Wednesday, June 20, 2012 11:41 AM
  • Take a look at the sample stored procedure that shows how it's done:

    How to insert information into multiple related tables and return ID using SQLDataSource

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Wednesday, June 20, 2012 5:23 PM
  • Sorry I didn't respond to this (and the other replies) before; I did not receive the notification that I had received a reply.

    All of the replies were helpful. Many thanks.

    Friday, June 22, 2012 1:51 PM

  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    What you did post is garbage. Why did you use IDENTITY? What does the count of physical disk insertion attempts? Why did you put “table” in a table name? I see you used the magical fifty character string instead of the USPS 35, etc. Here is the DDL that you might have posted if you had read the first chapter of a book on data modeling. 

    CREATE TABLE Parents
    (parent_physical_insertion_cnt INTEGER IDENTITY(1,1) PRIMARY KEY,
     parent_Name VARCHAR(35) NOT NULL

    CREATE TABLE Children
    (child_physical_insertion_cnt INTEGER IDENTITY(1,1) PRIMARY KEY,
     parent_physical_insertion_cnt INTEGER NOT NULL
       REFERENCES Parents,
     child_name VARCHAR(35) NOT NULL);

    This is the worst way to model a hierarchy. You are mimicking poitner chazins in SQL! 

    >> When I insert a child for parent_physical_insertion_cnt = 1, I want the parent_physical_insertion_cnt in the Children table to contain the parent_physical_insertion_cnt of the Parents (ie: 1).<<

    Parents are a strong entity; the children are a weak entity. A weak entity exists only because a strong entity exists. What is the problem? Now you need to get real keys .. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, June 22, 2012 6:26 PM