text: Q:
  
  How to define recursive foreign key on 2 columns
  
  I'm writing a small app to manage metadata for menus. A Menu is attached to an App, and is also (optionally) attached to a another Menu (therefore defining a submenu). So the wiring goes like this: attach Menu.AppId to App.Id, and then Menu.ParentId to Menu.Id to define submenus. 
  But this can let me insert incoherent data:
  INSERT INTO Menu (Id, ParentId, AppId, Desc) values (1, NULL, 25, 'Top Menu')
  INSERT INTO Menu (Id, ParentId, AppId, Desc) values (2, 1, 36, 'Sub Menu')
  
  Here I just stated that Sub Menu of App #36 should be under Top Menu of App #25 (another App).
  Is there a way I can define a constraint to make sure that when I insert Sub Menu as a child of Top Menu, the App has to be #25 (triggers are not an option)?
  (Of course I will manage this in the user interface, but I'm also looking for a way to secure the model).
  Thank you,
  
  A:
  
  You can use a foreign key constraint
  First constraint (Id, AppId) to be unique (it is obvious if Id is the PK but it is needed to check the following FK constraint):
  alter table Menu add constraint unique_app unique (Id, AppId)
  
  Then constraint a child to have the same AppId :
  alter table Menu add constraint fk_same_parent_app
  foreign key (ParentId, AppId) references Menu(Id, AppId)
  
  Edit: If you need to change the AppId, it must be done at once for every menu in the hierarchy.
  This can be done with a recursive query using a CTE :
  with AMenu(Id, ParentId)
  as (
    -- start with a root
    select Id, ParentId
    from Menu where Id = <id_of_a_root>
  union all 
    -- recursively add children
    select m.Id, m.ParentId
    from Menu m 
    join Amenu am on m.ParentId = am.Id
  )
  update m
  set AppId = <some_value>
  from Menu m 
  join AMenu am 
  on m.Id = am.Id
  
  