March 22, 2012 at 8:43 am
I'm trying to create a relationship table that records which users can act as proxies for other users. I'm attempting to use DRI to formulate the rules of these relationships, but I'm running into issues.
- Cascade delete will only work on one side of the proxy relationship. I already knew this would happen, but is there a work-around besides using application code? Due to the varied sources of our user data, we often use SSMS to delete and reload users in an ad hoc fashion. Cascade delete is a much-needed feature.
- Exactly one proxy relationship per user who can be a proxy should be primary. My initial attempt to do this was a bit field in the user_proxies table. But that can't have uniqueness enforced and it doesn't provide a replacement primary proxy if the primary is deleted. I've thus added a proxy_priority column with a unique index. Does this look like a good solution?
The tables below are skeleton approximations of the issue. The actual Users table has more columns and more tables that reference it.
BEGIN TRY
DROP TABLE User_Proxies
DROP TABLE Users
END TRY
BEGIN CATCH
END CATCH
CREATE TABLE Users (
person_id INT NOT NULL PRIMARY KEY,
person_name VARCHAR(50) NOT NULL UNIQUE
)
CREATE TABLE User_Proxies
(
proxy_person_id INT NOT NULL
REFERENCES Users(person_id) ON DELETE CASCADE,
proxy_for_person_id INT NOT NULL
REFERENCES Users(person_id), --ON DELETE CASCADE,
proxy_priority INT NOT NULL,
PRIMARY KEY (proxy_person_id, proxy_for_person_id),
UNIQUE (proxy_person_id, proxy_priority),
CHECK (proxy_for_person_id <> proxy_person_id)
)
March 22, 2012 at 8:57 am
You should probably just implement this as code in a trigger.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply