Hello
I am trying to delete a row from one table and I expected it to also be removed from the subsequent child tables, linked via foreign and primary keys.
However, when I tried to delete a row in the first table I saw this error:
DELETE FROM [dbo].[Names_DB]
WHERE [LName_Name]=N'andrews'
Error: Query(1/1) DELETE statement conflicted with COLUMN REFERENCE constraint 'FK_LName_Name'. The conflict occurred in database 'MainDB', table 'Category_A', column 'LName_Name'.
I went to the very last table in the sequence and I was able to delete the row without problems, but it did not effect any of the other tables.
Please advise.
I need to make many changes in these tables, should I use a trigger instead, if so what is the code to trigger each table? I am new to triggers.
Thanks
Regards
Lynn
Hello Fredrik
Thanks for the swift reply.
No I have not enabled the Cascade Delete Related Record I didn't know this was required, I am still a novice. Yes I have certainly missed this.
Now that I understand that this is required, I need to learn how to do this. Can you please direct me to a suitable turorial or perhaps explain how this is carried out and what is the required code for this process?
Thanks
Lynn
|||When you design a table in the Sql Enterprise Manager, you can add relations between tables. If you go to the properties of the relation you can enable the delete.
Another way is to handle it by your self by start removing the last table in the chain and move up to the main table.. but it need more code ;)
|||
Fredrik N:
When you design a table in the Sql Enterprise Manager, you can add relations between tables. If you go to the properties of the relation you can enable the delete.
Another way is to handle it by your self by start removing the last table in the chain and move up to the main table.. but it need more code ;)
Hello Fredrik
Within table properties where do I enable the delete to the existing tables. Can I add update also?
Thanks
Lynn
|||Hi
You can add constrains to a table it could be something like this:
CREATE TABLE Books ( BookIDINTNOT NULLPRIMARY KEY, AuthorIDINTNOT NULL, BookNameVARCHAR(100)NOT NULL, PriceMONEYNOT NULL)GOCREATE TABLE Authors ( AuthorIDINTNOT NULLPRIMARY KEY,Name VARCHAR(100)NOT NULL)GOALTER TABLE BooksADD CONSTRAINT fk_authorFOREIGN KEY (AuthorID)REFERENCES Authors (AuthorID)
ON DELETE CASCADE
ON UPDATE CASCADEGOYou can take a look atDatabase Objects: Constraints for moreHope this helps.|||
Hi Thanks for the post and the info.
As I have already created tables I am a little wary about altering tables in case I loose the data.
The tables I have already have foreign keys and primary keys.
I have a parent table called: DomNames
Primary Key = DomNamesID
I have recently inserted a foreign key:
Foreign Key = CatA_ID (taken from the Catagory A child table)
Then I have child category tables from A - Z
In category table A
Primary Key = CatA-ID
Foreign Key = DomNamesID (taken from the DomNames table)
In category table B
Primary Key = CatB-ID
Foreign Key = CatA_ID (taken from the previous A category table)
Each following table uses the Category ID alphabetical letter as a primary key and the previous tables Category ID as the foreign key, each table has a DomNameID column.
All tables contain data.
Do I miss out the first section of the code you mentioned and just put this:
ALTER TABLE DomNames
ADD CONSTRAINT fk_CatA_ID
FOREIGN KEY (CatAID)
REFERENCES DomNames (DomNamesID)
ON DELETE CASCADE
ON UPDATE CASCADE
I would be grateful if you would confirm before I alter my database.
Thanks
Regards
Lynn
