delete data

Hello I have a little problem with deleting data from database. I have to tables

Category:

CategoryID,CategoryName,CategoryImageID - collumns

CategoryImage - table

CategoryImageID,CategoryImage - collumns

I have foreign key between Category.CategoryImageID and CategoryImage.CategoryImageID

How can I delete category from Category table.

Due to the foreign key relationship, wouldn't you have to delete first the record on CategoryImage and THEN on Category

|||

Did you mean?

ALTER PROCEDURECategory_Delete

(@.CategoryIDint,

@.CategoryImageIDint)

AS

DELETE FROMCategoryImage

WHERECategoryImageID = @.CategoryImageID

DELETE FROMCategory

WHERECategoryID = @.CategoryID

Or how to write it ?

|||

Maybe like this one?

ALTER PROCEDURECategory_Delete

@.CategoryIDint

AS

DELETE FROMCategoryImage

WHERECategoryImageID in (select CategoryImageID from Category where CategoryID = @.CategoryID)

DELETE FROMCategory

WHERECategoryID = @.CategoryID

|||

The DELETE statement conflicted with the REFERENCE constraint "FK_Category_CategoryImage". The conflict occurred in database "AmoFurnitureDatabase", table "dbo.Category", column 'CategoryImageID'.

The statement has been terminated.

(1 row(s) affected)

(0 row(s) returned)

@.RETURN_VALUE = -6

It deleted only category but not categoryimage

|||

looks that you had opposite relations than I thought so you have to delete first from categories and next from images but it is not good so better is to modify your foreign key. What is you key definition?

|||

Yes It looks like and what worse it is. I have primary key in categoryImageID in CategoryImage table but in Category I have only categoryImageID without set primary key and I think I need primary keys on both because I cant set opposite foreign key. Will it be ok to set primary keys in both tables?

|||

yes you can do this, primary key is table specific and you can have only one in table and few foreign keys if you need them.

Good luck

|||

Thanks