I have two tables.
One of them stores some integer identificators, which are unique, but actually are structured entities. I will call them
Actually its a two parted indetificator, first byte of which is type of identificator. Lets call it
type_id It can have values, which are divided in two groups:
group named '
0x0f group': [
0x01, 0x02, 0x03, 0x04].
group named '
0xf0 group': [
The last 3 bytes of
full_id is arbitraty number and goes by name
So, it given that the
full_ids are unique.
acutual_ids are also unique, but only in scope of
0x0f group of
type_id values: there cant be equal
actual_id coupled with any of those
On the other hand -
actual_ids are not unique in a scope of
0xf0 group of
type_ids values and in practice there is plenty of duplicated values of
actual_id coupled with those
in SQL table A goes like so:
CREATE TABLE A ( full_id INTEGER NOT NULL PRIMARY KEY, actual_id AS ( CASE WHEN full_id & 0x0F000000 <> 0 THEN full_id & 0x00FFFFFF ELSE NULL END ) PERSISTED )
The second table contains only
actual_ids which is supposed to have
0x0f group. This is caused by the data stream, from which the second table is populated and this is something I cant change.
in SQL table B goes like so:
CREATE TABLE B ( actual_id INTEGER NOT NULL )
I want to:
I want to apply a foreign key:
ALTER TABLE B WITH CHECK ADD CONSTRAINT fk_a_actual_id FOREIGN KEY(actual_id) REFERENCES A(actual_id)
But I cant, because
actual_id in table A must have unique constraint. And I cant apply unique constraint on calculated column. How can I overcome this situation?
My DBMS is Microsoft SQL Server 2012