StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POUNIQUE CONSTRAINT on a column from foreign table in SQL Server 2008
text
Body
copied!<p>I have two tables:</p> <pre><code> 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]), <b>constraint [CK_Name] unique ([Name], [Sign])</b> ) </code></pre> <p>The problem is with the second constraint CK_Name</p> <p>Is there a way to make a constraint target column from a foreign table?</p> <p><br><br> EDIT:</p> <p><br><br> <b>Explanation.</b> 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:</p> <pre><code> public abstract class Main { // main properties } public class Names : Main { // names properties } </code></pre> <p>This forces me not to use sql views. <br><br> <b>Sample data.</b></p> <pre> --------------------------------------------- | 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' | </pre> <p>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.</p> <p>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</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload