Welcome to the third article in our series on database modeling and constraints in SQL Server. In this article, we will be focusing on the relationship between “Pets” and “Vets” entities and how to model and enforce the associated business rules.
Before we dive into the details, let’s take a look at the conceptual model diagram for our Pets and Vets solution:
The first business rule we will address is that a pet may be the patient of zero, one, or more vets, and a vet may provide healthcare for zero, one, or more pets. This is a classic many-to-many relationship, which we can resolve by introducing an associating table called “VetPatients”.
Experienced data modelers will recognize this pattern immediately. The associating table allows us to link the Vets and Pets tables, resolving the many-to-many relationship. In practice, additional attributes may be discovered once more is understood about the relationship being modeled.
Now, let’s move on to the second business rule: a pet may only see a vet if that vet is licensed to treat that species of pet. In the logical model, this constraint is simply a notation on the diagram and is not enforced by the design itself.
In order to enforce this constraint, we need to derive a valid set of values for the VetPatients table. We can achieve this by introducing a view that projects over the LicenseNbr and TaxID attributes from the Pets and Vets tables, respectively, and joins them on the SpeciesName attribute.
With this view, we can model a foreign key relationship from the VetPatients table to the view, which gives us a “pool” of all valid Pet/Vet combinations. Since the relationship expressed by VetPatients is a subset of all valid combinations, the relationship is modeled as [0..1]:1 from VetPatients to the view.
However, when it comes to the physical implementation, SQL Server does not support declaring foreign keys that reference views. One possible solution is to simulate the foreign key constraint via triggers. However, this approach has some drawbacks, including the need to maintain triggers for each table involved and the performance penalty for update operations.
Instead, we can implement this constraint using foreign keys by redundantly storing the SpeciesName attribute from the Pets and Vets tables in the VetPatients table. By doing so, we can impose an equality constraint on the two attributes, which enforces the business rule.
In SQL Server, it is a requirement that the attribute(s) in the referenced table of a foreign key relationship be unique. In our example, the only candidate key for the Pets table is LicenseNbr, so we must define a reducible superkey that includes the LicenseNbr attribute and the (superfluous) SpeciesName attribute to provide uniqueness.
Similarly, we define an analogous superkey in the Vets table over the TaxID and SpeciesName attributes. With these superkeys in place, we can eliminate the need for a check constraint by collapsing the redundant SpeciesName attributes into one attribute in the VetPatients table.
Here is the T-SQL script to add the superkeys to the Pets and Vets tables and create the VetPatients table:
ALTER TABLE dbo.Pets ADD CONSTRAINT uq_Pets_LicenseNbr_and_SpeciesName UNIQUE (LicenseNbr, SpeciesName) ALTER TABLE dbo.Vets ADD CONSTRAINT uq_Vets_TaxID_and_SpeciesName UNIQUE (TaxID, SpeciesName) CREATE TABLE dbo.VetPatients( LicenseNbr int NOT NULL, TaxID int NOT NULL, CONSTRAINT pk_VetPatients PRIMARY KEY (LicenseNbr, TaxID), SpeciesName varchar(24) NOT NULL, CONSTRAINT fk_VetPatients_to_Pets FOREIGN KEY (LicenseNbr, SpeciesName) REFERENCES dbo.Pets(LicenseNbr, SpeciesName), CONSTRAINT fk_VetPatients_to_Vets FOREIGN KEY (TaxID, SpeciesName) REFERENCES dbo.Vets(TaxID, SpeciesName) )
Finally, let’s run a unit test to ensure that our constraints are working correctly:
PRINT 'Valid: VetPatients' INSERT INTO dbo.VetPatients(LicenseNbr, TaxID, SpeciesName) SELECT 1001, 900700002, 'Dog' UNION ALL SELECT 1001, 900700003, 'Dog' UNION ALL SELECT 1002, 900700003, 'Dog' UNION ALL SELECT 1003, 900700001, 'Cat' UNION ALL SELECT 1004, 900700001, 'Cat' PRINT 'Invalid: VetPatients - Vet/Pet SpeciesName mismatch' INSERT INTO dbo.VetPatients(LicenseNbr, TaxID, SpeciesName) SELECT 1003, 900700002, 'Dog'
In conclusion, we have explored the Pets and Vets relationship and discussed how to model and enforce the associated business rules in SQL Server. We discovered a limitation in SQL Server’s support for foreign keys referencing views and presented a workaround using reducible superkeys. By redundantly storing the SpeciesName attribute in the associating table, we were able to enforce the constraint without the need for triggers.
Stay tuned for the next installment in our series, where we will continue to explore more database modeling concepts and constraints.
About the author: Troy Ketsdever is a database specialist with over 15 years of commercial software development experience. His main objective and vision is “making the right information available to the right people at the right time”.