none
Inserting into a parent when there are rows inserted into a child.

    Question

  • Is it possible to insert a record to the parent when a row inserted into the child for which the parent does not exists? I can do it with a trigger but it seems like an overkill on performance if the child table is a lot wider and the parent table has still a single column. The reason I need this is I have a partitioned table of lets say 500 (1 to 500) partitions currently, partition function is a smallint. Partitions are mapped one to one, even though partitioning is a range we are using exact match for our partitions on the smallint value. If somebody inserts a value of 600 which goes to the most right partition I need to detect the change and create a partition for value 600. I do not want to detect the change by scanning the whole partitioned index instead know if from the parent table that stores the PartitionId's. When people randomly insert from their queries, or do bulk inserts of large data I need to know the partitions that needs to be created.
    Thanks
    CREATE TABLE Parent (PartitionId smallint NOT NULL PRIMARY KEY CLUSTERED)
    CREATE TABLE Child (ChildID INT IDENTITY NOT NULL PRIMARY KEY CLUSTERED, PartitionIdsmallint NOT NULL, ChildName VARCHAR(256),
    FOREIGN KEY (ParentId) REFERENCES Parent(ParentID))
    INSERT Child(PartitionId, ChildName)VALUES(1,'ASFDASD')
    Friday, September 09, 2011 3:11 PM

All replies

  • You can not insert a record into the Child if the Parent Key does not exist. It has to be inserted first into the Parent and then Into the Child.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, September 09, 2011 3:20 PM
    Moderator
  • Is my only option is having a trigger on the child then?
    Friday, September 09, 2011 3:21 PM
  • What this trigger is going to do? For your original question: is it possible to insert into Child first, then into Parent - the answer is NO, only if there will be no relation between Parent and Child.
    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog
    Friday, September 09, 2011 3:24 PM
    Moderator
  • To Insert record in Child table with out inserting Parent is possible only when there is no relation with Primary Key and Foreign Key

     


    Thanks & Regards Prasad DVR
    Friday, September 09, 2011 3:34 PM
  • The instead of trigger will scan the data and create missing parent keys and then insert into the child, I just don't like using triggers in general though. The foreign key has delete and update cascade (no insert cascade). I want to detect if people insert records into the table that the parent does not exists without scanning the child against parent and not open up the complexity of the data structure to them. The parent is holding the acceptable values of the partition values which the user don't have to know, I just want to know if there is a new value exists in the child that does not exists in parent without scanning the table).
    Friday, September 09, 2011 3:38 PM