Update row with foreign key relationships

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Update row with foreign key relationships

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2008-03-03 : 06:13:38

Hi,

I have 2 tables Publication and Advertisment_Codes. I want to update the publication_code in both tables where country_id=1 and publication_code='A'. However, it gives me an error because of foreign key relationship. I tried first updating in Advertisment_Codes and then in Publication table but it didn't work. I tried the other way round but also it didn't work.
How can I update the publication code?
Thanks

CREATE TABLE [dbo].[Advertisment_Codes](
	[Advertising_Code] [nvarchar](20) NOT NULL,
	[Country_id] [int] NOT NULL CONSTRAINT [DF_Advertisment_Codes_Country_id]  DEFAULT ((0)),
	[Publication_code] [nvarchar](20) NULL,
	[Advert_type] [nchar](1) NULL,
	[Date_Entered] [datetime] NULL,
	[User_Id_Ent] [int] NULL CONSTRAINT [DF_Advertisment_Codes_User_Id_Ent]  DEFAULT ((0)),
	[Date_Updated] [datetime] NULL,
	[User_Id_Upd] [int] NULL CONSTRAINT [DF_Advertisment_Codes_User_Id_Upd]  DEFAULT ((0)),
	[Default_Code] [bit] NULL CONSTRAINT [DF_Advertisment_Codes_Default_Code]  DEFAULT ((0)),
	[Active] [bit] NULL CONSTRAINT [DF_Advertisment_Codes_Active]  DEFAULT ((0)),
 CONSTRAINT [PK_Advertisment_Codes_1] PRIMARY KEY CLUSTERED 
(
	[Advertising_Code] ASC,
	[Country_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
ALTER TABLE [dbo].[Advertisment_Codes]  WITH CHECK ADD  CONSTRAINT [FK_Advertisment_Codes_Countries] FOREIGN KEY([Country_id])
REFERENCES [dbo].[Countries] ([Country_id])
GO
ALTER TABLE [dbo].[Advertisment_Codes] CHECK CONSTRAINT [FK_Advertisment_Codes_Countries]
GO
ALTER TABLE [dbo].[Advertisment_Codes]  WITH CHECK ADD  CONSTRAINT [FK_Advertisment_Codes_Publications] FOREIGN KEY([Country_id], [Publication_code])
REFERENCES [dbo].[Publications] ([Country_id], [Publication_code])
GO
ALTER TABLE [dbo].[Advertisment_Codes] CHECK CONSTRAINT [FK_Advertisment_Codes_Publications]

CREATE TABLE [dbo].[Publications](
	[Country_id] [int] NOT NULL,
	[Publication_code] [nvarchar](20) NOT NULL,
	[Publication_name] [nvarchar](50) NOT NULL,
	[Media_type] [int] NOT NULL CONSTRAINT [DF_Puclications_Media_type]  DEFAULT ((0)),
	[Date_Entered] [datetime] NULL,
	[User_Id_Ent] [int] NULL CONSTRAINT [DF_Publications_User_Id_Ent]  DEFAULT ((0)),
	[Date_Updated] [datetime] NULL,
	[User_Id_Upd] [int] NULL CONSTRAINT [DF_Publications_User_Id_Upd]  DEFAULT ((0)),
 CONSTRAINT [PK_Puclications] PRIMARY KEY CLUSTERED 
(
	[Country_id] ASC,
	[Publication_code] 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
ALTER TABLE [dbo].[Publications]  WITH CHECK ADD  CONSTRAINT [FK_Publications_Countries] FOREIGN KEY([Country_id])
REFERENCES [dbo].[Countries] ([Country_id])
GO
ALTER TABLE [dbo].[Publications] CHECK CONSTRAINT [FK_Publications_Countries]

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

sanoj_av
Posting Yak Master

118 Posts

Posted - 2008-03-03 : 08:10:21

1) You Cannot update the Primary key Attribute of a table if it is having some child records poiniting to it and your updation will make these child records orphans(that is happened at your first Case).
2) Besides,you cannot update the child attribute to a value that is not existing in the master table (probably happend at your second case)
So, first update the child table (Publications) attribute to a value that is already existing in the Master table(Advertisment_Codes) or delete these records if unwanted.Then update the master table

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2008-03-03 : 19:12:39

Look up the ON UPDATE CASCADE option for your foreign key constraint in Books Online.

=======================================
Society is like a stew. If you don't keep it stirred up you get a lot of scum on the top. -Edward Abbey, naturalist and author (1927-1989)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources