If you want to force every child of every parent to have the same customer then there are two quick solutions that you could use to accomplish this.
The first, and my preference, would be to use a check constraint on the table like so:
-- define a function that performs the logic required
CREATE FUNCTION dbo.fn_CheckCustomersAreEqual(ParentID INT, CustomerID INT)
WHERE ID = ParentID AND CustomerID = CustomerID
), 0) AS BIT);
-- add a check constraint to the table using the function to provide the outcome
ALTER TABLE dbo.CustomerFolders
ADD CONSTRAINT CK_CheckCustomersAreEqual
CHECK (dbo.fn_CheckCustomersAreEqual(ParentID, CustomerID));
The second option would be to add a trigger to the table to enforce the requirements like so:
CREATE TRIGGER CheckCustomersAreEqual ON dbo.Folders
INSTEAD OF INSERT
-- check to see if the number of parents that meet the criteria
-- match the number of records we are inserting
IF (SELECT COUNT(*) FROM INSERTED AS F
JOIN dbo.CustomerFolders AS I ON I.ParentID = F.ID
AND I.CustomerID = F.CustomerID) <>
(SELECT COUNT(*) FROM INSERTED)
-- they don't match so raise an error
RAISERROR('Child customer does not match parent customer', 16, 1);
-- they match so insert
You can adjust the error handling and message returned to the client a bit better in the trigger but I personally prefer using the
CHECK CONSTRAINT version.
These are just two possibilities that I can come up with, there may be more and you may be able to optimize the function or trigger to perform better. You could also change the design of your database to accommodate this better if you wanted. I just wrote the example above off the top of my head so be aware that they are also untested.
I just noticed that the parent can be null (which makes sense) - but the example I gave above does not account for this. Make sure you account for this scenario in your end solution.