1

I have the following tables: CREATE TABLE group_systems ( group_name, system_name, section_name, created_date, decom_date, status (Active, Deactivate) )

CREATE TABLE systems ( system_name, section_name )

A system is identified by the key (system_name, section_name). There can be dup system names but no dup section name.

In the groups table, I want to enforce the constraint that only one system in a section in a group can be active. However, because the groups table is also a history table, I can't just use the unique constraint (group_name, section_name, system_name). I have to use a check constraint that runs a subquery. There's also some additional constraints that are subqueries.

The problem is that inserting a benchmark of 100k records takes a long time (due to the subqueries).

Is it better to build another table active_systems_for_groups that references back to the group_systems table? That way, I can add the unique constraint to active_systems_for_groups that enforces only one active system per section per group and keep building complex constraints by adding more tables.

Is there a better way to handle complex check constraints?

Improve this question
1
  • You are using a SQL implementation that has CHECK constraints that support subqueries?! The only one I know of is Access (ACE, Jet, whatever) and I know SQL Server can 'fake it' using a UDF. If it is anything else, I'd appreciate learning which SQL it is. Firebird (as suggested by your handle) doesn't AFAIK. Commented Sep 26, 2011 at 14:05

2 Answers 2

Reset to default
2

You can enforce the "single active record" pattern in two ways:

  1. The solution you suggest, which is to create a table that holds only the primary key values of the active records from the multiple-records-allowed table. Those values also serve as a primary key in the active records table.

  2. Adding a column to another table that represents the objects that can have only a single active record each. In this case that would mean adding a column active_group_name to systems. This column would be a foreign key to the multiple-records-allowed table.

Which is preferable depends, in part, on whether every section is required to have an active group, whether it's common (but not required) for a section to have an active group, or whether it's only occasionally true that a section has an active group.

In the first case (required), you would use option (2) and the column could be declared NOT NULL, preserving complete normalization. In the second case (common) you would need to make the column NULLable but I'd probably still use that technique for convenience of JOINs. In the third case (occasional), I'd probably use option (1) since it might well improve performance when JOINing to get the active records.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

"In the first case (required), you would use option (2) and the column could be declared NOT NULL, preserving complete normalization" -- you seem to be implying that declaring the column (more likely to be columns -- plural -- hint = "history table") as nullable would result in denormalization. Please state which normal forms you think the table would be in for both cases. Hint: I don't think this represents denormalization but the burden of proof lies with you :)
While I'm not really into "angel on the head of a pin" arguments about Normal Forms I'll just point out that many people believe NULLable columns violate 1st Normal Form. I consider it mostly an academic argument, but many people make it.
sure, but that isn't what you meant in your answer though, right? So what do you mean? If it is meaningless, just remove it. No arguments here :)
I meant what I said. If you want to hew to perfect normal form (including Date's statement that a column must contain a value), then you must either use "option 1" with a NOT NULL specifier on the foreign key column or use "option 2". If you are more concerned with practicality over perfect normalization, you can use option 1 with a NULLable column.
1

Since you never answered which RDBMS you're using I'll throw this out there for others who might be interested in another way to easily handle this constraint in SQL Server (2008 or later).

You can use a filtered unique index to effectively put a constraint on the number of "active" rows for a given type. As an example:

CREATE UNIQUE INDEX My_Table_active_IDX ON My_Table (some_pk) WHERE active = 1

This approach has several advantages:

  1. It's declarative
  2. It's self-contained within the single table (no FKs, no other objects that you need to keep updated, etc.)
Improve this answer

4 Comments

I feel this solution is actually worse than the others because 1) two UPDATEs instead of one are required to change the active record, 2) an extra index is required (with the performance hit that involves), 3) the same datum is stored more than once, 4) not only are NULLs allowed, they're required, and 5) the information is stored in the wrong place (the active record is an attribute of it's owner, not the record itself). In addition, both of the suggestions I made are declarative as well.
Huh?!?! The mthods that you describe require extra updates. This requires none. The data is NOT stored more than once. The index maintenance is almost always completely negligible and is certainly less than maintaining extra tables to track "active" rows. It does not require any NULLs at all. Both of your solutions require extra tables. I think you've thoroughly misunderstood something here.
Not at all. In my solution, there is no IsActive field to UPDATE. The only UPDATE required is to one record in either in the systems table of the system_active_groups table, depending on the method chosen (in one of the two solutions, it would be an INSERT OR UPDATE). If you maintain the IsActive field, you need to UPDATE two records. The data is stored more than once, because inactive status is held both in the record that's inactive and a second time in the record that is active. You're right that you could use 0 instead of NULL for the second storage, however.
Plus, only one of my solutions requires an extra table, the other only adds a column to an existing table (both actually remove the IsActive column0. But in a relational database design the goal is not to minimize tables but to represent the data correctly. You are still storing that data, but effectively you've denormalized it into a single table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.