Unique Constraint with multiple null columns

Question

I have two tables

PC(Id,EmpName Not NULL, PCName NULL, HostName NULL,.... PhysicalLocation NULL, PCType Not NULL) PCNetwork(Id, EmpName Not NULL, PCName NULL, HostName NULL,IPAddr,....,PhysicalLocation NULL, PCType Not NULL)

Id is a surrogate key, EmpName and PCType are not null fields and the rest can be null. I created Unique Constraint on PC with these columns and FK on PCNetwork. If there is a null in one of the column of a record then the constraint works perfect but if more than one column has null the constraint is not working. This is a follow up question of this post

Edit:- My unique constraint and foreign key constriant is not working

alter table PC add constraint UK_PC_EMPName_PCName_PCType unique (EMPName, PCName, HostName, PhysicalLocation, PCType);

alter table PCNetwork add constraint FK_PC_PCNetwork foreign key (EMPName, PCName, HostName, PhysicalLocation, PCType) references PC (EMPName, PCName, HostName, PhysicalLocation, PCType);

Answer 1

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)

Answer 2

You can create a unique filtered index. Here is a simpler example, since I don't know what your goal is. We have a baseball team and some players have been assigned numbers; some haven't chosen yet.

CREATE TABLE dbo.PlayerUniforms
(
  PlayerID INT PRIMARY KEY,  -- FOREIGN KEY etc.
  UniformNumber TINYINT NULL -- CHECK (COALESCE(UniformNumber,0) < 100)
);

CREATE UNIQUE INDEX x ON dbo.PlayerUniforms(UniformNumber)
  WHERE UniformNumber IS NOT NULL;

Now you can enter multiple NULLs in the UniformNumber column like ANSI meant it to be, and still enforce uniqueness among the non-NULL values.