How to prevent an entry in one table from existing in another table

I have 3 tables in SQL Server 2008

• Clubs with a PK of ID and a Name.
• Products which has a PK of ID, a FK of ClubID , a Name, a ShortCode and a Keyword.
  • There is a UK to enforce that there are no duplicate keywords for combinations of ShortCode/Keyword.
• ProductAdditionalShortCodes. This has a PK of ID, a FK of ProductID and a Keyword

The idea is to prevent any shortcode/keyword combination of products to point to different clubs and also to prevent the creation of duplicate short/code keyword combinations

I have a solution that works, but feels clunky, and could under certain circumstances fail if multiple users happened to update multiple entries at the same time. (Hypothetically)

How can I add some form of constraint to the DB to prevent the Keyword in the Main table from being the same as in the Additional table and the other way round?

Following is a sample script to create the scenario and some of the examples I want to prevent. I am not opposed to changing the DB design if the impact of the change would not disrupt too many other aspects of the solution. (I realize this is subjective)

use Tinker

if exists (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.ProductAdditionalKeywords') AND type in (N'U'))
    drop table dbo.ProductAdditionalKeywords
go
if exists (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.Products') AND type in (N'U'))
    drop table dbo.Products
go
if exists (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'dbo.Clubs') AND type in (N'U'))
    drop table dbo.Clubs
go

create table dbo.Clubs (
     ID        int         not null identity(1,1)
    ,Name      varchar(50) not null
    ,constraint PK_Clubs primary key clustered ( ID ) 
)
go
alter table dbo.Clubs add constraint UK_Clubs__Name unique ( Name )
go
create table dbo.Products (
     ID        int         not null identity(1,1)
    ,ClubID    int         not null
    ,Name      varchar(50) not null
    ,ShortCode varchar(50) not null
    ,Keyword   varchar(50) not null
    ,constraint PK_Products primary key clustered ( ID ) 
)
go
alter table dbo.Products add constraint UK_Products__ShortCode_Keyword unique ( ShortCode , Keyword )
go
alter table dbo.Products add constraint UK_Products__Name unique ( Name )
go
alter table dbo.Products add constraint FK_Products_ClubID foreign key ( ClubID ) references dbo.Clubs ( ID )
go
create table dbo.ProductAdditionalKeywords (
     ID        int         not null identity(1,1)
    ,ProductID int         not null
    ,Keyword   varchar(50) not null
    ,constraint PK_ProductAdditionalKeywords primary key clustered ( ID ) 
)
go
alter table dbo.ProductAdditionalKeywords add constraint FK_ProductAdditionalKeywords_ProductID foreign key ( ProductID ) references dbo.Products ( ID )
go
alter table dbo.ProductAdditionalKeywords add constraint UK_ProductAdditionalKeywords__Keyword unique ( Keyword )
go

insert into dbo.Clubs ( Name )
          select 'Club 1'
union all select 'Club 2'

insert into dbo.Products (ClubID,Name,Shortcode,Keyword) 
          select 1,'Product 1','001','P1' 
union all select 1,'Product 2','001','P2'
union all select 1,'Product 3','001','P3'
union all select 2,'Product 4','002','P4' 
union all select 2,'Product 5','002','P5'
union all select 2,'Product 6','002','P6'

insert into dbo.ProductAdditionalKeywords (ProductID,Keyword)
          select 1,'P1A'
union all select 1,'P1B'
union all select 2,'P2A'
union all select 2,'P2B'

/*
 What can be done to prevent the following statements from beeing allowed based on the reason in the comments?
 */

--insert into dbo.ProductAdditionalKeywords (ProductID,Keyword) values ( 1 , 'P2' ) -- Main keyword for product 2
--update dbo.Products set Keyword =  'P1A' where ID = 2                               -- Additional keyword for product 1
--insert into dbo.ProductAdditionalKeywords (ProductID,Keyword) values ( 3 , 'P1' ) -- Main ShortCode/Keyword combination for product 1

/*
 At the moment I look at the following view to see if the proposed(new/updated) Keyword/Shortcode combination already exists
 If it already exists I pevent the insert/update
 Is there any way to do it in the开发者_C百科 DB via constraints rather than in the BLL?
 */
select ShortCode,Keyword,count([ClubID]) as ClubCount from 
(
    select p.ClubID,p.ShortCode,p.Keyword,p.ID
    from dbo.Products p
union all 
    select p.ClubID,p.ShortCode,PAK.Keyword,PAK.ID * -1
    from dbo.ProductAdditionalKeywords as PAK 
    inner join dbo.Products P on PAK.ProductID = P.ID
) as FullList
group by Shortcode,Keyword
order by Shortcode,Keyword

How I would normally do this would be to place all of the keywords in a separate table (e.g. what is currently your additional table). If all keywords must be distinct within a ShortCode, then I'd include ShortCode in this table also, so that a unique constraint can be applied across both columns.

If all keywords for a product must be in the same ShortCode, then I'd keep ShortCode in Products also. I'd apply a unique constraint on (ID,ShortCode) in that table, and an additional foreign key from the keywords table, referencing both columns on both sides.

What we're left with now are two potential issues not included in your original design, but I don't know if they're a concern in practice:

1) Is the Keyword in Products more important, or special, than the additional keywords? If so, we need to add a column to keywords table to mark which one is important. To ensure only one is set, you can search for plenty of other SO questions which involve unique constraints with additional conditions. (Let me know if you can't find one and need it, I'm sure I can add a link if necessary)

2) Should a Product be allowed to have no keywords? If not, then I'd create a view that mimics your original Products table. In this circumstance, it would be easier if 1) above is true, in which case we always join to the "important" keyword. Otherwise, we need to have some way to limit it to a single row per product. We deny insert/update/delete on the table, and only allow them through the view. 3 relatively simple triggers will then maintain the underlying table structure.

on your design, I do not understand the the use of productAdditionalShortCodes having no field of ShortCode. However, you can add Unique key constraint with ShortCode & Keyword (composite key). This will eliminate duplicate entry in product table.