Auto update SQL table entries using foreign keys
-
Hello, I am using Microsoft SQL server 2008. I have 2 SQL tables Table 'Product1' and Table 'Product2'. In 'Product1' Table I have columns named 1.SLNO, 2.Name, 3.Place 4.State And 'Product2' have columns named, 1.SLNO, 2.Name, 3.Place 4.State 5.regno here 'regno' if FK to 'SLNO' (of product1 table ). My requirement is to update the columns in 'Product2' table when I modify data in 'Product1' table ( 'name' 'Place' and 'state' column). Is it possible to update 'Product2' table without running individual queries for each rows ? If yes how to do this ? Please help.
-
Hello, I am using Microsoft SQL server 2008. I have 2 SQL tables Table 'Product1' and Table 'Product2'. In 'Product1' Table I have columns named 1.SLNO, 2.Name, 3.Place 4.State And 'Product2' have columns named, 1.SLNO, 2.Name, 3.Place 4.State 5.regno here 'regno' if FK to 'SLNO' (of product1 table ). My requirement is to update the columns in 'Product2' table when I modify data in 'Product1' table ( 'name' 'Place' and 'state' column). Is it possible to update 'Product2' table without running individual queries for each rows ? If yes how to do this ? Please help.
Since the columns in
Product2always have to match the columns inProduct1, wouldn't it make more sense to remove them from theProduct2table? Otherwise, you're just duplicating data for no reason.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Since the columns in
Product2always have to match the columns inProduct1, wouldn't it make more sense to remove them from theProduct2table? Otherwise, you're just duplicating data for no reason.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
yes you are right. But for time being I cannot do that. However I got a one method which partially works. Here it goes.. So I have added 'Unique constraint' for 3 columns (Name, Place and State) in table1 table using,
ALTER TABLE table1 ADD
UNIQUE(column)and created foreign key in table2 table using,
ALTER TABLE table2 ADD
FOREIGN KEY (column) REFERENCES table1(column);
While adding 'ON DELETE CASCADE' to table2 I am getting error, that "There are no primary or candidate keys in the referenced table" Is it possible to set ''ON DELETE CASCADE" without adding primary key in the parent table ? Also how to add "ON DELETE CASCADE" for some foreign keys ? In my case I have 3 foreign keys ( Name, Place and State). But I want to add "ON DELETE CASCADE" only for Place and State. Please help.
-
yes you are right. But for time being I cannot do that. However I got a one method which partially works. Here it goes.. So I have added 'Unique constraint' for 3 columns (Name, Place and State) in table1 table using,
ALTER TABLE table1 ADD
UNIQUE(column)and created foreign key in table2 table using,
ALTER TABLE table2 ADD
FOREIGN KEY (column) REFERENCES table1(column);
While adding 'ON DELETE CASCADE' to table2 I am getting error, that "There are no primary or candidate keys in the referenced table" Is it possible to set ''ON DELETE CASCADE" without adding primary key in the parent table ? Also how to add "ON DELETE CASCADE" for some foreign keys ? In my case I have 3 foreign keys ( Name, Place and State). But I want to add "ON DELETE CASCADE" only for Place and State. Please help.
srikrishnathanthri wrote:
Is it possible to set ''ON DELETE CASCADE" without adding primary key in the parent table ?
A foreign key must reference either a primary key, or a column with a unique constraint. To set up a foreign key between the tables, you'll need a unique constraint on the SLNO column in table1.
srikrishnathanthri wrote:
But I want to add "ON DELETE CASCADE" only for Place and State.
I don't think you've understood what
ON DELETE CASCADEmeans. When you delete the row from table1, any rows in table2 with the same SLNO will be deleted as well. It doesn't do anything when you change a value in the referenced row, and it doesn't delete the value from specific columns. Cascading Referential Integrity Constraints[^] You could do this with a trigger[^] on table1, but it's a pretty nasty workaround, which can lead to hard-to-trace problems. As it's only two tables, it would be simpler to update both at the same time.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
srikrishnathanthri wrote:
Is it possible to set ''ON DELETE CASCADE" without adding primary key in the parent table ?
A foreign key must reference either a primary key, or a column with a unique constraint. To set up a foreign key between the tables, you'll need a unique constraint on the SLNO column in table1.
srikrishnathanthri wrote:
But I want to add "ON DELETE CASCADE" only for Place and State.
I don't think you've understood what
ON DELETE CASCADEmeans. When you delete the row from table1, any rows in table2 with the same SLNO will be deleted as well. It doesn't do anything when you change a value in the referenced row, and it doesn't delete the value from specific columns. Cascading Referential Integrity Constraints[^] You could do this with a trigger[^] on table1, but it's a pretty nasty workaround, which can lead to hard-to-trace problems. As it's only two tables, it would be simpler to update both at the same time.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Ok. I got your second point, yes now I understood 'ON DELETE CASCADE' correctly. But I have doubt in my first question. Here I am referring foreign key to a column with a unique constraint. I have created foreign keys refereeing to unique constraint without any error. The point I am getting error is when I run,
ALTER TABLE [dbo].[Product2] ADD CONSTRAINT
[FK_ProductDetails_Products] FOREIGN KEY([name],[place],[state])
REFERENCES [dbo].[Product1] ([name],[Place],[state])
ON UPDATE CASCADE
ON DELETE CASCADECan you guess what might be the error.
-
Ok. I got your second point, yes now I understood 'ON DELETE CASCADE' correctly. But I have doubt in my first question. Here I am referring foreign key to a column with a unique constraint. I have created foreign keys refereeing to unique constraint without any error. The point I am getting error is when I run,
ALTER TABLE [dbo].[Product2] ADD CONSTRAINT
[FK_ProductDetails_Products] FOREIGN KEY([name],[place],[state])
REFERENCES [dbo].[Product1] ([name],[Place],[state])
ON UPDATE CASCADE
ON DELETE CASCADECan you guess what might be the error.
Do you have a unique constraint on Product1 for name, place and state?
ALTER TABLE dbo.Product1
ADD CONSTRAINT UX_Product1_Name_Place_State
UNIQUE NONCLUSTERED (name, Place, state)If not, you can't create a foreign key pointing to it. If you have, then you'll probably get an error relating to multiple cascade paths, since you already have a foreign key relationship between the two tables.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Do you have a unique constraint on Product1 for name, place and state?
ALTER TABLE dbo.Product1
ADD CONSTRAINT UX_Product1_Name_Place_State
UNIQUE NONCLUSTERED (name, Place, state)If not, you can't create a foreign key pointing to it. If you have, then you'll probably get an error relating to multiple cascade paths, since you already have a foreign key relationship between the two tables.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Now I have a doubt, I want to another column named 'regno' and I want to make it primary key of product1. Now is it possible to add FK in prodcut2 for regno, name, state and place. ? Because I am getting an error..
Introducing FOREIGN KEY constraint 'FK_ProductDetails_Products' on table 'Product2' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
In my case, regno refers to Prooduct1's regno ( which is primary key) name, place and state refers to Prooduct1's name, place and state ( which are unique constraints ) Why I am getting this error ?
-
Now I have a doubt, I want to another column named 'regno' and I want to make it primary key of product1. Now is it possible to add FK in prodcut2 for regno, name, state and place. ? Because I am getting an error..
Introducing FOREIGN KEY constraint 'FK_ProductDetails_Products' on table 'Product2' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
In my case, regno refers to Prooduct1's regno ( which is primary key) name, place and state refers to Prooduct1's name, place and state ( which are unique constraints ) Why I am getting this error ?
Because you have multiple FKs between the tables. It's a bad design, but it's possible - so long as only one of the FKs specifies a "cascade" rule for update or delete.
Error message 1785 occurs when you create a FOREIGN KEY constraint that may cause multiple cascade paths[^]:
[A] table cannot appear more than one time in a list of all the cascading referential actions that are started by either a DELETE or an UPDATE statement.
You're trying to use a foreign key for something that it wasn't designed for. If you can't change the table design, switch to issuing multiple queries, or using a trigger to push the changes from Product1 to Product2.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
Hello, I am using Microsoft SQL server 2008. I have 2 SQL tables Table 'Product1' and Table 'Product2'. In 'Product1' Table I have columns named 1.SLNO, 2.Name, 3.Place 4.State And 'Product2' have columns named, 1.SLNO, 2.Name, 3.Place 4.State 5.regno here 'regno' if FK to 'SLNO' (of product1 table ). My requirement is to update the columns in 'Product2' table when I modify data in 'Product1' table ( 'name' 'Place' and 'state' column). Is it possible to update 'Product2' table without running individual queries for each rows ? If yes how to do this ? Please help.
Hi I would suggest you spend some time understanding Normalization of tables and reference integrity. If you have a good understanding of these concepts then it will be a lot easier for you designing the tables in the right way and fixing any errors.
