[AccessD] Upsize errors

John Colby jwcolby at ColbyConsulting.com
Wed Dec 21 23:09:42 CST 2005


The weird part is that when I did this on the BACK END directly I didn't get
these, but when I do it through the FE there are just tons of them.

Server Error 547: ALTER TABLE statement conflicted with COLUMN FOREIGN KEY
constraint 'tblClaim_FK00'. The conflict occurred in database 'DISCO_SQL',
table 'tblClaimant', column 'CLMT_ID'.

The code that caused the error.
ALTER TABLE tblClaim
WITH CHECK ADD 
        CONSTRAINT "tblClaim_FK00"
        FOREIGN KEY (CL_IDCLMT) REFERENCES tblClaimant(CLMT_ID) 
ON DELETE NO ACTION
ON UPDATE NO ACTION, 

        CONSTRAINT "tblClaim_FK01"
        FOREIGN KEY (CL_IDEX) REFERENCES tblDISEmployee(EM_ID) 
ON DELETE NO ACTION
ON UPDATE NO ACTION, 

        CONSTRAINT "tblClaim_FK02"
        FOREIGN KEY (CL_IDCLOC) REFERENCES tlkpClaimLocation(CLOC_ID) 
ON DELETE NO ACTION
ON UPDATE NO ACTION, 

        CONSTRAINT "tblClaim_FK03"
        FOREIGN KEY (CL_IDIStatus) REFERENCES tlkpClaimStatus(CS_ID) 
ON DELETE NO ACTION
ON UPDATE NO ACTION, 

        CONSTRAINT "tblClaim_FK04"
        FOREIGN KEY (CL_IDIStatus) REFERENCES tlkpClaimStatus(CS_ID) 
ON DELETE NO ACTION
ON UPDATE NO ACTION, 

        CONSTRAINT "tblClaim_FK05"
        FOREIGN KEY (CL_IDDUEP) REFERENCES tlkpDateUnits(DU_ID) 
ON DELETE NO ACTION
ON UPDATE NO ACTION, 

        CONSTRAINT "tblClaim_FK06"
        FOREIGN KEY (CL_IDDUBP) REFERENCES tlkpDateUnits(DU_ID) 
ON DELETE NO ACTION
ON UPDATE NO ACTION, 

        CONSTRAINT "tblClaim_FK07"
        FOREIGN KEY (CL_IDDX1) REFERENCES tlkpDX(DX_ID) 
ON DELETE NO ACTION
ON UPDATE NO ACTION, 

        CONSTRAINT "tblClaim_FK08"
        FOREIGN KEY (CL_IDDX2) REFERENCES tlkpDX(DX_ID) 
ON DELETE NO ACTION
ON UPDATE NO ACTION, 

        CONSTRAINT "tblClaim_FK09"
        FOREIGN KEY (CL_IDOR) REFERENCES tlkpOverpaymentReason(OR_ID) 
ON DELETE NO ACTION
ON UPDATE NO ACTION, 

        CONSTRAINT "tblClaim_FK10"
        FOREIGN KEY (CL_IDOS) REFERENCES tlkpOverpaymentStatus(OS_ID) 
ON DELETE NO ACTION
ON UPDATE NO ACTION, 

        CONSTRAINT "tblClaim_FK11"
        FOREIGN KEY (CL_IDPPT) REFERENCES tmmPolicyProductType(PPT_ID) 
ON DELETE NO ACTION
ON UPDATE NO ACTION
ALTER TABLE tblClaim
CHECK CONSTRAINT "tblClaim_FK00"
ALTER TABLE tblClaim
CHECK CONSTRAINT "tblClaim_FK01"
ALTER TABLE tblClaim
CHECK CONSTRAINT "tblClaim_FK02"
ALTER TABLE tblClaim
CHECK CONSTRAINT "tblClaim_FK03"
ALTER TABLE tblClaim
CHECK CONSTRAINT "tblClaim_FK04"
ALTER TABLE tblClaim
CHECK CONSTRAINT "tblClaim_FK05"
ALTER TABLE tblClaim
CHECK CONSTRAINT "tblClaim_FK06"
ALTER TABLE tblClaim
CHECK CONSTRAINT "tblClaim_FK07"
ALTER TABLE tblClaim
CHECK CONSTRAINT "tblClaim_FK08"
ALTER TABLE tblClaim
CHECK CONSTRAINT "tblClaim_FK09"
ALTER TABLE tblClaim
CHECK CONSTRAINT "tblClaim_FK10"
ALTER TABLE tblClaim
CHECK CONSTRAINT "tblClaim_FK11" 


John W. Colby
www.ColbyConsulting.com 

Contribute your unused CPU cycles to a good cause:
http://folding.stanford.edu/
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Thursday, December 22, 2005 12:02 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Upsize errors

On 21 Dec 2005 at 23:54, John Colby wrote:

> I am getting a TON of these kinds of errors.
> 
> CREATE UNIQUE INDEX ER_Reason ON tlkpEventReason(ER_Reason)
> 
> Server Error 1505: CREATE UNIQUE INDEX terminated because a duplicate 
> key was found for index ID 3. Most significant primary key is 
> '<NULL>'. Server Error 3621: The statement has been terminated.
> 
> I am confused as to how Access could have a unique index that SQL 
> Server is finding a duplicate key for.  I have not gone looking yet.
> 

Was the index in Access on a field that had 'Required" set to "No". or was
the index designed with "Ignore Nulls" set to "Yes"?
In either case, you could have Null records in the index and that appears to
be what is causing the problem based on the above message.

 
-- 
Stuart


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list