Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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);

share|improve this question
    
That's by design (msdn.microsoft.com/en-us/library/ms191166(v=SQL.105).aspx). What's your question? –  Thomas Stringer Apr 17 '13 at 13:46
    
Can you give examples of rows with NULL values that would or would not be considered duplicates? You can create a filtered unique index but the exact definition depends on what you are trying to accomplish. –  Aaron Bertrand Apr 17 '13 at 14:01
    
I cannot figure out what your problem is. Can you post your CREATE TABLE statemnts? –  AlexKuznetsov Apr 17 '13 at 14:21
    
Why do you want a 5-column foreign key? Yikes. –  Aaron Bertrand Apr 17 '13 at 16:50
    
@AaronBertrand I want PCNetwork common fields to be updated or deleted when PC common fields are updated or deleted, I mean cascade constraints. Like if PC's location changed the Network info of that PC should also have that location. Is there a way to do that? –  scc Apr 17 '13 at 20:14

2 Answers 2

up vote 2 down vote accepted

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)
share|improve this answer
1  
I didn't do exactly as you said but I changed my db design lil bit, created 2 PC tables of diff pc types, 2 n/w tables corresponding to pc's and still cascading issue didn't get solved because of nulls so I'm doing cascading updates and deletes by queries in pc table triggers which solved my problem. Thanks for the answer. –  scc May 1 '13 at 14:51

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.

share|improve this answer
    
I tried unique index as well, it did not work since I had 3 columns with null values. I have updated the question. –  scc Apr 17 '13 at 16:40

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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