Insert record and throw exception

Question

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
  (description) 
value 
  (@description,@idCar)

Examples:

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)

Answer 1

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

insert into carsdescription
 (description) 
SELECT (@description) FROM carsdescription
 where idCar=@idCar;

Answer 2

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

EDIT:

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

Answer 3

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)
ELSE 
   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!

CREATE UNIQUE NONCLUSTERED INDEX UIX01_Cardescription
   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.

Answer 4

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.

First

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