Add Composite Primary Key and Foreign Key Support to DDL Parser

[jeffrade]

Summary

The DDL parser currently only supports single-column primary keys and foreign keys. Tables with composite (multi-column) keys fail during EF Core migration generation.

Current Behavior

When parsing a table like:

CREATE TABLE [dbo].[parent_table](                                                                                                                                                                          
    [id] [bigint] NOT NULL,                                                                                                                                                                                 
    [secondary_id] [bigint] NOT NULL,                                                                                                                                                                       
    CONSTRAINT [PK_parent_table] PRIMARY KEY CLUSTERED ([id] ASC, [secondary_id] ASC)                                                                                                                       
)                                                                                                                                                                                                           
                                                                                                                                                                                                            
CREATE TABLE [dbo].[child_table](                                                                                                                                                                           
    [parent_id] [bigint] NOT NULL,                                                                                                                                                                          
    [parent_secondary_id] [bigint] NOT NULL,                                                                                                                                                                
    CONSTRAINT [FK_child_parent] FOREIGN KEY ([parent_id], [parent_secondary_id])                                                                                                                           
        REFERENCES [dbo].[parent_table] ([id], [secondary_id])                                                                                                                                              
)                                                                                                                                                                                                           
                                                                                                                                                                                                            
The DDL parser:                                                                                                                                                                                             
1. Only captures the first column of composite PKs                                                                                                                                                          
2. Only captures the first column of composite FKs                                                                                                                                                          
3. Generates incorrect EF Core relationships causing migration failures                                                                                                                                     
                                                                                                                                                                                                            
Error Message                                                                                                                                                                                               
                                                                                                                                                                                                            
Unable to create a 'DbContext' of type 'AppDbContext'. The exception 'The relationship from                                                                                                                 
'Child_table.Parent_table' to 'Parent_table' with foreign key properties {'parent_id' : long}                                                                                                               
cannot target the primary key {'id' : long, 'secondary_id' : long} because it is not compatible.'                                                                                                           
                                                                                                                                                                                                            
Expected Behavior                                                                                                                                                                                           
                                                                                                                                                                                                            
The DDL parser should:                                                                                                                                                                                      
1. Parse all columns in composite PRIMARY KEY constraints                                                                                                                                                   
2. Parse all columns in composite FOREIGN KEY constraints                                                                                                                                                   
3. Generate correct app.yaml with multi-column key definitions                                                                                                                                              
4. ModelGenerator should generate EF Core entities with proper composite key configuration                                                                                                                  
                                                                                                                                                                                                            
Files to Modify                                                                                                                                                                                             
                                                                                                                                                                                                            
DdlParser Project                                                                                                                                                                                           
                                                                                                                                                                                                            
- CreateTableVisitor.cs - Modify PK/FK constraint parsing to capture multiple columns                                                                                                                       
- TableMetadata.cs / ForeignKeyMetadata.cs - Change from single column to List<string> for key columns                                                                                                      
- YamlGenerator.cs - Generate YAML with composite key syntax                                                                                                                                                
                                                                                                                                                                                                            
ModelGenerator Project                                                                                                                                                                                      
                                                                                                                                                                                                            
- EntityGenerator.cs - Generate [Key] attributes or fluent API for composite PKs                                                                                                                            
- Generate proper navigation properties for composite FKs                                                                                                                                                   
                                                                                                                                                                                                            
DotNetWebApp.Models Project                                                                                                                                                                                 
                                                                                                                                                                                                            
- AppDictionary/Property.cs - May need KeyOrder or similar property                                                                                                                                         
- AppDictionary/Relationship.cs - Support multiple FK columns                                                                                                                                               
                                                                                                                                                                                                            
Workaround                                                                                                                                                                                                  
                                                                                                                                                                                                            
Tables with composite keys are currently commented out in sql/*.sql files. Re-enable after this feature is implemented.                                                                                     
                                                                                                                                                                                                            
Acceptance Criteria                                                                                                                                                                                         
                                                                                                                                                                                                            
- DDL parser correctly identifies composite primary keys                                                                                                                                                    
- DDL parser correctly identifies composite foreign keys                                                                                                                                                    
- Generated app.yaml represents composite keys accurately                                                                                                                                                   
- ModelGenerator produces valid EF Core entities with composite keys                                                                                                                                        
- make run-ddl-pipeline succeeds with composite key tables                                                                                                                                                  
- make migrate succeeds                                                                                                                                                                                     
- Unit tests cover composite key scenarios