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);
Best Answer
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 :