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

  • 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. – ypercubeᵀᴹ Aug 6 '15 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 – Vasilly.Prokopyev Aug 7 '15 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. – ypercubeᵀᴹ Aug 7 '15 at 9:32
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 | |
  • 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 – Vasilly.Prokopyev Aug 7 '15 at 5:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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