FK Constraint Problem

Hi All,
I use several SP's to create tables and I have run into a problem I do
not understand. All the SP's create a FK constraint when the table is
built and they all work, except for 1.
CREATE TABLE dbo.ZipCodes (
CountryID int NOT NULL,
StateID int NULL,
CountyID int NULL,
ZipCodeID int CONSTRAINT PK_ZipCodes PRIMARY KEY IDENTITY (1, 1) NOT NULL,
ZipCodeStatus smallint NOT NULL,
ZipCode nvarchar (25) NOT NULL,
CONSTRAINT FK_Counties_ZipCodes FOREIGN KEY (CountyID) REFERENCES
Counties (CountyID) ON DELETE NO ACTION ON UPDATE NO ACTION,
* CONSTRAINT FK_ZipCodes_Cities FOREIGN KEY (ZipCodeID) REFERENCES
Cities (ZipCodeID) ON DELETE CASCADE ON UPDATE CASCADE
)
The last FK constraint (*) will cause an error and the table is not
built. Once I add some data to the table then I can manually setup the
same constraint.
How do I get this to work when the SP is executed, before any data is added?
Thanks for any help,
Charles
On Tue, 14 Mar 2006 15:05:21 -0600, Charles E Finkenbiner wrote:

>Hi All,
>I use several SP's to create tables and I have run into a problem I do
>not understand. All the SP's create a FK constraint when the table is
>built and they all work, except for 1.
>CREATE TABLE dbo.ZipCodes (
> CountryID int NOT NULL,
> StateID int NULL,
> CountyID int NULL,
> ZipCodeID int CONSTRAINT PK_ZipCodes PRIMARY KEY IDENTITY (1, 1) NOT NULL,
> ZipCodeStatus smallint NOT NULL,
> ZipCode nvarchar (25) NOT NULL,
> CONSTRAINT FK_Counties_ZipCodes FOREIGN KEY (CountyID) REFERENCES
>Counties (CountyID) ON DELETE NO ACTION ON UPDATE NO ACTION,
>* CONSTRAINT FK_ZipCodes_Cities FOREIGN KEY (ZipCodeID) REFERENCES
>Cities (ZipCodeID) ON DELETE CASCADE ON UPDATE CASCADE
>)
>The last FK constraint (*) will cause an error and the table is not
>built. Once I add some data to the table then I can manually setup the
>same constraint.
>How do I get this to work when the SP is executed, before any data is added?
Hi Charles,
What is the error message you get?
Also, are you sure that this is the constraint you need to define? It's
highly unusual to have an identity column as a foreign key column.
Are you sure that the constraint should not go on the ZipCode column?
(And a foreign key constraint on a CityID column would make even more
sense, but there is not even a column CityID in the table).
Or did you intend to add a foreign key to the Cities table that
references the ZipCodes table?
Hugo Kornelis, SQL Server MVP
|||On 3/14/2006 4:36 PM, Hugo Kornelis wrote:
> Hi Charles,
> What is the error message you get?
> Also, are you sure that this is the constraint you need to define? It's
> highly unusual to have an identity column as a foreign key column.
> Are you sure that the constraint should not go on the ZipCode column?
> (And a foreign key constraint on a CityID column would make even more
> sense, but there is not even a column CityID in the table).
> Or did you intend to add a foreign key to the Cities table that
> references the ZipCodes table?
>
Hi Hugo,
The exact error message is: "There are no primary or candidate keys in
the referenced table 'Cities' that match the referencing column list in
the foreign key 'FK_ZipCodes_Cities'."
The ZipCodes table contains each zip code, once. There can be many
cities that use that zip code (this is to maintain the history of the
changes a zip code goes through, it may be Unacceptable to the PO now to
use a certain city name but my data goes back to the DOS days and I flag
that city name as unacceptable).
The Cities table does contain a column called ZipCodeID and it is
defined as int, so a 1 to many relationship exists between ZipCodes and
Cities.
Also, I am no expert with SQL but I do have many years experience with
relational databases since the DOS days. It is possible that I have
messed up my logic.
Thanks for any help,
Charles
|||On 3/14/2006 5:36 PM, Charles E Finkenbiner wrote:
> On 3/14/2006 4:36 PM, Hugo Kornelis wrote:
> Hi Hugo,
> The exact error message is: "There are no primary or candidate keys in
> the referenced table 'Cities' that match the referencing column list in
> the foreign key 'FK_ZipCodes_Cities'."
> The ZipCodes table contains each zip code, once. There can be many
> cities that use that zip code (this is to maintain the history of the
> changes a zip code goes through, it may be Unacceptable to the PO now to
> use a certain city name but my data goes back to the DOS days and I flag
> that city name as unacceptable).
> The Cities table does contain a column called ZipCodeID and it is
> defined as int, so a 1 to many relationship exists between ZipCodes and
> Cities.
> Also, I am no expert with SQL but I do have many years experience with
> relational databases since the DOS days. It is possible that I have
> messed up my logic.
>
> Thanks for any help,
> Charles
Hi,
I just noticed something else. I do not need to add any data at all.
After the ZipCodes table is built I can manually define the constraint
with no error message.
Primary key table: ZipCodes
Primary key column: ZipCodeID
Foreign key table: Cities
Foreign key column: ZipCodeID
So, I can do this manually but not with code. Any ideas?
Thanks for any help,
Charles
|||On Tue, 14 Mar 2006 17:46:40 -0600, Charles E Finkenbiner wrote:
(snip)
>I just noticed something else. I do not need to add any data at all.
>After the ZipCodes table is built I can manually define the constraint
>with no error message.
> Primary key table: ZipCodes
>Primary key column: ZipCodeID
> Foreign key table: Cities
>Foreign key column: ZipCodeID
>So, I can do this manually but not with code. Any ideas?
Hi Charles,
A foreign key constraint is always defined on the referencing column,
which "lives" in the table on the "many" side of the one-to-many
relationship. You tried to define it in the ZipCodes table, whereas it
should have been defined on the Cities table.
ALTER TABLE Cities
ADD CONSTRAINT FK_ZipCodes_Cities
FOREIGN KEY (ZipCodeID)
REFERENCES ZipCodes (ZipCodeID)
ON DELETE CASCADE ON UPDATE CASCADE
Hugo Kornelis, SQL Server MVP
|||On 3/15/2006 2:28 PM, Hugo Kornelis wrote:
> On Tue, 14 Mar 2006 17:46:40 -0600, Charles E Finkenbiner wrote:
> (snip)
>
> Hi Charles,
> A foreign key constraint is always defined on the referencing column,
> which "lives" in the table on the "many" side of the one-to-many
> relationship. You tried to define it in the ZipCodes table, whereas it
> should have been defined on the Cities table.
> ALTER TABLE Cities
> ADD CONSTRAINT FK_ZipCodes_Cities
> FOREIGN KEY (ZipCodeID)
> REFERENCES ZipCodes (ZipCodeID)
> ON DELETE CASCADE ON UPDATE CASCADE
>
Hi Hugo,
I see the error of my ways. :D) My brain must have been on vacation
because I never caught that mistake. I guess using Access as my SQL
database designer has gotten me confused. I am in the ZipCodes table
when I define the constraint and it works in Access XP (2002). At the
same time I have other SP's that define constraints in the 'many' side
table and I never connected the two. Sorry for the newbie mistake and
thanks for taking the time to let me know.
Thanks for your help,
Charles