December 19, 2005 at 7:52 pm
If two tables are created in this way:
Title Table
TitleID int(4) IDENTITY 1,1 Primary Key
TitleName nvarchar(50)
CityID int(4)
City Table
CityID int(4) IDENTITY 1,1 Primary Key
CityName nvarchar(50)
TitleID int(4)
How can this error be corrected:
Unable to create relationship 'FK_city_Titles'.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cascading foreign key 'FK_city_Titles' cannot be created where the referencing column 'city.cityId' is an identity column.
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.
December 19, 2005 at 8:42 pm
What code did you use to make the foreign key?
Why would you want to link a city to a single title??
December 19, 2005 at 8:48 pm
Its just an example. I created the foreign key thru the interface.
December 19, 2005 at 8:56 pm
Can't tell what's wrong without the code used to create it as I've no way to try to reproduce the error here.
December 20, 2005 at 12:16 am
CityId inthe Title table and TitleId in the city table. Isn't that a circular reference?
And if those aren't the real tables, please post the real table definitions.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 20, 2005 at 9:13 am
First of all, make sure that you identify the City table as the parent key table if you are using enterprise manager, or alter the Title table to reference city if using T-SQL.
Secondly I think the problem is that you are defining the constratint with a cascade option which is probably generating the error. There may be a limitation that you can't allow CASCADE update or delete when an identity column is involved. Look for that.
December 20, 2005 at 10:43 am
The problem is most likely with the circular refference, but I can't confirm without the code. Also I hope that this design doesn't go in production!!!
December 21, 2005 at 12:05 am
There may be a limitation that you can't allow CASCADE update or delete when an identity column is involved. Look for that.
There's no problem defining a cascading update/delete if the master column in an identity. There's no real point in cascading updates, since the identity value won't change, but there's no restriction against defining the relationship.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2005 at 11:38 am
Try this out and you will get the what you want:
CREATE TABLE [dbo].[City] (
[CityID] [int] IDENTITY (1, 1) PRIMARY KEY,
[CityName] [varchar] (50),
[TitleID] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Title] (
[TitleID] [int] IDENTITY (1, 1) PRIMARY KEY,
[TitleName] [varchar] (50),
[CityID] [int] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE Title ADD CONSTRAINT
FK_Title_City FOREIGN KEY (CityID)
REFERENCES City(CityID)
GO
ALTER TABLE City ADD CONSTRAINT
FK_City_Title FOREIGN KEY (TitleID)
REFERENCES Title(TitleID)
GO
December 21, 2005 at 2:58 pm
I have to agree with a previous poster. You are much better off not using Identity as a PK (ever). It is a meaningless PK because it does not enforce uniqueness, so unless you have strict controls on the data going into the tables you will end up with duplicate data and no real referential integrity eill be possible. Look for a naturally unique column of combination of columns that you can make a PK.
December 22, 2005 at 11:14 am
An identity can make a great primary key for multiple reasons
1. It is great for joining. I would hate to have a 3 column composite key that was migrating all over my db. The SQL would be horrible to write and it would be slow.
2. It is better for indexing than a string or composite key.
3. Cascading the update across the whole db is slow if part of the natural key changes.
If there is a good natural key, by all means use it. If not, use an identity. It does not void referential integrity because you will not end up with orphaned data.
One more point is that let's say you could use two of the existing columns as a PK. They are both strings, so it would be slow to join them and a waste of space to migrate them to all of your child tables. Create a PK as an identity then create an index or constraint on those two columns that enforces uniqueness as an alternate key.
This is obviously a point many people argue on both sides of and I just wanted to post so people could see valid cases and uses of an identity as a pk.
Here is another article about this: http://www.aspfaq.com/show.asp?id=2504
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply