April 25, 2010 at 3:26 pm
Hi Here is my Problem
I am giving you example from Nothwind Database.
Here are the two tables
CREATE TABLE [dbo].[CustomerCustomerDemo](
[CustomerID] [nchar](5) NOT NULL,
[CustomerTypeID] [nchar](10) NOT NULL,
CONSTRAINT [PK_CustomerCustomerDemo] PRIMARY KEY NONCLUSTERED
(
[CustomerID] ASC,
[CustomerTypeID] 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].[CustomerCustomerDemo] WITH CHECK ADD CONSTRAINT [FK_CustomerCustomerDemo] FOREIGN KEY([CustomerTypeID])
REFERENCES [dbo].[CustomerDemographics] ([CustomerTypeID])
GO
ALTER TABLE [dbo].[CustomerCustomerDemo] CHECK CONSTRAINT [FK_CustomerCustomerDemo]
GO
ALTER TABLE [dbo].[CustomerCustomerDemo] WITH CHECK ADD CONSTRAINT [FK_CustomerCustomerDemo_Customers] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Customers] ([CustomerID])
GO
ALTER TABLE [dbo].[CustomerCustomerDemo] CHECK CONSTRAINT [FK_CustomerCustomerDemo_Customers]
GO
GO
CREATE TABLE [dbo].[CustomerDemographics](
[CustomerTypeID] [nchar](10) NOT NULL,
[CustomerDesc] [ntext] NULL,
CONSTRAINT [PK_CustomerDemographics] PRIMARY KEY NONCLUSTERED
(
[CustomerTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
I am unable to perform these operations.
Update dbo.CustomerCustomerDemo set CustomerTypeID = 11
where CustomerTypeID = 1
Update dbo.CustomerDemographics set CustomerTypeID = 11
where CustomerTypeID = 1
Msg 547, Level 16, State 0, Line 2
The UPDATE statement conflicted with the FOREIGN KEY constraint "FK_CustomerCustomerDemo". The conflict occurred in database "Northwind", table "dbo.CustomerDemographics", column 'CustomerTypeID'.
The statement has been terminated.
Msg 547, Level 16, State 0, Line 5
The UPDATE statement conflicted with the REFERENCE constraint "FK_CustomerCustomerDemo". The conflict occurred in database "Northwind", table "dbo.CustomerCustomerDemo", column 'CustomerTypeID'.
The statement has been terminated.
I know that for foreign key we need to add ON UPDATE CASCADE
But i am unable to alter table correctly.
Can you guys please write the alter table statements for the foreign key and primary key of both tables so that i can update foreign key columns and primary key columns of both the tables.
Waiting for Reply
April 25, 2010 at 6:20 pm
Got the solution Guys.
I have done from the SQL Server Management Studio
To enable a foreign key constraint for INSERT and UPDATE statements
1. In Object Explorer, right-click the table with the constraint, and click Design.
The table opens in Table Designer.
2. From the Table Designer menu, click Relationships.
3. In the Foreign Key Relationships dialog box, select the relationship in the Selected Relationship list.
4. In the grid, click Delete Rule or Update Rule and choose an action from the drop-down list box to the left of the property.
* No Action An error message tells the user that the deletion is not allowed and the DELETE is rolled back.
* Cascade Deletes all rows containing data involved in the foreign key relationship.
* Set Null Sets the value to null if all foreign key columns for the table can accept null values. Applies to SQL Server 2005 only.
* Set Default Sets the value to the default value defined for the column if all foreign key columns for the table have defaults defined for them. Applies to SQL Server 2005 only.
I have Used CASCADE option.
Now i am able to update the foreign key and primary key columns.
If i update the primarykey value it will update to its all corresponding foreign keys.
No need of writing update statements for ForeignKey Tables. It is enough if we write for PK Table.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply