question

Is this an existing table with existing data that you want to find? Or do you want a design that doesn't allow it in the first place? Lets take the first scenario: **existing data** Set up some test data use tempdb; --this table will allow the 'bad' data create table tempdb..HouseHold_Allow (Hhldkey int, Cust_type char(1)) insert into tempdb..HouseHold_Allow select 1,'P' insert into tempdb..HouseHold_Allow select 1,'P' insert into tempdb..HouseHold_Allow select 1,'S' insert into tempdb..HouseHold_Allow select 2,'S' insert into tempdb..HouseHold_Allow select 2,'S' insert into tempdb..HouseHold_Allow select 3,'P' insert into tempdb..HouseHold_Allow select 3,'S' insert into tempdb..HouseHold_Allow select 3,'S' Now this query will give you all those Hhldkey values that break the rules. It does this by finding all those Hhldkey values where there is one and only 1 row for the 'P', and then comparing those to all Hhldkey values. select distinct Hhldkey from tempdb..HouseHold_Allow where Hhldkey not in ( select Hhldkey from tempdb..HouseHold_Allow where Cust_type = 'P' group by Hhldkey having count(Hhldkey) = 1 ) go If you want to design a table that does not allow this data, then this is more interesting! Lets setup our table --this table will not allow the 'bad' data create table tempdb..HouseHold_NotAllow (Hhldkey int, Cust_type char(1)) go Now for the first rule of only one 'P' then this can be easily constrained by adding a **unique, filtered** index. We create an index that only looks at the 'P' rows, and make it unique. create unique index UFX_HouseHold_NotAllow on HouseHold_NotAllow (Hhldkey, Cust_type) where Cust_type = 'P' go To enforce the rule that there must be a primary, I've decided to create a trigger. This trigger will rollback the transaction if it finds a 'S' row without a 'P' row - the details of exactly what you want to check for and how to handle it I'll leave up to you. create trigger CheckOnePrimary on dbo.HouseHold_NotAllow for insert, update as if exists( select Hhldkey, Cust_type from dbo.HouseHold_NotAllow where Cust_type = 'S' and Hhldkey not in (select Hhldkey from dbo.HouseHold_NotAllow where Cust_type = 'P') ) begin raiserror ('Cannot have a Houshold without a Primary', 16, 1); rollback transaction; end go So now if we try to add rows with 2 'P' rows insert into tempdb..HouseHold_NotAllow select 1,'P' insert into tempdb..HouseHold_NotAllow select 1,'P' insert into tempdb..HouseHold_NotAllow select 1,'S' we will get an error Msg 2601, Level 14, State 1, Line 48 Cannot insert duplicate key row in object 'dbo.HouseHold_NotAllow' with unique index 'UFX_HouseHold_NotAllow'. The duplicate key value is (1, P). The statement has been terminated. Be aware though that 2 of these rows will still make it into the table, it's only the 2nd 'P' that fails - if you wanted the whole batch to fail you would have to handle that in a transaction. Try and add 'S' rows without a 'P' insert into tempdb..HouseHold_NotAllow select 2,'S'; insert into tempdb..HouseHold_NotAllow select 2,'S'; gives us Msg 50000, Level 16, State 1, Procedure CheckOnePrimary, Line 61 Cannot have a Houshold without a Primary Msg 3609, Level 16, State 1, Line 51 The transaction ended in the trigger. The batch has been aborted. but the valid data is fine insert into tempdb..HouseHold_NotAllow select 3,'P'; insert into tempdb..HouseHold_NotAllow select 3,'S'; insert into tempdb..HouseHold_NotAllow select 3,'S';