CODEWITHSUNDEEP
sqlsql-servert-sql
Katiso Koqo
Katiso Koqo

Reputation: 43

Subqueries are not allowed in this context

I want to prevent further duplicates from being added to my table while allowing existing duplicates to remain. I thought I could accomplish this using a filtered index as follows.

But when I execute the following query:

CREATE UNIQUE INDEX IX_Account
ON Holdings(Account)
WHERE Account NOT IN (select Account from Holdings)

I get the following error:

Msg 1046, Level 15, State 1, Line 57
Subqueries are not allowed in this context. Only scalar expressions are allowed.

How can I prevent further duplicates from being added?

Upvotes: 2

Views: 587

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88996

raiserror in a trigger will not automatically rollback the transaction, but throw will. Alternatively you can raiserror and rollback.

Also with an AFTER trigger the data in the INSERTED virtual table is already present in the table. So a trigger would need to look like:

use tempdb
drop table if exists Holdings 
create table Holdings(id int primary key, Account int)

go
    create or alter trigger no_more_duplicates on Holdings
    after insert as
    begin
        if exists
        (
            select 1 
            from inserted 
            where inserted.Account IN (select Account from Holdings where id <> inserted.id) 
        )
        begin
           throw 60000, 'Cannot add duplicates', 1 ;
          --raiserror('Cannot add duplicates',16,1)
        end;
    end -- trigger
go
insert into Holdings(id,Account) values (1,1)
go
insert into Holdings(id,Account) values (2,1)
go
select * from holdings

Upvotes: 1

George Menoutis
George Menoutis

Reputation: 7240

You can't have your cake and eat it.

Either

  1. decide that your data should have integrity and purge the duplicated before adding the unique index (filtering it for the reason you mention does not make sense) or
  2. enforce your logic with an insert trigger:
    create trigger no_more_duplicates on Holdings
    after insert as
        if exists
        (
            select 1 
            from inserted 
            where inserted.Account IN (select Account from Holdings) 
        )
            raiserror('Cannot add duplicates',16,0)
    end -- trigger

This trigger's a bit dumb, it will not prevent duplicates on a multiple-row insert, nor will it let the nonduplicate ones be saved. Yet, it's enough that you get the picture.

Upvotes: 2

Related Questions