Sql-server – computed column as foreign key allow null

computed-columnforeign keysql-server-2012unique-constraint

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:

group named '0x0f group': [0x01, 0x02, 0x03, 0x04].
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

Best Answer

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

Related Solutions

Sql-server – Unique Constraint with multiple null columns

I think your database is suffering from a bad data model. You might want to rethink it a little, judging from the tables you've mentioned the model isn't well normalized and you're trying to enforce a foreign key reference to a table column which isn't really a key to anything. One way to go is something like this :

CREATE TABLE EMP (ID INT PRIMARY KEY IDENTITY
                 ,EmpName VARCHAR(100) NULL)

CREATE TABLE PCTypes (id INT PRIMARY KEY IDENTITY
                     ,PcTypeName VARCHAR(100) NULL)

CREATE TABLE PC (Id INT PRIMARY KEY IDENTITY
                ,EmpId INT NULL
                ,PCName VARCHAR(100) NULL
                ,HostName VARCHAR(100) NULL
                ,PhysicalLocation VARCHAR(100) NULL
                ,PCType INT NULL)

ALTER TABLE PC ADD CONSTRAINT FK_PC_EmpId FOREIGN KEY (empId) REFERENCES Emp(id)
ALTER TABLE PC ADD CONSTRAINT FK_PC_PCType FOREIGN KEY (PCType) REFERENCES PCTypes(id)

CREATE TABLE PCNetwork (Id INT PRIMARY KEY IDENTITY
                       ,NetworkName VARCHAR(100) NULL
                       ,EmpId INT NULL
                       ,PhysicalLocation VARCHAR(100) NULL)

ALTER TABLE PCNetwork ADD CONSTRAINT FK_PCNetwork_EmpId FOREIGN KEY (empId) REFERENCES Emp(id)

CREATE TABLE PCNetwork_PCs (PCNetworkId INT NOT NULL
                           ,PCId INT NOT NULL
                           ,IPAddr NVARCHAR(20) NULL)

ALTER TABLE PCNetwork_PCs ADD CONSTRAINT FK_PCNetwork_PCs_Network FOREIGN KEY (PCNetworkId) REFERENCES PCNetwork(id)
ALTER TABLE PCNetwork_PCs ADD CONSTRAINT FK_PCNetwork_PCs_PC FOREIGN KEY (PCId) REFERENCES PC(id)
CREATE UNIQUE INDEX uq_pcnetworks ON PCNetwork_PCs(PCNetworkId, PCId)
CREATE UNIQUE INDEX uq_pcnetworks_ip ON PCNetwork_PCs(IPAddr)

Sql-server – Allow self-referential foreign keys to be null from an alter-table

If there are no illegal values in C.ParentID, then I am not clear what the problem might be. Is this Microsoft SQL Server?

I created a test case as follows:

Create Table dbo.Hier
(Id  INT IDENTITY Primary KEy,
 ParID INT NULL,
 Other varchar(10));
GO
 INSERT INTO dbo.Hier (ParID, Other) VALUES (NULL, 'Able');
 INSERT INTO dbo.Hier (ParID, Other) VALUES (1, 'Baker');
 INSERT INTO dbo.Hier (ParID, Other) VALUES (NULL, 'Charlie');
GO
ALTER TABLE dbo.Hier ADD 
      CONSTRAINT FK_Parent
      FOREIGN    KEY (ParID)
      REFERENCES Hier(ID);
GO
INSERT INTO dbo.Hier (ParID, Other) VALUES (3, 'Delta');
SELECT * FROM dbo.Hier;
GO
DROP TABLE dbo.Hier;
GO

Seems to work fine. If you are on a different SQL Server it might have different behavior. Or fluentMigrator may have a problem.