Restrict a bit field to true in only one child record at a time

Question

I have a Person table, each of who's records belongs to a parent record from the Company table.

One Person is designated as "Organizer" for their parent Company. Initially I handled this by having a recursive reference from the Company table identifying the Person record that was it's "Organizer" - but the software I'm using to build my application layer falls over - it can't handle recursive references.

I've changed tack, and have added a bit field to the Person table to identify whether the person is an "Organizer" or not, but neet to ensure that there is only one "Organizer" for each Company record. If I use an AFTER UPDATE trigger on the Person table, an update on Person triggers an update on Person - obviously I want to avoid recursive triggers.

How can I ensure that there is only ever one Person marked as the "Organizer" for it's parent Company?

+-----------+---------+---------+-----------+                             +-----------+---------+---------+-----------+
| FirstName | Surname | Company | Organizer |                             | FirstName | Surname | Company | Organizer |
+-----------+---------+---------+-----------+                             +-----------+---------+---------+-----------+
| John      | Smith   |       1 | True      |                             | John      | Smith   |       1 | True      |
| Mike      | Jones   |       1 | NULL      |                             | Mike      | Jones   |       1 | NULL      |
| Fred      | Green   |       1 | NULL      |                             | Fred      | Green   |       1 | NULL      |
| James     | McMahon |       2 | NULL      |                             | James     | McMahon |       2 | NULL      |
| Philip    | Stills  |       2 | NULL      | Making Philip organizer ==> | Philip    | Stills  |       2 | True      |
| Hector    | Berlioz |       2 | True      | 'triggers' this change  ==> | Hector    | Berlioz |       2 | NULL      |
+-----------+---------+---------+-----------+                             +-----------+---------+---------+-----------+

Unique index with where clause? For example, `create unique index ix_your_index_name on Person (Company, Organizer) where Organizer is not null`. — Max Zolotenko, Jan 30 '20 at 07:10
Probably not what you want, but have you thought about designating a `Organizer` Field in the Company table instead? That way you can store the information of who is an organizer there and fetch it via joins if necessary. Also, does your person table have a unique identifier number (something like ID?) if not, it should have that. — Magisch, Jan 30 '20 at 08:34
A unique index as already mentioned or a constraint with a udf like this for example: `ALTER TABLE [dbo].[Person] WITH CHECK ADD CONSTRAINT [chkUniqueOrganizer] CHECK (([dbo].[CheckUniqueOrganizer](Company)=0))` — Robin, Jan 30 '20 at 09:42
@Magisch yeah, there's an ID for each table, just didn't include it for simplicity of the example. Your suggestion is exactly what I originally did, but my client side software can't handle recursive references - that would essentially make the Company a child of one of it's own child records. Fine in SQL Server, but not in the application layer. — Isaac Reefman, Jan 31 '20 at 03:47
@user1711390 I don't quite follow... Perhaps if you fleshed that idea out in an answer? — Isaac Reefman, Jan 31 '20 at 03:49
i missed the part that you also want to remove the old organizer. i think thats not possible with my suggestion. But you can prevent recursion in triggers:[link](https://stackoverflow.com/questions/1529412/how-do-i-prevent-a-database-trigger-from-recursing/47074365#47074365) — Robin, Jan 31 '20 at 05:29

score 0 · Accepted Answer · answered May 07 '20 at 10:37

So seeing as no-one has given an answer here, I'll post what I eventually did:

Created a separate table called Organizer, with only two fields:

CREATE TABLE Organizer (
    Company int NOT NULL UNIQUE,
    Person int NOT NULL,
    CONSTRAINT FK_Organizer_Company FOREIGN KEY (Company) REFERENCES Company(ID) ON DELETE CASCADE,
    CONSTRAINT FK_Organizer_Person FOREIGN KEY (Person) REFERENCES Person(ID) ON DELETE CASCADE,
    CONSTRAINT PK_Organizer_ID PRIMARY KEY (Company, Person)
);

By making the Company field unique, I can only ever have one organizer for any company.
ON DELETE CASCADE prevents me ending up with orphan organizer records for companies or people that don't exist.
Can't quite recall why I made the PRIMARY KEY both fields. Doesn't seem to hurt.

It was then just a matter of checking for an existing Organizer record and updating that if it existed, or inserting one if it didn't. I did this in the application layer, though I could just have easily have made a Stored Procedure that took Company.ID and Person.ID parameters, checked for Organizer records with the former, and updated the table accordingly. Could even throw in a check for whether the Person actually belongs to that company, and return a value accordingly to the application layer.

Restrict a bit field to true in only one child record at a time

1 Answers1