FK constraint when FK row is there! Always takes 10 mins to fail!

I am developing a SQLServer/JSP/Java (Jrun4) web app that intermittently has a FK constraint that always hoses up the transaction for exactly 10 minutes.

The user can save a record from a form that inserts a few rows in the db. The second insert is dependent on the FK of the first insert. All the inserts are done in an entity bean that calls stored procs.

We've had this intermittant error where the inserts take exactly 10 minutes (every time!), then it fails with a FK constraint, yet out logging shows that it's using an existing foreign key. (the stored procs returns the FK)

We have a dev server and a test server, and this has yet to occur on the dev server! I feel like there must be something set up incorrectly.

Any ideas?

thanks in advance!

-DanFlagged -

Are any of the tables really large? Enforcing a FK constraint without indexes on large tables could cause it to take a really long time. 10 minutes seems extreme, but...

And do you know what the differences are between your dev and test server? Do they have the same constraints? Do they have the same data?|||And the same indexes and statistics?|||There isn't much data: under 100 rows, 10 column.

The problem only happens intermittently. In fact it hasn't happed in the last several hundered inserts (we've been testing it)

Originally posted by strader
Flagged -

Are any of the tables really large? Enforcing a FK constraint without indexes on large tables could cause it to take a really long time. 10 minutes seems extreme, but...

And do you know what the differences are between your dev and test server? Do they have the same constraints? Do they have the same data?|||Both servers are set up the same. The more I think about it- based on the infrequency of the problem it may just be a coincidence that it only happed on the one server.

I'm investigating a possible answer to the problem- I noticed an unhandled exception in the bean and I think it's possible the 1st insert worked, then some unhandled exception occured and rolled back the transaction, then the bean continuted to attempt to insert the child row- just a theory since this is so infrequent.

Originally posted by Paul Young
And the same indexes and statistics?|||Here's another possibility - if you're running simultaneous transactions from different threads or processes against that table, and if your java code or your interface (jdbc?) is hanging, causing that transaction to sit there indefinitely, it could be causing blocking or deadlocks on that small table. If it puts an exclusive lock on the table, and you're holding that transaction open, then all the other waiting processes will fail or timeout.

It might be happening on only one server vs. the other because you're stressing one more than the other.

If it happens again, try using SET LOCK_TIMEOUT to change it to a shorter time for that connection. The more I think about it, the more likely it seems this is what's happening, since it's timing out after exactly ten minutes each time.|||I wanted to try one more thing before I tried your suggestion- and it looks like I fixed the problem, but I'm not 100% sure why the transaction was failing. Here's what the code was doing:

When the user saves form data, the entity bean inserts rows into the database, and in the same transaction (I think) proceeds to update those rows. My guess is that once in a while sqlserver locks those rows so that the records cannot be re-read. My fix was to prevent the extraneous update from occuring. It was happening because I didn't know enough about Java beans when I wrote the bean code. (I only used the store method in the bean for UPDATES and I handled inserts manually, but the store is called automatically for all bean calls so I ended up inserting, then updating).

Our beans are container managed, so that their transactions begin when the bean is called and end when the bean returns. I thought that one could manipulate an uncommited row inserted within a transaction but I guess that's not ALWAYS the case because 1 out of 100 times it times out and fails.

I'd say the problem's solved since it's been a week since my fix was in and no more problems!

thanks for the help.

FK Constraint question

I have two tables
GroupUsers and Alias

GroupUsers
-GroupId
-AliasId
-UserId

Alias
-AliasId
-UserId

When a user joins a Group he selects a default Alias for his group, so I have put a foreign key constraint from the groupusers table to the alias table.

Now, if an alias is delete, is there any way through a foreign key constraint to set the value to null because all I've seen is Cascade and No Action

A better solution I'm looking for would be a way to run a script that would use the value in the UserId column from the GroupUsers table to select the top alias from the Alias table with a corresponding UserId (if exists) and set it to that. I'm unsure as to if this solution is even possible.
SQL Server doesn't support the SQL standard to set a FK to NULL, you would need to implement this, or your other solution, using a DELETE trigger on the Alias table.|||We support the SET DEFAULT and SET NULL options in SQL Server 2005. So for now, you will have to implement the logic using SPs or trigger code.

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

FK Constraint

Hi,
My Master table structure
CREATE TABLE [dbo].[GR_CHANNEL_M] (
[CHANNELNO] [varchar] (50) NOT NULL ,
[SERVICE] [varchar] (50) NOT NULL ,
[IVR] [varchar] (50) NOT NULL CONSTRAINT [GR_CHANNEL_M_PK] PRIMARY KEY
CLUSTERED ([IVR],[CHANNELNO]),
[OPTIONAL1] [varchar] (50) NULL ,
[OPTIONAL2] [varchar] (50) NULL )
GO
My transaction table
CREATE TABLE [dbo].[GR_CALL_DETAILS_M] (
[CALLID] [varchar] (50) NOT NULL constraint GR_CALL_DETAILS_M_PK primary
key clustered(CALLID),
[DATEANDTIME] [datetime] NULL ,
[DURATION] [numeric](10, 0) NULL ,
[IVR] [varchar] (50) NOT NULL ,
[CHANNELNO] [varchar] (50) NOT NULL CONSTRAINT
[CHANNEL_M_SCHEMECHANGE_T_FK1] FOREIGN KEY ([CHANNELNO])
REFERENCES [dbo].[GR_CHANNEL_M] ([CHANNELNO]),
[CALLTERMINATE] [varchar] (50) NULL)
If I create the table GR_CALL_DETAILS_M i am getting the error
There are no primary or candidate keys in the referenced table
'dbo.GR_CHANNEL_M' that match the referencing column list in the foreign key
'CHANNEL_M_SCHEMECHANGE_T_FK1'.
How to solve this?
thanks
vanithaYour foreign key is not linked to a primary key or unique constraint. For
example, if the primary key of gr_channel_m were channelno,
gr_call_details_m would work just fine.
Ben Nevarez
"vanitha" <vanitha@.discussions.microsoft.com> wrote in message
news:8B38AC26-7E65-4CCD-BBFA-BBB8BDF5A0AA@.microsoft.com...
> Hi,
> My Master table structure
> CREATE TABLE [dbo].[GR_CHANNEL_M] (
> [CHANNELNO] [varchar] (50) NOT NULL ,
> [SERVICE] [varchar] (50) NOT NULL ,
> [IVR] [varchar] (50) NOT NULL CONSTRAINT [GR_CHANNEL_M_PK] PRIMARY KEY
> CLUSTERED ([IVR],[CHANNELNO]),
> [OPTIONAL1] [varchar] (50) NULL ,
> [OPTIONAL2] [varchar] (50) NULL )
> GO
> My transaction table
> CREATE TABLE [dbo].[GR_CALL_DETAILS_M] (
> [CALLID] [varchar] (50) NOT NULL constraint GR_CALL_DETAILS_M_PK primary
> key clustered(CALLID),
> [DATEANDTIME] [datetime] NULL ,
> [DURATION] [numeric](10, 0) NULL ,
> [IVR] [varchar] (50) NOT NULL ,
> [CHANNELNO] [varchar] (50) NOT NULL CONSTRAINT
> [CHANNEL_M_SCHEMECHANGE_T_FK1] FOREIGN KEY ([CHANNELNO])
> REFERENCES [dbo].[GR_CHANNEL_M] ([CHANNELNO]),
> [CALLTERMINATE] [varchar] (50) NULL)
> If I create the table GR_CALL_DETAILS_M i am getting the error
> There are no primary or candidate keys in the referenced table
> 'dbo.GR_CHANNEL_M' that match the referencing column list in the foreign
> key
> 'CHANNEL_M_SCHEMECHANGE_T_FK1'.
> How to solve this?
> thanks
> vanitha|||"vanitha" <vanitha@.discussions.microsoft.com> wrote in message
news:8B38AC26-7E65-4CCD-BBFA-BBB8BDF5A0AA@.microsoft.com...
> Hi,
> My Master table structure
>
> If I create the table GR_CALL_DETAILS_M i am getting the error
> There are no primary or candidate keys in the referenced table
> 'dbo.GR_CHANNEL_M' that match the referencing column list in the
foreign key
> 'CHANNEL_M_SCHEMECHANGE_T_FK1'.
> How to solve this?
> thanks
> vanitha
Vanitha,
The tables slightly realigned for readability:
CREATE TABLE [dbo].[GR_CHANNEL_M] (
[CHANNELNO] [varchar] (50) NOT NULL ,
[SERVICE] [varchar] (50) NOT NULL ,
[IVR] [varchar] (50) NOT NULL
CONSTRAINT [GR_CHANNEL_M_PK]
PRIMARY KEY CLUSTERED ([IVR], [CHANNELNO]),
[OPTIONAL1] [varchar] (50) NULL ,
[OPTIONAL2] [varchar] (50) NULL )
GO
My transaction table
CREATE TABLE [dbo].[GR_CALL_DETAILS_M] (
[CALLID] [varchar] (50) NOT NULL constraint
GR_CALL_DETAILS_M_PK primary
key clustered(CALLID),
[DATEANDTIME] [datetime] NULL ,
[DURATION] [numeric](10, 0) NULL ,
[IVR] [varchar] (50) NOT NULL ,
[CHANNELNO] [varchar] (50) NOT NULL
CONSTRAINT [CHANNEL_M_SCHEMECHANGE_T_FK1]
FOREIGN KEY ([CHANNELNO])
REFERENCES [dbo].[GR_CHANNEL_M] ([CHANNELNO]),
[CALLTERMINATE] [varchar] (50) NULL)
Table GR_CHANNEL_M has a PRIMARY KEY of ([IVR], [CHANNELNO]).
You cannot create a foreign key back to the CHANNELNO column because
it is not a key in its own right, but only a *part* of a key, and
has no identifiable or enforced uniqueness.
Create GR_CHANNEL_M as (or use ALTER):
CREATE TABLE [dbo].[GR_CHANNEL_M] (
[CHANNELNO] [varchar] (50) NOT NULL ,
[SERVICE] [varchar] (50) NOT NULL ,
[IVR] [varchar] (50) NOT NULL
CONSTRAINT [GR_CHANNEL_M_PK]
PRIMARY KEY CLUSTERED ([IVR], [CHANNELNO]),
[OPTIONAL1] [varchar] (50) NULL ,
[OPTIONAL2] [varchar] (50) NULL
,CONSTRAINT GR_CHANNEL_M_UNI_CHANNELNO
UNIQUE (CHANNELNO) )
Note the new CONSTRAINT at the end of the DDL.
The second table can be created after this change is added.
Sincerely,
Chris O.|||if i enforce te unique key, that means that channelno is unique, in my logic
channelno is not unique, only channelno with that ivr is unique.
thanks
vanitha
"Chris2" wrote:

> "vanitha" <vanitha@.discussions.microsoft.com> wrote in message
> news:8B38AC26-7E65-4CCD-BBFA-BBB8BDF5A0AA@.microsoft.com...
> foreign key
> Vanitha,
> The tables slightly realigned for readability:
> CREATE TABLE [dbo].[GR_CHANNEL_M] (
> [CHANNELNO] [varchar] (50) NOT NULL ,
> [SERVICE] [varchar] (50) NOT NULL ,
> [IVR] [varchar] (50) NOT NULL
> CONSTRAINT [GR_CHANNEL_M_PK]
> PRIMARY KEY CLUSTERED ([IVR], [CHANNELNO]),
> [OPTIONAL1] [varchar] (50) NULL ,
> [OPTIONAL2] [varchar] (50) NULL )
> GO
> My transaction table
> CREATE TABLE [dbo].[GR_CALL_DETAILS_M] (
> [CALLID] [varchar] (50) NOT NULL constraint
> GR_CALL_DETAILS_M_PK primary
> key clustered(CALLID),
> [DATEANDTIME] [datetime] NULL ,
> [DURATION] [numeric](10, 0) NULL ,
> [IVR] [varchar] (50) NOT NULL ,
> [CHANNELNO] [varchar] (50) NOT NULL
> CONSTRAINT [CHANNEL_M_SCHEMECHANGE_T_FK1]
> FOREIGN KEY ([CHANNELNO])
> REFERENCES [dbo].[GR_CHANNEL_M] ([CHANNELNO]),
> [CALLTERMINATE] [varchar] (50) NULL)
>
> Table GR_CHANNEL_M has a PRIMARY KEY of ([IVR], [CHANNELNO]).
> You cannot create a foreign key back to the CHANNELNO column because
> it is not a key in its own right, but only a *part* of a key, and
> has no identifiable or enforced uniqueness.
>
> Create GR_CHANNEL_M as (or use ALTER):
>
> CREATE TABLE [dbo].[GR_CHANNEL_M] (
> [CHANNELNO] [varchar] (50) NOT NULL ,
> [SERVICE] [varchar] (50) NOT NULL ,
> [IVR] [varchar] (50) NOT NULL
> CONSTRAINT [GR_CHANNEL_M_PK]
> PRIMARY KEY CLUSTERED ([IVR], [CHANNELNO]),
> [OPTIONAL1] [varchar] (50) NULL ,
> [OPTIONAL2] [varchar] (50) NULL
> ,CONSTRAINT GR_CHANNEL_M_UNI_CHANNELNO
> UNIQUE (CHANNELNO) )
> Note the new CONSTRAINT at the end of the DDL.
> The second table can be created after this change is added.
>
> Sincerely,
> Chris O.
>
>|||Then perhaps you can do this:
CREATE TABLE [dbo].[GR_CHANNEL_M] (
[CHANNELNO] [varchar] (50) NOT NULL ,
[SERVICE] [varchar] (50) NOT NULL ,
[IVR] [varchar] (50) NOT NULL CONSTRAINT [GR_CHANNEL_M_PK] PRIMARY KEY
CLUSTERED ([IVR],[CHANNELNO]),
[OPTIONAL1] [varchar] (50) NULL ,
[OPTIONAL2] [varchar] (50) NULL )
CREATE TABLE [dbo].[GR_CALL_DETAILS_M] (
[CALLID] [varchar] (50) NOT NULL constraint GR_CALL_DETAILS_M_PK primary
key clustered(CALLID),
[DATEANDTIME] [datetime] NULL ,
[DURATION] [numeric](10, 0) NULL ,
[IVR] [varchar] (50) NOT NULL ,
[CHANNELNO] [varchar] (50) NOT NULL,
CONSTRAINT [CHANNEL_M_SCHEMECHANGE_T_FK1] FOREIGN KEY ([IVR], [CHANNELNO])
REFERENCES [dbo].[GR_CHANNEL_M] ([IVR], [CHANNELNO]),
[CALLTERMINATE] [varchar] (50) NULL)
-- Ben Nevarez
"vanitha" <vanitha@.discussions.microsoft.com> wrote in message
news:D3BE275B-701A-458B-9761-A53440BCCFDD@.microsoft.com...
> if i enforce te unique key, that means that channelno is unique, in my
> logic
> channelno is not unique, only channelno with that ivr is unique.
> thanks
> vanitha
> "Chris2" wrote:
>|||"vanitha" <vanitha@.discussions.microsoft.com> wrote in message
news:D3BE275B-701A-458B-9761-A53440BCCFDD@.microsoft.com...
> "Chris2" wrote:
>
the
<snip>
> if i enforce te unique key, that means that channelno is unique,
in my logic
> channelno is not unique, only channelno with that ivr is unique.
> thanks
> vanitha
>
vanitha,
Then you cannot have a foreign key reference back to it.
May I ask if you intended for GR_CALL_DETAILS_M to reference both
IVR and CHANNELNO?
CREATE TABLE [dbo].[GR_CALL_DETAILS_M] (
[CALLID] [varchar] (50) NOT NULL constraint
GR_CALL_DETAILS_M_PK primary
key clustered(CALLID),
[DATEANDTIME] [datetime] NULL ,
[DURATION] [numeric](10, 0) NULL ,
[IVR] [varchar] (50) NOT NULL ,
[CHANNELNO] [varchar] (50) NOT NULL,
[CALLTERMINATE] [varchar] (50) NULL,
CONSTRAINT [CHANNEL_M_SCHEMECHANGE_T_FK1]
FOREIGN KEY ([IVR], [CHANNELNO])
REFERENCES [dbo].[GR_CHANNEL_M] ([IVR], [CHANNELNO])
)
Sincerely,
Chris O.