CODEWITHSUNDEEP
sqlsqlite
147pm
147pm

Reputation: 2239

sqlite many-to-many + adjacency list

I'm using sqlite and want to have a many-to-many table with a parent column for adjacency list capability. Such as:

CREATE TABLE YDelta                                                                                                                                                    
(                                                                                                                                                                      
    YDeltaThingId NCHAR(8) NOT NULL,                                                                                                                                   
    YDeltaTS TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL, -- delta timestamp                                                                                          
    YDeltaStatus NCHAR(2) NOT NULL, -- 2-letter code for delta status                                                                                                  
    YDeltaStaffId NCHAR(10) NOT NULL, -- staff member                                                                                                                  
    YDeltaDesc TEXT NOT NULL,                                                                                                                                          
    YDeltaParentId ????                                                                                                                                                    
    PRIMARY KEY (YThingsId, YDeltaTS),                                                                                                                                 
    FOREIGN KEY (YDeltaThingId) REFERENCES YThings(YThingsId),                                                                                                         
    FOREIGN KEY (YDeltaStaffId) REFERENCES YStaff(YStaffId)                                                                                                            
);

. . . but I'm not sure how to build the YDeltaParentId. It should, of course, be a repeat of the primary key which is a composite of the YDeltaThingId NCHAR(8) and the YDeltaTS TIMESTAMP columns. (Following this adjacency example: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/)

Upvotes: 0

Views: 309

Answers (1)

CL.
CL.

Reputation: 180020

If the parent's key has two columns, you also need two columns in the child:

CREATE TABLE YDelta (
    ...
    YDeltaParentThingId NCHAR(8),
    YDeltaParentTS TIMESTAMP,
    ...
    PRIMARY KEY (YThingsId, YDeltaTS),
    FOREIGN KEY (YDeltaParentThingId, YDeltaParentTS)
              REFERENCES YDelta(YThingsId, YDeltaTS),
    ...
)

Upvotes: 2

Related Questions