UNIQUE CONSTRAINT on a column from foreign table in SQL Server 2008

Question

I have two tables:

create table [dbo].[Main]
(
    [ID] [int] identity(1,1) primary key not null,
    [No] [int] not null,
    [Sign] [char](1) not null
)

create table [dbo].[Names]
(
    [ID_Main][int] primary key not null,
    [Name][nvarchar](128) not null,
    constraint [FK_Main_Users] foreign key ([ID_Main]) references [dbo].[Main]([ID]),
    constraint [CK_Name] unique ([Name], [Sign])
)

The problem is with the second constraint CK_Name

Is there a way to make a constraint target column from a foreign table?

EDIT:

Explanation. I'm using these tables in a Silverlight application using EntityFramework. The entities are created by Table per type inheritance so the code is something like this:

public abstract class Main
{
    // main properties
}

public class Names : Main
{
    // names properties
}

This forces me not to use sql views.

Sample data.

---------------------------------------------
|  Main                |  Names             |
---------------------------------------------
|  ID  |  Sign  |  No  |  ID_Main  |  Name  |
---------------------------------------------
|   1  |     A  |   1  |    1      |  'qwe' |
|   2  |     B  |   1  |    2      |  'qwe' |
|   3  |     B  |   1  |    3      |  'qwe' |
|   4  |     C  |   1  |    4      |  'qwe' |
|   5  |     A  |   2  |    5      |  'asd' |
|   6  |     B  |   2  |    6      |  'asd' |
|   7  |     B  |   2  |    7      |  'asd' |
|   8  |     C  |   2  |    8      |  'asd' |

As you can see there are some rows with the same Name but with different Sign. There can't be any non unique Name with the same Sign.

I'd like to enforce that there is only one Name with Sign = A and only one Name with Sign C but many Names with Sign = B

Answer 1

Yes you can enforce such constraint using indexed view and creating a unique constraint (index) on (Sign, Name) for the filtered resultset.

CREATE VIEW dbo.vwSelectiveUniqueSignName WITH SCHEMABINDING
AS
SELECT  [Sign], Name
FROM    
    dbo.Main INNER JOIN dbo.Names on ID = ID_Main
WHERE
    dbo.Main.Sign IN ('A', 'C')
GO

CREATE UNIQUE CLUSTERED INDEX IDX_vwSelectiveUniqueSignName_Unique_Sign_Name
    ON dbo.vwSelectiveUniqueSignName ( [Sign] ASC, Name ASC )
GO

Test:

-- using your sample data:
/* Case 1 Sign = 'A', Name = 'qwe': this will throw error 'cannot insert duplicate key row in object ... with unique index ... */
BEGIN TRAN NotAllowedMoreThan_1
insert dbo.Main ([Sign]) OUTPUT inserted.* values ('A') /* same for 'C' */
insert dbo.Names (ID_Main, Name) OUTPUT inserted.* SELECT SCOPE_IDENTITY(),'qwe'
COMMIT TRAN NotAllowedMoreThan_1
GO

/* Case 2 Sign = 'B', Name = 'qwe': this will pass > 1 times (note GO loop) */
BEGIN TRAN AllowedMoreThan_1
insert dbo.Main ([Sign]) OUTPUT inserted.* values ('B') /* any other than A, C */
insert dbo.Names (ID_Main, Name) OUTPUT inserted.* SELECT SCOPE_IDENTITY(),'qwe'
COMMIT TRAN AllowedMoreThan_1
GO 2

Answer 2

To answer your question, there is no means to reference a column in a foreign table in a check constraint (or any other kind of constraint for that matter). However, in your situation, you want to ensure that any combination of Names.Name and Main.Sign will be unique. To do that you can simply add a unique constraint on each of the two columns:

Alter Table dbo.Main Add Constraint UC_Main Unique Nonclustered ( Sign )
GO
Alter Table dbo.Names Add Constraint UC_Names Unique Nonclustered ( Name )

There is no need to create a unique constraint on ID_Main and Name together since ID_Main is already required to be unique via its primary key constraint.