3

I have two tables.

TABLE A:

One of them stores some integer identificators, which are unique, but actually are structured entities. I will call them full_id.

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:

  1. group named '0x0f group': [0x01, 0x02, 0x03, 0x04].

  2. group named '0xf0 group': [0x10, 0x20].

The last 3 bytes of full_id is arbitraty number and goes by name actual_id

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 type_ids.

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 type_ids

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 )

TABLE B:

The second table contains only actual_ids which is supposed to have type_id within 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

Improve this question
3
  • 1
    You can create a partial unique index (to enforce uniqueness for those values that will be unique) but I don't think you can have a foreign key to a unique partial index. If you want a foreign key, you probably need an additional table where only the unique actual_ids are stored. Commented Aug 6, 2015 at 9:09
  • @ypercube Nope, I can not. SQL Server cant have computed columns in WHERE clause of an CREATE INDEX statement. It was first thing I tried Commented Aug 7, 2015 at 6:10
  • 1
    Right, I was not careful. But you can bypass that in your case with something like: sqlfiddle.com/#!6/2917d But still, no foreign keys possible. Commented Aug 7, 2015 at 9:32

1 Answer 1

Reset to default
3

You cannot create the contraint on A.actual_id because it may contains NULLs and there is no PK or unique index. I didn't touch B because you said you cannot change it.

Here is what I did. It may work for you:

  • Create A_data where <> 0, Create A_null when = 0 and your Table B

    Create Table dbo.A_data ( full_id INTEGER NOT NULL PRIMARY KEY, actual_id AS (CASE WHEN (full_id & 0x0F000000) <> 0 THEN full_id & 0x00FFFFFF ELSE NULL END) PERSISTED ) Create Table dbo.A_null (full_id INTEGER NOT NULL PRIMARY KEY) Create Table dbo.B(actual_id int not null)

  • I can now create a unique index on A_data and a contraints on B which only contains not null value

    Create UNIQUE INDEX idx_A_data On dbo.A_data(actual_id) Go ALTER Table B WITH CHECK ADD CONSTRAINT fk_a_actual_id FOREIGN KEY(actual_id) REFERENCES A_data(actual_id) Go

  • I then create a view A which look like your table A

    Create View A with schemabinding as Select full_id, actual_id From dbo.A_data union all Select full_id, null From dbo.A_null

  • I finally create a instead of trigger on the view. It will give you the same insert possibilities than your old table A

    Create Trigger triggerA on A Instead Of Insert As Insert Into A_data(full_id) select full_id from inserted where (full_id & 0x0F000000) <> 0 Insert Into A_null(full_id) select full_id from inserted where (full_id & 0x0F000000) = 0

  • You may also have to create Instead Of Update and Instead Of Delete triggers if required. It is better to keep them separated (1 trigger for each task: insert, delete, update)

Test

 insert into A(full_id)values(0),(1),(2),(286331153)

0, 1 and 2 go to A_null because (full_id & 0x0F000000) = 0 and 286331153 goes to A_data

A contains:

  • 286331153 / 1118481
  • 0 / NULL
  • 1 / NULL
  • 2 / NULL
Improve this answer
1
  • Sorry for that non clear statement about 'something I cant change'. I meant, that I cant change incoming data which goes into table B and which contains only actual id, so I cant have full id in table B. Table B itself can be changed and I actually did so to be able to make two field foreign key by type_id and actual_id in both tables. Your answer still is very helpful, ty Commented Aug 7, 2015 at 5:59

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.