0

Is there a pattern in SQL where a child row inherits empty (null?) values from a parent?

E.g., given the following 'chickens' table (sorry):

id parent_id name flys noise weight egg_color special_feathers
1 chicken true clucks 5lbs white false
2 1 maran brown
3 1 silkie false squeeks 4lbs
4 3 silkie frizzle true

I want to easily be able to do:

select * from chickens where egg_color = 'white'

and have it return all the chickens except for the maran.

I don't want to explicitly set the values in child rows because I want to be able to update the parent data and have it updated everywhere.

I think this can be accomplished via a recursive view, however I am curious if there are any other solutions.

I am also not sure how to handle explicitly empty states other than with an empty string (which won't work for non-string fields). This might not be something we need to accommodate but want to see if anyone has ideas.

3
  • E.g., given the following 'chickens' table Provide this as CREATE TABLE + INSERT INTO. I am curious if there are any other solutions. You may insert the values inherited from the parent row with according trigger during the insertion, of course. But you will lose the opportunity to update your data when the parent property is altered, because you cannot determine does the value was set explicitly or it was inherited. So the solution with recursive CTE is reasonable.
    – Akina
    Mar 21 at 5:48
  • 2
    Do you need to optimize for query speed or data modification speed? You can choose, but you cannot have both. Mar 21 at 9:40
  • I'd say query speed. We'll only be modifying one record at a time. I also think the table won't be huge (maybe a few thousand records max). Mar 21 at 12:19

1 Answer 1

0

The most clear way to implement this is to set up a trigger that, BEFORE INSERT (NOT UPDATE :)), when a new field is NULL, we look up the parent's attributes and set the new column value with those defaults. If you update and migrate away your null values oince, you should never have to do a recursive dive again. That is, instead of having a null egg color, immediately inherit the parent and then change it accordingly.

That may not be desirable.

Instead, you can indeed try a recursive query like so:

WITH RECURSIVE chickenhierarchy AS (
    SELECT c.ID
         , c.parent_id
         , c.name
         , c.egg_color
         , c.parent_id AS root_parent_id
    FROM chickens c
    WHERE c.parent_id IS NULL
    
    UNION ALL
    
    SELECT c.ID, c.parent_id, c.name, 
           COALESCE(c.egg_color, ph.egg_color) AS egg_color, 
           ph.root_parent_id
    FROM chickens c
    JOIN chickenhierarchy ph ON c.parent_id = ph.ID
)
SELECT ID, parent_id, name, egg_color
FROM chickenhierarchy;
id parent_id name egg_color
1 NULL daddy white
2 1 randy brown
3 1 george white
4 3 steve white
5 4 pete white

The swapping of NULLs is done by the COALESCE(c.egg_color, ph.egg_color) AS egg_color, call.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.