Take the 2-minute tour ×

I want to throw an exception if cardescription is in table in row with specific @idCar

cardescription is not PK, and any change in design is not allowed

insert into carsdescription


IDCar  | Description
1      | nice        - this record was in table before.
1      | nice        - should throw exception
2      | nice        - it is allowed

Carsdescription table:

  • ID (PK, int, autoincrement)
  • idcar(FK, int)
  • Description(varchar)

Cars table:

  • Id(int, PK, autoincrement)
  • Name(nvarchar(255)
share|improve this question

closed as not a real question by Otávio Décio, John Gietzen, Greg, Graviton, Vinay Sajip Mar 4 '10 at 10:06


Is idCar an identity column? –  alexphi Mar 3 '10 at 16:33
add comment

4 Answers

up vote 3 down vote accepted

You should create a unique index over IDCar and Description. Then your Database will prevent inserting duplicate data. Your CommandObject will then throw an exception.

insert into carsdescription(IDCar, description) 
values (@idCar, @description)

But to be nice to your users you should first lookup in your table if a duplicate entry would be insterted.

select count(*) from  carsdescription where IDCar = @idCar and decription = @description


Fore some reasons schema changes are not allowed. This means, your Database is unable to protected you against duplicate entries. So, as Matthew pointed out correctly, you have to:

  • First: do the select statement. If count is greater than 0, throw your Exception
  • Second: Do the insert
share|improve this answer
index is not allowed:/ and the second squery wouldn't throw exception... –  user278618 Mar 3 '10 at 16:36
I think the point is to do the query first, throw an exception if it returns true (I'd use EXISTS instead), otherwise, do the insert. –  Matthew Flynn Mar 3 '10 at 17:00
why not use the index? That's what it's purpose is! –  Leslie Mar 3 '10 at 18:56
add comment

If you can't use an index (why not?? Homework requirement??), then you can check before inserting, whether the record already exists, and only insert it if it doesn't exist:

IF NOT EXISTS(SELECT * FROM dbo.cardescription 
              WHERE idCar = @idCar AND Description = @description)
   INSERT INTO dbo.carsdescription(description, idCar) 
   VALUES(@description, @idCar)
   RAISERROR(N'Duplicate record - INSERT aborted', 16, 1)

Read up on RAISERROR in the MSDN tech library - this will cause an exception in the calling .NET application.

But the solution with the unique index would be definitely better!

   ON dbo.CarDescription(idCar, description)

That way, there would be no way to insert any duplicates - even if someone manages to connect to your database using Excel or some other tool, circumventing your SQL INSERT statement with the IF NOT EXISTS check.

share|improve this answer
add comment

An INSERT doesn't have a WHERE, use a SELECT-statement in your query to use the WHERE.

insert into carsdescription
SELECT (@description) FROM carsdescription
 where idCar=@idCar;
share|improve this answer
i corrected the code –  user278618 Mar 3 '10 at 16:35
add comment

This is an allowable transaction as far as the DB is concerned, so if you cannot change the constraints in the database (by adding an index or a trigger), you'll need to do it in the application.


Select exists(*) from carsdescription where IDCar = ? and description = ?

If this returns true, have your code throw an exception, if it is not, do the insert.

You don't say what RMDBS you are using. In Oracle you could write a pl/sql procedure that would set a variable to the results of the select, do the insert based on the result or throw an exception

share|improve this answer
If you're hitting SQL Server, see marc_s's solution –  Matthew Flynn Mar 3 '10 at 17:27
add comment
