Representation of circular references constraint

Question

We have a database where we store commands and properties for devices that may be controlled via a network.

For instance, "Concrete Device" is an instance of some DeviceType, which in turn contains commands and properties.

We have the ability to get values of the properties of devices by analyzing responses on commands; therefore there is a relationship between commands and properties. A problem arises when we trying to link commands from one DeviceType to properties from another DeviceType - this kind of link is useless and confusing.

How can I prevent this 'circular reference' through integrity constraints?

---

Here is a SqlFiddle for my scenario.

Answer 1

All you need is to propagate TypeId down to the link table:

CREATE TABLE Commands(
    CommandId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL,
    DeviceTypeId int NOT NULL REFERENCES DeviceTypes(TypeId),
    CONSTRAINT UniqueCommandNameInSpecificType UNIQUE(Name, DeviceTypeId),
-- added this constraint
    CONSTRAINT Commands_FkTarget UNIQUE(CommandId , DeviceTypeId)
);

CREATE TABLE DeviceProperties(
    PropertyId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL,
    DeviceTypeId int NOT NULL REFERENCES DeviceTypes(TypeId),
    CONSTRAINT UniquePropertyInSpecificType UNIQUE(TypeId,Name),
-- added this constraint
    CONSTRAINT DeviceProperties_FkTarget UNIQUE(PropertyId , DeviceTypeId)
);

CREATE TABLE DevicePropertiesInCommands(
    CommandId int NOT NULL,
    PropertyId int NOT NULL,
        DeviceTypeId INT NOT NULL,
    OrderNoOfProperty int NOT NULL,
    PRIMARY KEY(CommandId, PropertyId),
    CONSTRAINT UniqueOrderNoInSpecificCommand UNIQUE(CommandId,OrderNoOfProperty),
        CONSTRAINT FK_DevicePropertiesInCommands_Commands 
            FOREIGN KEY(CommandId , DeviceTypeId ) REFERENCES Commands(CommandId , DeviceTypeId ),
        CONSTRAINT FK_DevicePropertiesInCommands_DeviceProperties 
            FOREIGN KEY(PropertyId , DeviceTypeId ) REFERENCES DeviceProperties(PropertyId , DeviceTypeId )
);