0

I have modelled a database (CMS for personal challenge), 14 tables with many foreign key constraints (specifically 1 -> 0,1 relations) and I will fill the tables with default values.

The challenge for me is to detect some kind of methodic in what sequence to fill the tables, to avoid errors when inserting.

Is there some distinct method for doing this? I keep getting some errors in the initialization script because of wrong sequence!

Short description: A page can hold a content entity, but not necessarily. A content can hold one or more sections, but not necessarily. Both page and content can exist as standalone. A page can be part of a community in a meny...etc.

I am using entity framework as my orm and I have modified edmx slightly, so I don't get a reference back when I do a one->many relation, f.ex.

Here is my model and relations:

enter image description here

Here is my initialization script of just the user and some other tables just to get going with the coding of login procedure:

USE [WebMateCMS]
GO

INSERT INTO [Category]
VALUES ('93dad9c7-6b92-4f3b-a8a9-5a0c330290fc',
        'Icon Flag',
        'Icon Flags for the different languages',
        '2016-03-17T08:32:55',  --IsCreated
         '2016-03-17T08:32:55',  --IsValidFrom
         '2016-03-17T08:32:55',  --IsActivated
         null,  --IsDeleted
         null,  --IsEdited
         null,  --IsExpired
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- CreatedByNameID/System User account
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- ValidFromByNameID/System User account
         null,  --EditedByNameID
         null,  --ExpiredByNameID
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- ActivatedByNameID/System User account
         null)  --DeletedByNameID

INSERT INTO [Image]
VALUES ('2f426aa0-752b-4032-a6d0-83d84eb4d0e3',
        'English-Flag',
        'English Icon Flag',
        null,  --ImageTypeID
        null,  --IsThumbnailOfID
        'System string filler',  --RecordedBy
        'System string filler',  --Copyright
        'System string filler',  --Meta
        'System string filler',  --OriginalSizePx
        'System string filler',  --Base64Original
        'System string filler',  --BinaryOriginal
        'System string filler',  --FilePath
        null,  --CategoryID
        '2016-03-17T08:32:55',  --IsCreated
         '2016-03-17T08:32:55',  --IsValidFrom
         '2016-03-17T08:32:55',  --IsActivated
         null,  --IsDeleted
         null,  --IsEdited
         null,  --IsExpired
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- CreatedByNameID/System User account
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- ValidFromByNameID/System User account
         null,  --EditedByNameID
         null,  --ExpiredByNameID
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- ActivatedByNameID/System User account
         null)  --DeletedByNameID


INSERT INTO [Image]
VALUES ('57972deb-6a77-43f8-8b96-a6617a1c7efa',
        'Danish-Flag',
        'Danish Icon Flag',
        null,  --ImageTypeID
        null,  --IsThumbnailOfID
        'System string filler',  --RecordedBy
        'System string filler',  --Copyright
        'System string filler',  --Meta
        'System string filler',  --OriginalSizePx
        'System string filler',  --Base64Original
        'System string filler',  --BinaryOriginal
        'System string filler',  --FilePath
        null,  --CategoryID
        '2016-03-17T08:32:55',  --IsCreated
         '2016-03-17T08:32:55',  --IsValidFrom
         '2016-03-17T08:32:55',  --IsActivated
         null,  --IsDeleted
         null,  --IsEdited
         null,  --IsExpired
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- CreatedByNameID/System User account
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- ValidFromByNameID/System User account
         null,  --EditedByNameID
         null,  --ExpiredByNameID
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- ActivatedByNameID/System User account
         null)  --DeletedByNameID



INSERT INTO [Language]
VALUES ('2f426aa0-752b-4032-a6d0-83d84eb4d0e3',
        'English',
        'English language',
        'en',
        null,
        '2016-03-17T08:32:55',
        '2016-03-17T08:32:55',
        '2016-03-17T08:32:55',
        null,
        null,
        null,
        '65d6a29b-30b4-4d42-9b20-450182f393d2', -- System User account
        '65d6a29b-30b4-4d42-9b20-450182f393d2', -- System User account
        null,
        null,
        '65d6a29b-30b4-4d42-9b20-450182f393d2', -- System User account
        null)


INSERT INTO [Language]
VALUES ('57972deb-6a77-43f8-8b96-a6617a1c7efa',
        'Dansk',
        'Dansk sprog',
        'dk',
        null,
        '2016-03-17T08:32:55',
        '2016-03-17T08:32:55',
        '2016-03-17T08:32:55',
        null,
        null,
        null,
        '65d6a29b-30b4-4d42-9b20-450182f393d2', -- System User account
        '65d6a29b-30b4-4d42-9b20-450182f393d2', -- System User account
        null,
        null,
        '65d6a29b-30b4-4d42-9b20-450182f393d2', -- System User account
        null)


INSERT INTO [UsedPassword]
VALUES ('5b8c3e64-d069-41c6-9554-0cf9c7da9fb7',
         'Used passwords for administrator@webmatecms.com',
         'Used passwords for administrator@webmatecms.com',
         null,  --UsedPasswords
         null,  --UsedSalts
         '2016-03-17T08:32:55',  --IsCreated
         '2016-03-17T08:32:55',  --IsValidFrom
         '2016-03-17T08:32:55',  --IsActivated
         null,  --IsDeleted
         null,  --IsEdited
         null,  --IsExpired
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- CreatedByNameID/System User account
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- ValidFromByNameID/System User account
         null,  --EditedByNameID
         null,  --ExpiredByNameID
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- ActivatedByNameID/System User account
         null)  --DeletedByNameID


INSERT INTO [UsedPassword]
VALUES ('bffec845-a4fb-477a-bfcc-b7dbb97224cf',
         'Used passwords for user@webmatecms.com',
         'Used passwords for user@webmatecms.com',
         null,  --UsedPasswords
         null,  --UsedSalts
         '2016-03-17T08:32:55',  --IsCreated
         '2016-03-17T08:32:55',  --IsValidFrom
         '2016-03-17T08:32:55',  --IsActivated
         null,  --IsDeleted
         null,  --IsEdited
         null,  --IsExpired
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- CreatedByNameID/System User account
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- ValidFromByNameID/System User account
         null,  --EditedByNameID
         null,  --ExpiredByNameID
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- ActivatedByNameID/System User account
         null)  --DeletedByNameID



INSERT INTO [Credential]
VALUES ('5b8c3e64-d069-41c6-9554-0cf9c7da9fb7',
         'administrator@webmatecms.com',
         'Credential for administrator@webmatecms.com',
         'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',  --PassWord
         'bbbbbbbbb',  --Salt
         null,  --Token
         null,  --Certificate
         null, --Company
         null,  --ShowClass
         null,  --RoleID
         null,  --UsedPassWordsID
         '2016-03-17T08:32:55',  --IsCreated
         '2016-03-17T08:32:55',  --IsValidFrom
         '2016-03-17T08:32:55',  --IsActivated
         null,  --IsDeleted
         null,  --IsEdited
         null,  --IsExpired
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- CreatedByNameID/System User account
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- ValidFromByNameID/System User account
         null,  --EditedByNameID
         null,  --ExpiredByNameID
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- ActivatedByNameID/System User account
         null)  --DeletedByNameID


INSERT INTO [Credential]
VALUES ('bffec845-a4fb-477a-bfcc-b7dbb97224cf',
         'user@webmatecms.com',
         'Credential for user@webmatecms.com',
         'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa',  --PassWord
         'bbbbbbbbb',  --Salt
         null,  --Token
         null,  --Certificate
         null, --Company
         null,  --ShowClass
         null,  --RoleID
         null,  --UsedPassWordsID
         '2016-03-17T08:32:55',  --IsCreated
         '2016-03-17T08:32:55',  --IsValidFrom
         '2016-03-17T08:32:55',  --IsActivated
         null,  --IsDeleted
         null,  --IsEdited
         null,  --IsExpired
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- CreatedByNameID/System User account
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- ValidFromByNameID/System User account
         null,  --EditedByNameID
         null,  --ExpiredByNameID
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- ActivatedByNameID/System User account
         null)  --DeletedByNameID



INSERT INTO [User]
VALUES ('5b8c3e64-d069-41c6-9554-0cf9c7da9fb7',
         'Administrator',
         'User account for the administrator',
         'User',
         'RootUser',
         'administrator@webmatecms.com',
         null,  --MobilePhoneNo
         null,  --ProfileImageID
         null,
         'RootUser',
         '5b8c3e64-d069-41c6-9554-0cf9c7da9fb7',
         null,
         null,
         null,
         null,
         null,
         null,
         null,
         1,
         1,
         1,
         '2f426aa0-752b-4032-a6d0-83d84eb4d0e3', -- languageid
         0,  --IsBlockedForSecurityReason
         '2016-03-17T08:32:55',
         '2016-03-17T08:32:55',
         '2016-03-17T08:32:55',
         null,
         null,
         null,
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- System User account
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- System User account
         null,
         null,
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- System User account
         null)

INSERT INTO [User]
VALUES ('bffec845-a4fb-477a-bfcc-b7dbb97224cf',  --ID  
         'User',  --Name
         'User account for the ordinary user',  --Description
         'User',  --FirstName
         'OrdinaryUser',  --LastName
         'user@webmatecms.com',  --Email
         null, --MobilePhoneNo
         null,  --ProfileImageID
         null,  --ProfileThumbnailImageID
         'OrdinaryUser',  --UserName
         'bffec845-a4fb-477a-bfcc-b7dbb97224cf',  --CredentialID
         null,  --LastLoginAttempt
         null,  --LastActive
         null,  --SequentialLoginAttempts
         null,  --TooManyFaultyLoginAttempts
         null,  --SessionID
         null,  --PreferredLoginMethod
         null,  --PreferredIpAdr
         1,  --CanComment
         1,  --ReceiveNewsMail
         1,  --ReceiveNewContentNotification
         '57972deb-6a77-43f8-8b96-a6617a1c7efa', -- languageid
         0,  --IsBlockedForSecurityReason
         '2016-03-17T08:32:55',  --IsCreated
         '2016-03-17T08:32:55',  --IsValidFrom
         '2016-03-17T08:32:55',  --IsActivated
         null,  --IsDeleted
         null,  --IsEdited
         null,  --IsExpired
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- CreatedByNameID/System User account
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- ValidFromByNameID/System User account
         null,  --EditedByNameID
         null,  --ExpiredByNameID
         '65d6a29b-30b4-4d42-9b20-450182f393d2', -- ActivatedByNameID/System User account
         null)  --DeletedByNameID

This script inserts all except for the two last inserts (user) because it generates error:

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 210
The INSERT statement conflicted with the FOREIGN KEY constraint "User_ImageID_ZeroOrOne". The conflict occurred in database "WebMateCMS", table "dbo.Image", column 'ID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 247
The INSERT statement conflicted with the FOREIGN KEY constraint "User_ImageID_ZeroOrOne". The conflict occurred in database "WebMateCMS", table "dbo.Image", column 'ID'.
The statement has been terminated.

Here are my foreignkey constraints;

--Adding Foreign Key constraints--


--Page--

ALTER TABLE Page 
ADD CONSTRAINT Page_LanguageID_ZeroOrOne FOREIGN KEY (ID) REFERENCES [Language](ID)
ALTER TABLE Page 
ADD CONSTRAINT Page_ImageID_ZeroOrOne FOREIGN KEY (ID) REFERENCES [Image](ID)
ALTER TABLE Page 
ADD CONSTRAINT Page_MenuID_ZeroOrOne FOREIGN KEY (ID) REFERENCES [Menu](ID)



--Content--

ALTER TABLE [Content] 
ADD CONSTRAINT Content_PageID_ZeroOrOne FOREIGN KEY (ID) REFERENCES [Page](ID)
ALTER TABLE [Content] 
ADD CONSTRAINT Content_DocumentTypeID_ZeroOrOne FOREIGN KEY (ID) REFERENCES [Type](ID)
ALTER TABLE [Content] 
ADD CONSTRAINT Content_IsLockedByNameID_Many FOREIGN KEY (IsLockedByNameID) REFERENCES [User](ID)
ALTER TABLE [Content] 
ADD CONSTRAINT Content_WrittenByNameID_Many FOREIGN KEY (WrittenByNameID) REFERENCES [User](ID)
ALTER TABLE [Content] 
ADD CONSTRAINT Content_LanguageID_ZeroOrOne FOREIGN KEY (ID) REFERENCES [Language](ID)
ALTER TABLE [Content] 
ADD CONSTRAINT Content_ImageID_ZeroOrOne FOREIGN KEY (ID) REFERENCES [Image](ID)


--Section--
ALTER TABLE [Section] 
ADD CONSTRAINT Section_ContentID_Many FOREIGN KEY (ContentID) REFERENCES [Content](ID)
ALTER TABLE [Section] 
ADD CONSTRAINT Section_ImageID_ZeroOrOne FOREIGN KEY (ID) REFERENCES [Image](ID)
ALTER TABLE [Section] 
ADD CONSTRAINT Section_IsLockedByNameID_ZeroOrOne FOREIGN KEY (ID) REFERENCES [User](ID)



--Image--
ALTER TABLE [Image] 
ADD CONSTRAINT Image_CategoryID_Many FOREIGN KEY (CategoryID) REFERENCES [Category](ID)


--Menu--
ALTER TABLE Menu 
ADD CONSTRAINT Menu_LanguageID_ZeroOrOne FOREIGN KEY (ID) REFERENCES [Language](ID)


--User--
ALTER TABLE [User] 
ADD CONSTRAINT User_LanguageID_Many FOREIGN KEY (LanguageID) REFERENCES [Language](ID)
ALTER TABLE [User] 
ADD CONSTRAINT User_ImageID_ZeroOrOne FOREIGN KEY (ID) REFERENCES [Image](ID)
ALTER TABLE [User] 
ADD CONSTRAINT User_CredentialID_ZeroOrOne FOREIGN KEY (ID) REFERENCES [Credential](ID)

--Credentials--
ALTER TABLE [Credential] 
ADD CONSTRAINT Credential_RoleID_Many FOREIGN KEY (RoleID) REFERENCES [Role](ID)
ALTER TABLE [Credential] 
ADD CONSTRAINT Credential_UsedPasswordsID_ZeroOrOne FOREIGN KEY (ID) REFERENCES [UsedPassword](ID)

--Role--
ALTER TABLE [Role] 
ADD CONSTRAINT Role_PermissionID_Many FOREIGN KEY (ID) REFERENCES [Permission](ID)


--Language--
ALTER TABLE [Language] 
ADD CONSTRAINT Language_IconID_ZeroOrOne FOREIGN KEY (ID) REFERENCES [Image](ID)

--Type--
ALTER TABLE Type 
ADD CONSTRAINT Type_CategoryID_Many FOREIGN KEY (CategoryID) REFERENCES [Category](ID)
ALTER TABLE Type 
ADD CONSTRAINT Type_TemplateID_Many FOREIGN KEY (TemplateID) REFERENCES [Template](ID)
quadroid
  • 8,444
  • 6
  • 49
  • 82
Finn Christensen
  • 91
  • 1
  • 10
  • Sample data, desired results, and a simplified script would really help explain what you are trying to do. – Gordon Linoff Aug 25 '16 at 08:15
  • In general you have to start from the most "internal" entity, the one that isn't referenced by other entities (e.g. A), then going up and inserting only entities (B, C) referencing only entities you have just inserted (A), and so on so forth. – Wallkan Aug 25 '16 at 08:21
  • It could be tricky. Suppose AB is cross-reference for A an B and the input steam contains pairs (a1,b1) (a1,b2) ... The script wich reads the stream and populates A,B and AB should check and conditionally insert data in all 3 tables. Can you be more specific on DB schema and inputs? – Serg Aug 25 '16 at 09:04

1 Answers1

0

Visualization helps a lot. Use some tool that can show you relations and can traverse through parent-child relations.

Simply find the "topmost" table (root), make an insert and go to it;s children.

Boris Schegolev
  • 3,601
  • 5
  • 21
  • 34