Relative Constraints and Permissioning in T-SQL

Introduction

This article shows the implementation of relative database constraints and permissioning using shadow tables and table triggers in T-SQL.

Background

In relational databases the following constraints can usually be defined:

• Not null
• Primary key
• Foreign key
• Unique
• Check

Most of these constraints are absolute in the sense that their definition applies to all data rows in the table for which they are defined. It is for example not possible to define a column as null/not null or unique/not unique depending on the value in the same or other column of the same row. Such constraints are relative in the sense that their definition applies (or not) depending on the data values actually stored (see "Relative Constraints in ER Data Models", Behm/Teorey 1993).

ArticleDB Example

To illustrate the concept of relative constraints let us look at the example of an article database which stores electric home appliances (e.g. fridge, washing machine, blender, vacuum cleaner) and accessory parts (e.g. blender cups, cleaner bags) for them. The following information should be stored for the articles in this database:

• Article number
• Mnemonic
• Name
• Description
• Power consumption
• Type

The type is used as a discriminator to distinguish between electronic appliances and accessory parts.

From a business perspective the following constraints should be implemented:

1. ArticleNumber identifies an article uniquely and all articles must have ArticleNumber populated.
2. Mnemonic is optional but needs to start with A for appliances and P for accessory parts. In addition, the code needs to be unique, i.e. if set no two Articles should have the same code.
3. PowerConsumption is mandatory for appliances and optional for accessory parts.
4. Accessory parts must be linked to an appliance but appliances must not have any link.

Using constraints supported by T-SQL this would translate into the following Article table.

CREATE TABLE Article(
    Number int NOT NULL,
    Mnemonic varchar(20) NULL,
    Name varchar(100) NOT NULL,
    Description varchar(255) NULL,
    PowerConsumption decimal(12, 4) NULL,
    LinkedArticle int NULL,
    Type char(1) NOT NULL,
CONSTRAINT pk_Article PRIMARY KEY (Number),
CONSTRAINT chk_Type CHECK (Type IN ('A','P')),
CONSTRAINT chk_Mnemonic CHECK ((Mnemonic like 'A%' AND Type = 'A') OR (Mnemonic LIKE 'P%' AND Type = 'P')),
CONSTRAINT chk_PowerConsumption CHECK ((PowerConsumption IS NOT NULL AND Type ='A') OR Type ='P'),
CONSTRAINT chk_LinkedArticle CHECK ((LinkedArticle IS NOT NULL AND Type ='P') OR Type ='A')
)
GO
 
ALTER TABLE Article 
ADD CONSTRAINT fk_LinkedArticle FOREIGN KEY (LinkedArticle) REFERENCES Article (Number)
GO

The first constraint is implemented by the primary key definition on the table. The remaining ones are addressed by the constraints defined on the columns and table. As T-SQL however cannot define constraints that include other records of the same table (as most other RDBMS cannot) the fourth constraint can only be defined up o the point that LinkedArticle must be set for accessory parts but it cannot be defined that the article it references must be an appliance. Also the third constraint causes a problem as the column Mnemonic cannot be declared as unique as this would imply that there can only be one row that is NULL which is something we don't want. It should be possible for more than one row with Mnemonic set to NULL.

One approach to ensure that LinkedArticle only references from accessory part to appliance would be to create two tables in order to store appliances and accessory parts separately and then define the foreign key constraint between these two. This solution would however have some drawbacks:

• If the database model was to be extended by an Orders table, a foreign key constraint that references all articles cannot be defined that easily.
• Mechanisms would need to be put into place to keep the article number unique accross both tables.
• Queries that would want to access all articles would need to query two tables or go through a union view.

While the above points are not impossible to solve they are simply inconvenient when working with an Article table split into two and the benefit to get the foreign key right might not be worth the effort. Also splitting the Article table would still not help with having the mnemonic column as unique and allowing more than on row to be set to NULL at the same time.

An alternative solution that solves both would be to create shadow tables that are populated automatically from the primary table Article and on which the relative constraint can be defined. To demonstrate this approach for the given example, three tables are set up: One for accessory parts, one for appliances, and one for mnemonics:

CREATE TABLE Article(
    Number int NOT NULL,
    Mnemonic varchar(20) NULL,
    Name varchar(100) NOT NULL,
    Description varchar(255) NULL,
    PowerConsumption decimal(12, 4) NULL,
    LinkedArticle int NULL,
    Type char(1) NOT NULL,
CONSTRAINT pk_Article PRIMARY KEY (Number),
CONSTRAINT chk_Type CHECK (Type IN ('A','P'))
)
GO
 
ALTER TABLE Article 
ADD CONSTRAINT fk_LinkedArticle_Number 
    FOREIGN KEY (LinkedArticle) REFERENCES Article (Number)
GO
 
CREATE TABLE sdw_Mnemonic (
    Number int NOT NULL,
    Code varchar(20) NOT NULL,
CONSTRAINT pk_Mnemonic PRIMARY KEY (Number),
CONSTRAINT unq_Mnemonic UNIQUE (Code))
GO
 
ALTER TABLE sdw_Mnemonic 
ADD CONSTRAINT fk_Mnemonic_Number FOREIGN KEY (Number) REFERENCES Article (Number) 
ON UPDATE CASCADE
ON DELETE CASCADE
GO
 
CREATE TABLE sdw_Appliance (
    Number int NOT NULL,
    Mnemonic varchar(20) NULL,
    Name int NOT NULL,
    Description int NULL,
    PowerConsumption int NOT NULL,
    LinkedArticle int NULL,
Type char(1) not NULL,
CONSTRAINT pk_Appliance PRIMARY KEY (Number),
CONSTRAINT chk_MnemonicA CHECK (Mnemonic LIKE 'A%'),
CONSTRAINT chk_LinkedArticleA CHECK(LinkedArticle IS NULL),
CONSTRAINT chk_TypeA CHECK (Type = 'A'))
GO
 
ALTER TABLE sdw_Appliance 
ADD CONSTRAINT fk_Appliance_Number FOREIGN KEY (Number) REFERENCES Article (Number) 
ON UPDATE CASCADE 
ON DELETE CASCADE
GO
 
CREATE TABLE sdw_AccessoryPart (
    Number int NOT NULL,
    Mnemonic varchar(20) NULL,
    Name int NOT NULL,
    Description int NULL,
    PowerConsumption int NULL,
    LinkedArticle int NOT NULL,
    Type char(1) NOT NULL,
CONSTRAINT pk_AccessoryPart PRIMARY KEY (Number),
CONSTRAINT chk_MnemonicP CHECK (Mnemonic like 'P%'),
CONSTRAINT chk_TypeP CHECK (Type = 'P'))
GO
 
ALTER TABLE sdw_AccessoryPart
ADD CONSTRAINT fk_AccessoryPart_Number FOREIGN KEY (Number) REFERENCES Article (Number)
ON UPDATE CASCADE 
ON DELETE CASCADE
GO
 
ALTER TABLE sdw_AccessoryPart
ADD CONSTRAINT fk_AccessoryPart_LinkedArticle FOREIGN KEY 
        (LinkedArticle) REFERENCES sdw_Appliance (Number)
GO

The prefix sdw_ is used to indicate that they are shadow tables.

To populate the shadow tables from Article a trigger is defined that keeps the data in the primary table Article in sync with the shadow tables using the merge statement:

CREATE TRIGGER tiu_Article ON Article FOR INSERT, UPDATE
AS
BEGIN
    --Merge Mnemnonic

    MERGE 
        INTO sdw_Mnemonic AS s
        USING inserted AS I
        ON s.Number = i.Number
        WHEN MATCHED AND i.Mnemonic IS NOT NULL AND i.Mnemonic <> s.Code
            THEN UPDATE SET s.Code = i.Mnemonic
        WHEN MATCHED AND i.Mnemonic IS NULL
            THEN DELETE
        WHEN NOT MATCHED AND i.Mnemonic IS NOT NULL
            THEN INSERT (Number, Code) VALUES (i.Number, i.Mnemonic);
 
    --Merge Appliance

    MERGE 
        INTO sdw_Appliance AS s
        USING inserted AS I
        ON s.Number = i.Number
        WHEN MATCHED AND i.Type = 'A'
            THEN UPDATE SET Mnemonic = i.Mnemonic,
                Name = len(i.Name), 
                Description = len(i.Description), 
                PowerConsumption = datalength(i.PowerConsumption), 
                LinkedArticle = i.LinkedArticle
        WHEN MATCHED AND i.Type = 'P'
            THEN DELETE
        WHEN NOT MATCHED AND i.Type = 'A'
            THEN INSERT (Number, Mnemonic, Name, 
              Description, PowerConsumption, LinkedArticle, Type) 
            VALUES (i.Number, i.Mnemonic, len(i.Name), len(i.Description), 
                    datalength(i.PowerConsumption), i.LinkedArticle, i.Type);
 
    --Merge AccessoryPart

    MERGE 
        INTO sdw_AccessoryPart AS s
        USING inserted AS I
        ON s.Number = i.Number
        WHEN MATCHED AND i.Type = 'P'
            THEN UPDATE SET Mnemonic = i.Mnemonic,
                Name = len(i.Name), 
                Description = len(i.Description), 
                PowerConsumption = datalength(i.PowerConsumption), 
                LinkedArticle = i.LinkedArticle
        WHEN MATCHED AND i.Type = 'A'
            THEN DELETE
        WHEN NOT MATCHED AND i.Type = 'P'
            THEN INSERT (Number, Mnemonic, Name, Description, 
              PowerConsumption, LinkedArticle, Type) 
            VALUES (i.Number, i.Mnemonic, len(i.Name), len(i.Description), 
                    datalength(i.PowerConsumption), i.LinkedArticle, i.Type);
END
GO

An alternative approach using standard SQL is:

CREATE TRIGGER tiu_Article ON Article FOR INSERT, UPDATE
AS
BEGIN
    --INSERT Mnemnonic
    INSERT INTO sdw_Mnemonic (Number, Code)
        SELECT i.Number, i.Mnemonic
    FROM inserted i
    WHERE i.Mnemonic IS NOT NULL 
    AND NOT EXISTS(SELECT 1 FROM sdw_Mnemonic WHERE Number = i.Number)
 
    --UPDATE Mnemnonic
    UPDATE sdw_Mnemonic 
        SET Code = i.Mnemonic
    FROM sdw_Mnemonic s
    JOIN inserted i ON i.Number = s.Number
    WHERE i.Mnemonic IS NOT NULL
    AND i.Mnemonic <> s.Code
 
    --DELETE Mnemnonic
    DELETE sdw_Mnemonic
    FROM sdw_Mnemonic s
    JOIN inserted i ON i.Number = s.Number 
    WHERE Mnemonic IS NULL
 
    --INSERT Appliance
    INSERT INTO sdw_Appliance (Number, Mnemonic, Name, 
                Description, PowerConsumption, LinkedArticle, Type)
        SELECT i.Number, i.Mnemonic, LEN(i.Name), LEN(i.Description), 
               DATALENGTH(i.PowerConsumption), i.LinkedArticle, i.Type
    FROM inserted i
    WHERE i.Type = 'A' 
    AND NOT EXISTS(SELECT 1 FROM sdw_Appliance WHERE Number = i.Number)
 
    --Update Appliance
    UPDATE sdw_Appliance 
        SET Mnemonic = i.Mnemonic, 
            Name = LEN(i.Name), 
            Description = LEN(i.Description), 
            PowerConsumption = DATALENGTH(i.PowerConsumption), 
            LinkedArticle = i.LinkedArticle
    FROM sdw_Appliance s
    JOIN inserted i ON i.Number = s.Number
    WHERE i.Type = 'A'
 
    --DELETE Appliance
    DELETE sdw_Appliance
    FROM sdw_Appliance s
    JOIN inserted i ON i.Number = s.Number
    WHERE i.Type = 'P'
 
    --INSERT AccessoryPart
    INSERT INTO sdw_AccessoryPart (Number, Mnemonic, Name, 
           Description, PowerConsumption, LinkedArticle, Type)
    SELECT i.Number, i.Mnemonic, LEN(i.Name), LEN(i.Description), 
           DATALENGTH(i.PowerConsumption), i.LinkedArticle, i.Type
    FROM inserted i
    WHERE i.Type = 'P'
    AND NOT EXISTS(SELECT 1 FROM sdw_AccessoryPart WHERE Number = i.Number)
 
    --UPDATE AccessoryPart
    UPDATE sdw_AccessoryPart 
        SET Mnemonic = i.Mnemonic,
            Name = LEN(i.Name), 
            Description = LEN(i.Description), 
            PowerConsumption = DATALENGTH(i.PowerConsumption), 
            LinkedArticle = i.LinkedArticle
    FROM sdw_AccessoryPart s
    JOIN inserted i ON i.Number = s.Number
    WHERE i.Type = 'P'
 
    --DELETE AccessoryPart
    DELETE sdw_AccessoryPart
    FROM sdw_AccessoryPart s
    JOIN inserted i ON i.Number = s.Number
    WHERE i.Type = 'A'
END
GO

The rules applied for deriving the shadow table are:

• Create a shadow table for each subset of data in the primary table for which relative constraints should be applied
• Define the constraints on the shadow tables
• Create a trigger that populates the shadow tables from the primary table

The downside to this approach is that it creates data redundancy. To soften this issue a bit the following strategies can be used:

• Do not write the full data from the primary side to the shadow tables: Either by only storing the data length (as in our example) or by only storing relevant columns (i.e. columns that have a constraint on them defined) in the shadow table, or use a combination of both.
• Integrate the shadow tables in your audit concept: In case you already use shadow tables in your database to keep a history of changes (old records are stored in an audit table), these tables can be used to define the relational constraints on them rather then setting up dedicated shadow tables.

Relative Permissioning

By assigning different permissions to the shadow tables it is also possible to implement a relative permission concept. Traditionally, it is only possible to grant permissions on table or column level but not on data row level.

In our example it is now however possible to assign different permissions to Appliances and Accessory Parts and implement a basic relative permissioning which means that it is possible to set up users that are only permissioned to maintain data for appliances in Article and users that are only permissioned to maintain accessory parts in Article.

Using the code

To use the code set the objects up (e.g., in database tempdb) and then try to run some statements. Below are some statements to test the constraints.

--Prepare test data
INSERT INTO Article (Number, Mnemonic, Name, Description, PowerConsumption, LinkedArticle, Type)
VALUES (1000, 'AFRID', 'Fridge', 'Fridge 200 Watts', 200, NULL, 'A')
INSERT INTO Article (Number, Mnemonic, Name, Description, PowerConsumption, LinkedArticle, Type)
VALUES (2000, 'ABLEN', 'Blender', 'Blender 50 Watts', 50, NULL, 'A')
INSERT INTO Article (Number, Mnemonic, Name, Description, PowerConsumption, LinkedArticle, Type)
VALUES (3000, 'PCUP', 'Blender cup', 'Cup for blender', NULL, 2000, 'P')
INSERT INTO Article (Number, Mnemonic, Name, Description, PowerConsumption, LinkedArticle, Type)
VALUES (4000, 'AVAC','Vacuum cleaner','Vacuum cleaner 2000 Watts',2000, null, 'A')
INSERT INTO Article (Number, Mnemonic, Name, Description, PowerConsumption, LinkedArticle, Type)
VALUES (5000, 'PNOZ','Nozzle','Vacuum cleaner nozzle 10 Watt',10, 4000, 'P')
GO
 
--Check that Mnemonic starts with A for appliances and P for accessory parts

UPDATE Article
SET Mnemonic = 'FRID'
WHERE Number = 1000
GO
 
Output: The MERGE statement conflicted with the CHECK constraint "chk_MnemonicA". 
  The conflict occurred in database "tempdb", table "dbo.sdw_Appliance", column 'Mnemonic'.
 
UPDATE Article
SET Mnemonic = 'CUP'
WHERE Number = 3000
GO
 
Output: The MERGE statement conflicted with the CHECK constraint "chk_MnemonicP". 
  The conflict occurred in database "tempdb", table "dbo.sdw_AccessoryPart", column 'Mnemonic'.
 
--Check that Mnemonic is either NULL or unique

UPDATE Article
SET Mnemonic = NULL
GO
 
Output: (No error)
 
UPDATE Article
SET Mnemonic = Type
GO
 
Output: Violation of UNIQUE KEY constraint 'unq_Mnemonic'. Cannot insert 
  duplicate key in object 'dbo.sdw_Mnemonic'. The duplicate key value is (A).
 
--Check that PowerConsumption is mandatory for appliances and optional for accessory parts

UPDATE Article
SET PowerConsumption = NULL
WHERE Type = 'A'
GO
 
Output: Cannot insert the value NULL into column 'PowerConsumption', 
  table 'tempdb.dbo.sdw_Appliance'; column does not allow nulls. UPDATE fails.
 
WHERE Number = 5000
UPDATE Article
SET PowerConsumption = NULL
GO
 
Output: (No error)
 
--Check that accessory parts must be linked
--to an appliance but appliances must not have any link

UPDATE Article
SET LinkedArticle = NULL
WHERE Type = 'P'
GO
 
Output: Cannot insert the value NULL into column 'LinkedArticle', 
  table 'tempdb.dbo.sdw_AccessoryPart'; column does not allow nulls. UPDATE fails.
 
UPDATE Article
SET LinkedArticle = 3000
WHERE Number = 5000
GO
 
Output: The MERGE statement conflicted with the FOREIGN KEY constraint "fk_AccessoryPart_LinkedArticle". 
  The conflict occurred in database "tempdb", table "dbo.sdw_Appliance", column 'Number'.
 
UPDATE Article
SET LinkedArticle = 1000
WHERE Number = 4000
GO
 
Output: The MERGE statement conflicted with the CHECK constraint "chk_LinkedArticleA". 
  The conflict occurred in database "tempdb", 
  table "dbo.sdw_Appliance", column 'LinkedArticle'.

Points of Interest

I originally started writing this article using Sybase T-SQL for the examples but quickly realized that due to the lack of support for cascaded updates and deletes I will have to switch to MS T-SQL.

There are probably better ways to model an article database but the intention behind the approach chosen was to gear the model towards having as many as possible examples of the various relative constraints.

There might also be better solutions to implement relative constraints by using inherited tables and move the columns that differ with respect to constraints to child tables. However T-SQL (Sybase and MS SQL) which I know does not support inheritance. It would therefore be interesting to see a solution in Oracle PL or PostgresSQL.

History

• 2013-February-06: First draft version.
• 2013-May-12: Initial Version submitted.