March 30, 2012 at 6:43 am
Below is parent table :-
CREATE TABLE [dbo].[Parent](
[Emp_id] [numeric](18, 0) NOT NULL,
[empname] [varchar](max) NOT NULL,
[address] [varchar](max) NOT NULL,
CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED
(
[Emp_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Now it's child table :-
CREATE TABLE [dbo].[child](
[Emp_id] [numeric](18, 0) NOT NULL,
[empname] [varchar](max) NOT NULL,
[address] [varchar](max) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[child] WITH CHECK ADD CONSTRAINT [FK_child_Parent] FOREIGN KEY([Emp_id])
REFERENCES [dbo].[Parent] ([Emp_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[child] CHECK CONSTRAINT [FK_child_Parent]
GO
Both parent and child table have same key-field parameters with same datatype.
here i applied update and delete cascade on both, as if i deleting from parent table then child table entry will get deleted.
my doubt is that.. can we apply update cascade on all field of table.. so that if i update any value from parent it will be updated in child table also ???????
Thanks & Regards,
Pallavi
March 30, 2012 at 8:20 am
pallavi.unde (3/30/2012)
can we apply update cascade on all field of table.. so that if i update any value from parent it will be updated in child table also ???????
No.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.March 30, 2012 at 8:31 am
It will only update the changes to the foreign key value. If you want to track all changes to the table you are looking at a trigger, or possibly CDC.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply