How to manage uniqueness of a value that is stored in multiple databases tables

Question

I have separate assets tables for storing different kind of physical and logical assets, such as:-

Vehicle table( ID, model, EngineSize, Drivername, lastMaintenanceDate)
Server table ( ID, IP, OSName, etc…)
VM (ID, Size, etc…).
VM_IP (VM_ID,IP)

Now the problems I have is:-

For the IP column in the server table and in the VM_IP table, I need this column to be unique in these two tables, so for example the database should not allow a server and a VM to have the same IP. In the current design I can only guarantee uniqueness for the table separately. So can anyone advice on how I can handle this unique requirement on the databases level.

Regards

::EDITED::

I have currently the following database structure:-

enter image description here

Currently I see these points:-

I have introduced a redundant AssetTypeID column in the base Asset table, so I can know the asset type without having to join tables. This might break normalization.
In my above architecture , I cannot control (on the database level) which asset should have IP, which asset should not have IP and which asset can/cannot have multiple IPs. So is there a way to improve my architecture to handle these two points.

Thanks in advance for any help.

Pleun · Answer 1 · 2013-06-28T08:00:37.903

2

Create an IP table and use foreign keys

edited Jun 28 '13 at 08:00

answered Jun 27 '13 at 09:30

Pleun

8,856
2
30
50

you mean it is better ,, than using Indexed view as mentioned in the below reply. – John John Jun 27 '13 at 10:17
Better? Well a view with a clustered index is "almost" a table anyway. I believe a separate table is more straightforward/simple. – Pleun Jun 28 '13 at 08:02

score 1 · Answer 2 · answered Jun 27 '13 at 09:45

If I were facing the problem in design level, I would add two more tables:

A valid_IP table (containing valid IP range)
A Network_Enabeled, base table for all entities that may have an IP, like Server table, VM_IP ,... the primary key of this base table will be the primary key of child tables.

In Network_Enabeled table, Having a foreign key from valid_IP table and setting a unique key on the filed will be the answer.
Hope be helpful.

score 0 · Answer 3 · edited May 23 '17 at 11:48

0

You can use an indexed view.

CREATE VIEW YourViewName with SCHEMABINDING
as
    ...
GO

CREATE UNIQUE CLUSTERED INDEX IX_YourIndexName
    on YourViewName  (..., ...)

edited May 23 '17 at 11:48

Community

1
1

answered Jun 27 '13 at 09:11

davek

22,499
9
75
95

And how are you going to get values from those two tables to appear as the same column in this view, when you're not allowed `UNION`? – Damien_The_Unbeliever Jun 27 '13 at 12:29
@Damien:maybe by creating another (not indexed) view that uses UNION and which references my indexed view. – davek Jun 27 '13 at 12:39

Damien_The_Unbeliever · Answer 4 · 2013-06-28T08:34:07.860

Based on your edit, you can introduce a superkey on the asset table and use various constraints to enforce most of what it sounds like you're looking for:

create table Asset (
    AssetID int not null primary key,
    AssetTypeID int not null
    --Skip all of the rest, foreign keys, etc, irrelevant to example
    ,constraint UQ_Asset_TypeCheck
        UNIQUE (AssetID,AssetTypeID) --This is the superkey
)

The above means that the AssetTypeID column can now be checked/enforced in other tables, and there's no risk of inconsistency

create table Servers (
    AssetID int not null primary key,
    AssetTypeID as 1 persisted,
    constraint FK_Servers_Assets FOREIGN KEY (AssetID)
        references Asset (AssetID), --Strictly, this will become redundant
    constraint FK_Servers_Assets_TypeCheck FOREIGN KEY (AssetID,AssetTypeID)
        references Asset (AssetID,AssetTypeID)
)

So, in the above, we enforce that all entries in this table must actually be of the correct asset type, by making it a fixed computed column that is then used in a foreign key back to the superkey.

--So on for other asset types
create table Asset_IP (
    AssetID int not null,
    IPAddress int not null primary key, --Wrong type, for IPv6
    AssetTypeID int not null,
    constraint FK_Asset_IP_Assets FOREIGN KEY (AssetID)
        references Asset (AssetID), --Again, redundant
    constraint CK_Asset_Types CHECK (
        AssetTypeID in (1/*,  Other types allowed IPs */)),
    constraint FK_Asset_IP_Assets_TypeCheck FOREIGN KEY (AssetID,AssetTypeID)
        references Asset (AssetID,AssetTypeID)
)

And now, above, we again reference the superkey to ensure that we've got a local (to this table) correct AssetTypeID value, which we can then use in a check constraint to limit which asset types are actually allowed entries in this table.

create unique index UQ_Asset_SingleIPs on Asset_IP (AssetID)
   where AssetTypeID in (1/* Type IDs that are only allowed 1 IP address */)

And finally, for certain AssetTypeID values, we ensure that this table only contains one row for that AssetID.

I hope that gives you enough ideas of how to implement your various checks based on types. If you want/need to, you can now construct some views (through which the rest of your code will interact) which hides the extra columns and provides triggers to ease INSERT statements.

On a side note, I'd recommend picking a convention and sticking to it when it comes to table naming. My preferred one is to use the plural/collective name, unless the table is only intended to contain one row. So I'd rename Asset as Assets, for example, or Asset_IP as Asset_IPs. At the moment, you have a mixture.

How to manage uniqueness of a value that is stored in multiple databases tables

4 Answers4