Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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 )
);
share|improve this answer
    
thanks, Alex. my first approach with trigger and second with CHECK CONSTRAINT and UDF frustrated me a little, but your solution restored my faith in SQL language. :) –  T_12 Sep 17 '12 at 18:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.