SQL Server Performance Forum – Threads Archive

Referential integrity with Partitioned Views

I have a scenario like this: I have an application that runs on my SQL Server and is used by two different types of users. I have set up partitioned views on all of my tables using the user_type as the partitioning column, so records inserted by one type of user go to one partition, and records inserted by the other go to another partition. Both types of users read directly from the UNION ALL view, so they can see one another’s information. My question has to do with maintaining referential integrity in this scenario. Lets say that user of type 1 inserts a record into the PARENT view (just for example’s sake), which is actually inserted into the PARENT table on server 1, via the view. Next, a user of type 2 inserts a record into the CHILD view, which gets inserted into the CHILD table on server 2, via the view. Let’s assume that the CHILD table has a column ‘parent_id’. I’d like for that to be a foreign key that references the parent_id column in PARENT. Unfortunately, with a partitioned view, since these records might reside in different tables on different servers, I don’t know how I can maintain referential integrity between PARENT and CHILD. Any tips?

---

]]>