SQL statement failed, no primary key value for foreign key ... in table ...

Problem: SQL statement failed, no primary key value for foreign key ...
in table ...
  After deleting or changing primary key values in either consolidated
or remote
  database.

Question:
Are there any triggers that can resolve inserting a record that has a
foreign
key value with no existing primary key value in primary table?

Description:
We have a serious problem with the dbremote program. I think it's a
general
problem that could appear in every database with related tables.
Everytime
the problem turns up we must create new remote databases from the
consolidated database with dbextract.
Until now we haven't found any solution. I hope that someone has an
idea!

I will explain it with a simple example with two related tables:

Primary Table: CUSTOMER
 Fields: CustomerCode (Primary Key)
  Name
  City
  Street
  ...

Foreign Table: PROJECTS
 Fields: ProjectCode (primary key)
  CustomerCode (foreign key to CUSTOMER; update cascade trigger;
      delete cascade trigger)
  Description
  Notes
  ...

1st example
-----------
1) Someone working on the consolidated database changes the customer
code
   for a customer from 001 to 101. This customer has no projects until
now
   in the consolidated database.

2) Someone working on the remote database inserts a new project for the
   customer 001.

3) Now dbremote gets started with the consolidated database.

4) Dbremote gets started with the remote database.
  Incoming message ...
  => The customer code 001 will be changed to 101
  => update cascade trigger fires and the customer code for the new
     project will be changed.

  Sending message ...
  => Sending: Insert for the new project but with the old customer code

5) Dbremote gets started with the consolidated database.
  Incoming message ...
  => Executing: insert statement for the new project with the old
customer
                code
  => Error: SQL statement failed
  (because there is no primary key for customer 001)

  => No further statement can be committed because of the same error.

6) After this no further messages are received from the consolidated or
remote
   database but dbremote shows the error message: Missing message from
....

2nd example
-----------
1) Someone working on the consolidated database deletes the customer
with the
   customer code 001. This customer has no projects until now
   in the consolidated database.

2) Someone working on the remote database inserts a new project for the
   customer 001.

3) Now Dbremote gets started with the remote database.

4) Dbremote gets started with the consolidated database.
  Incoming message ...
  => Executing: insert statement for the new project with the old
customer
                code
  => Error: SQL statement failed
  (because there is no primary key for customer 001)

  => No further statement can be committed because of the same error.

5) After this no further messages are received from the consolidated or
remote
   database but dbremote shows the error message: Missing message from
....


Thanks in advance, Heiner Schmauder


0
Heiner
8/22/1998 3:50:17 PM
sybase.sqlanywhere.replication 4591 articles. 0 followers. Follow

1 Replies
882 Views

Similar Articles

[PageSpeed] 55

In the first problem, the cause of the problem is a change to the primary
key value, but there are more ramifications that you probably have not yet
detected.

1)  At the consolidated, add customer code of 1, name of 'Heiner Schmauder'.
2)  At the consolidated, add customer Code of 2 with a name of 'Carl
Federl';
3)  Run replication at all locations.
3)  At the consolidated, Update Code of 1 to Zero, Code of 2 to 1 and then
Code of Zero to 2 i.e. swap customer codes.
4)  at a remote, add Project A for Customer 1 and Project B for Customer 2.
5)  Run replication at all locations.
Now the bad news:
6)  Run a query at the consolidated to find that Project A is under customer
'Carl Federl' and Project B is related to customer 'Heiner Schmauder'
7)  Run a query at the remote to find that Project A is under customer
'Heiner Schmauder' and Project B is related to customer 'Carl Federl'

The only solution is to not allow the primary key to be changed.   Either do
not allow customer code to change or add a new dumb primary key and revising
all the foreign keys.

In the second problem, the cause is physically deleting before all foreign
key constraints are checked at every remote.  The solution is to add a
delete_requested_timestamp to the table.  On customer inserts, set the
delete_requested_timestamp to '9999-12-31' i.e. the largest date that
SQLAnywhere supports.  You will need to perform procedure FK validation in
the project triggers that includes a check on the
delete_requested_timestamp.   The PK column is a non-changing primary key.

create trigger ib900_project BEFORE insert order 900 on Project
 REFERENCING NEW AS NEW FOR EACH ROW
begin
IF NOT EXISTS
  (select 1 from customer
    where customer.PK = NEW.PK
      and customer.delete_requested_timestamp = '9999-12-31' )
then
    RAISERROR 20000 'Customer has been deleted by headquarters';
END IF
END;

Also add a trigger on Project after insert that sets the customer
delete_requested_timestamp to "infinity" when the timestamp is less than
infinity.

create trigger ib900_project AFTER insert order 900 on Project
 REFERENCING NEW AS NEW FOR EACH ROW
begin
UPDATE CUSTOMER
SET delete_requested_timestamp = '9999-12-31'
WHERE customer.PK = NEW.PK
      and customer.delete_requested_timestamp < '9999-12-31'
END IF
END;

Finally, on a period basis, perform a physical delete of customer when all
remotes have sent messages after the delete_requested_timestamp:

delete from customer
where delete_requested_timestamp < '9999-12-31'
and delete_requested_timestamp
 <   (select min(time_received) from SYS.SYSREMOTEUSER)
;

Good Luck

Carl C. Federl

Heiner Schmauder wrote in message <35DEE8B8.2D590F5A@hgmedia.com>...
<
Problem: SQL statement failed, no primary key value for foreign key in table
After deleting or changing primary key values in either consolidated or
remote database.  Question: Are there any triggers that can resolve
inserting a record that has a
foreign key value with no existing primary key value in primary table?

Description: We have a serious problem with the dbremote program. I think
it's a general problem that could appear in every database with related
tables. Everytime the problem turns up we must create new remote databases
from the consolidated database with dbextract.
Until now we haven't found any solution. I hope that someone has an idea!

I will explain it with a simple example with two related tables:

Primary Table: CUSTOMER
Fields: CustomerCode (Primary Key)
  Name
  City
  Street
  ...

Foreign Table: PROJECTS
Fields: ProjectCode (primary key)
  CustomerCode (foreign key to CUSTOMER; update cascade trigger;
      delete cascade trigger)
  Description
  Notes
  ...

1st example
-----------
1) Someone working on the consolidated database changes the customer code
for a customer from 001 to 101. This customer has no projects until now  in
the consolidated database.

2) Someone working on the remote database inserts a new project for the
   customer 001.

3) Now dbremote gets started with the consolidated database.

4) Dbremote gets started with the remote database.
  Incoming message ...
  => The customer code 001 will be changed to 101
  => update cascade trigger fires and the customer code for the new
     project will be changed.

  Sending message ...
  => Sending: Insert for the new project but with the old customer code

5) Dbremote gets started with the consolidated database.
  Incoming message ...
  => Executing: insert statement for the new project with the old
customer
                code
  => Error: SQL statement failed
  (because there is no primary key for customer 001)

  => No further statement can be committed because of the same error.

6) After this no further messages are received from the consolidated or
remote database but dbremote shows the error message: Missing message from
....

2nd example
-----------
1) Someone working on the consolidated database deletes the customer with
the
   customer code 001. This customer has no projects until now  in the
consolidated database.

2) Someone working on the remote database inserts a new project for the
customer 001.

3) Now Dbremote gets started with the remote database.

4) Dbremote gets started with the consolidated database.
  Incoming message ...
  => Executing: insert statement for the new project with the old
customer
                code
  => Error: SQL statement failed
  (because there is no primary key for customer 001)

  => No further statement can be committed because of the same error.

5) After this no further messages are received from the consolidated or
remote
   database but dbremote shows the error message: Missing message from
....

Thanks in advance, Heiner Schmauder
<


0
federl
8/24/1998 11:59:42 AM
Reply:

Similar Artilces:

SQL statement failed: (-194) No primary key value for foreign key
I'm getting this error message sporadically when my remote sites are replicating to my central server. The odd thing is the record that is the primary key value does exist! I'm at a lost...Does any have any idea, why this may be happening? Thanks Tasha Did the record exist at the time the error was reported? I have seen cases before where the Parent record is physically sent to the remote *after* the Child record. This would explain why the error was legitimately reported but when you look at the database later the Parent record now exists. -- -------------...

Primary Key Prob with SQL table innerjoin with primary key
Hello Exception Details: System.Data.MissingPrimaryKeyException: Table doesn't have a primary key. I have used Stored Procedure in SQL with innerjoin. I think this Stored Procedure is problem because when I use one table with primary key rather using innerjoin to get data from 2 SQL tables I don't have this Table doesn't have a primary key: CREATE proc dbo._BookLink As select Convert(nvarchar(10),BookID)as BookId, CategoryName,Title, Author, Publisher, PublicationDate, ISBN, Price, BriefDesc, FullDesc, [Image], link, htmlready from dbo.BookCategory inner join dbo.B...

No primary key value for foreign key 'ISYSTABCOL' in table 'ISYSCOLPERM'
Hi all, running SA 10.0.1.3686 and .3712 on Win 2003 Server SP1. The above message appears when I try to add a column to a table. It's just a simple one: alter table dbo.T_MaZuGavAktion add fkz_BeilageNrLautVersand int check (@Wert > 0) null; There is a view based on this table, and several users have been granted update column rights on this view. It is only possible to execute the "ALTER TABLE ADD column null" statement when I revoke the update column rights from the view before (and re-grant them afterwards). That happened once in the past week...

No primary key value for foreign key
Hi, I tried to: alter table sa.shipitems add foreign key FK_SHIPITEM_REFERENCE_OITEMS (oitemsid) references sa.oitems (oitemsid) on update cascade on delete restrict but got this error: No primary key value for foreign key 'FK_SHIPITEM_REFERENCE_OITEMS' in table 'shipitems' What does it mean and how to round this problem? fyi, here is the definition of both table /*==============================================================*/ /* Table: shipitems */ /*===============================...

SQL statement failed: (-837) Trigger or foreign key for table is no longer valid
This is a multi-part message in MIME format. ------=_NextPart_000_0015_01C4B83E.ED805430 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable We just rolled asa9.0.1 out to our field staff and are trying to = determine causes for some eoorors that we are seeing during replication = using dbremote. We are using ASA 9.0.1 build 1899 What is meant by the trigger or foreign key for table is no longer = valid on the following statements? I. 10/20 02:35:30. UPDATE DBA.Contact SET IllusExist=3D1, LastModified=3D'02:30:48.56...

no primary key value for foreign key value error in Master-Detail Dataset
Hi, We are using master detail relationship dataset but getting primary key violation error during posting the data. ------------------------------- *Project GBlWare.exe raised exception class EASANativeException with message* *[FireDAC][Phys][ODBC][Sybase][SQLAnywhere]no primary key value for foreign key* ------------------------------- Our current master detail scenario is that ,we are using parameter in details dataset that reference to master dataset primary key field value. But when I add a new record at that time master dataset key value is null. So when I press tab or down ...

Clustered Primary Key and Foreign Key: T-SQL
I am having a little trouble getting this to work right, but have come a ways since I started this.......other tables created first and with no problems..... then these two with the last table being the problemI need to set one foreign key in the second table referencing the first table.But, the primary key is clustered with the two foreign keys and I get the error....There are no primary or candidate keys in the referenced table 'courseScores' that match the referencing column list in the foreign key 'FK_course'.CREATE TABLE dbo.courseScores   (     course...

integer primary/foreign key vs. varchar(255) primary/foreign key
Hello. Is there any principal performance (or some other) difference between using the integer primary/foreign key and using the string (varchar(255)) keys in queries that use tables joins? Server is Sybase SQL Anywhere 5.5.04 Yes! The primary key will only be made up of 12 bytes. By using an integer you ensure that your entire column is in the key. -- Jim Egan [TeamPS] Dynamic Data Solutions, Inc. http://www.dyn-data.com Houston, TX *********************************************************** Enterprise Applications in Your F...

Updating tables in sequence with primary key and foreign key relations
Hi all,        In my project i will have the  data in a collection of objects, I need to update series of tables with foreign key relations        Right now my code looks like this        foreach(object obj in Objects){        int accountId=Account.Insert(obj.accountOpenDate,obj.accountName);//this will update the accounts table and returns account id which is a Identity column in Acccounts table        int DebtId=Debt....

Primary Keys containing common foreign keys from different tables
I have 2 tables Table 1 and Table 2 which have primary keys which include the same foreign key from a third Table 0. Table 1 has a primary key consisting of T1_KEY<pk> and T0_KEY<pk,fk>. Table 2 has a primary key consisting of T2_KEY<pk> and T0_KEY<pk,fk>. Now I want Table 3 to reference both Table 1 and Table 2 so that it has a primary key consisting of: T3_KEY<pk>, T0_KEY<pk, fk>, T1_KEY<pk, fk>, T2_KEY<pk, fk> I am trying to manually reverse engineer a database for which I only have a verbal description. I manage to achieve ...

Primary Key/Foreign Key
Can I use SQL Commands to add Primary key or reference keys to existed tables? (Not a new table, that's why I can't use CREATE TABLE command.) If does, could someone give me some sample codes? Thanks. Hi John, What you need is the ALTER TABLE statement. Look for it in SQLA help for the syntax. Regards, Nat. John Chyan wrote in message <35A4FF5D.5F192806@dissol.com>... >Can I use SQL Commands to add Primary key or reference keys to existed >tables? (Not a new table, that's why I can't use CREATE TABLE command.) > >If does, could someone ...

Keys, keys,keys...
Just installed PGP 6.5.8 and all is running fine, so far :-) Question: When I opened the PGP Keys window for the first time, apart from the *Create Key* wizard, there are about 50 odd keys belonging to various and sundry at NAI. Were these supplied for a purpose or only for illustration? Is there any reason they could not or should no be deleted? Question 2: I sent someone an encrypted test message using that person's public key. It went off fine except, I am left with only the encrypted eMail. What setting should I change in order for my eMail client (Outlook 2000) ...

foreign key as a primary key
hi, I am using a foreign key (which is a primary key in a secondary table) as a primary key in a primary table. There is also another primary key in the primary table. Do You have to declare the foregin key as a primary key ? regards Jan Eikeland email:jaeikela@c2i.net url :http://home.c2i.net/jaeikela In article <368b0234.5056065@forums.powersoft.com>, jaeikela@c2i.net says... > hi, > > I am using a foreign key (which is a primary key in a secondary table) > as a primary key in a primary table. > There is also another primary key in the primary table...

primary key and foreign Key????
 Hi i have 2 table.. Table1UID         Username1               AB2               CD3               DD Table 2ITEM       Price          UIDPOO1      20.00          1POO2      10.00          2POO3       15.00         1 Now i am letting the Admin to delete the user in table1But since UID 1 and 2 are ...

Web resources about - SQL statement failed, no primary key value for foreign key ... in table ... - sybase.sqlanywhere.replication

User:Jimbo Wales/Statement of principles - Wikipedia, the free encyclopedia
As we move forward with software and social changes, I think it is imperative that I state clearly and forcefully my views on openness and the ...

Category:Articles containing potentially dated statements from June 2006 - Wikipedia, the free encyclopedia ...
This is an administration category . It is used for administration of the Wikipedia project and is not part of the encyclopedia. It contains ...

Police Statement Ansell
ABC Home Open Sites menu ABC Home News iview TV Radio Kids Shop More Search Australia Weather News Home Just In Australia National Australian ...

Peyton Manning Issues Statement Denying Doping Allegations - Bleacher Report
Denver Broncos quarterback Peyton Manning has issued a statement strongly denying that he received human growth hormone from an anti-aging clinic ...

Charlie Sly recants statements about Peyton Manning - Business Insider Deutschland
Peyton Manning and other pro athletes have been named in an explosive Al Jazeera investigation into doping in pro sports.

Chicago police statement on double fatal police shooting - Chicago Tribune
Chicago police emailed the following statement to the media at about 9:45 the morning of Dec. 26, 2015, after an early morning police-involved ...

Super Mario Bros World 1-1 Gets Remade to Make Bold Statement About Accessibility
To be honest, I’m not totally sure what to think about this little video , but its heart seems to be in the right place. The creators of the ...

China firm loses 5 years of financial statements
China Animal Healthcare has lost five years of financial statements midway through a forensic audit ordered by regulators.

Does Draymond feel Warriors have chance to make statement vs Cavs?
... Giants A's Sharks Warriors Kings 49ers Raiders Quakes Insiders More Tickets Shop Watch Does Draymond feel Warriors have chance to make statement ...

UK Gov’t Issues Statement On Possibly Banning Trump From Britain
UK Gov’t Issues Statement On Possibly Banning Trump From Britain

Resources last updated: 1/4/2016 3:11:12 PM