| Bug #24505 | Problem with FOREIGN KEY Constraint creation for varchar datatype | ||
|---|---|---|---|
| Submitted: | 22 Nov 2006 13:22 | Modified: | 23 Dec 2006 14:58 |
| Reporter: | Hari A | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 5.0.11 | OS: | Windows (Windows 2000 server) |
| Assigned to: | CPU Architecture: | Any | |
[22 Nov 2006 13:37]
Valeriy Kravchuk
Thank you for a problem report. Please, try to repeat with a newer version (5.0.27 or 5.1.12) and, in case of similar problem, send exact CREATE TABLE statements used to create tables initially.
[23 Nov 2006 5:55]
Hari A
Hi,
Thank you for your response,now I am sending the Table structure.
Table 1:icdcodes (This is the Master Table)
CREATE TABLE `sw_icdcodes` (
`ICDCode` varchar(20) NOT NULL default '0',
`Descr` varchar(255) default NULL,
`SDescr` varchar(255) NOT NULL default '' COMMENT 'PARENT CODE',
`PrntCode` varchar(20) default NULL,
PRIMARY KEY (`ICDCode`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 10240 kB; InnoDB free: 9216 kB';
Table 2:sw_claims(Detail table)
--------
CREATE TABLE `sw_claims` (
`ClId` int(10) NOT NULL auto_increment,
`ClmId` int(10) NOT NULL default '0' COMMENT 'Claim ID',
`Id` int(10) NOT NULL default '0' COMMENT 'Enrollment ID',
`PrvId` int(10) NOT NULL default '0' COMMENT 'Provider ID',
`BenClsId` int(10) NOT NULL default '0' COMMENT 'Benfit ClassificationID',
`MedSrvId` int(10) NOT NULL default '0' COMMENT 'Medical Service ID',
`CPTCode` varchar(20) default NULL COMMENT 'Current Procedure Terminology Code',
`LocSrvId` int(10) NOT NULL default '0' COMMENT 'Location Service ID / Service Location ID',
`UsrId` int(11) NOT NULL default '0' COMMENT 'User ID',
`ClmDt` date default NULL COMMENT 'Claim Date',
`BldChrg` decimal(15,2) NOT NULL default '0.00' COMMENT 'Billed Charges',
`ElgChrg` decimal(15,2) NOT NULL default '0.00' COMMENT 'Eligible Charges',
`LocId` int(10) NOT NULL default '0' COMMENT 'Location ID',
`HpClsId` int(10) NOT NULL default '0' COMMENT 'Health Plan Classification ID',
`Deduc` decimal(15,2) NOT NULL default '0.00' COMMENT 'Deductible',
`Copay` decimal(15,2) NOT NULL default '0.00' COMMENT 'Co-pay',
`COB` decimal(15,2) NOT NULL default '0.00' COMMENT 'Co-ordination of Benefits',
`DOSSt` date default NULL COMMENT 'Start date of service',
`DOSEnd` date default NULL COMMENT 'End date of service',
`MemId` int(10) NOT NULL default '0' COMMENT 'Member ID',
`AdmsDt` date default NULL COMMENT 'Admission Date',
`DisChrgDt` date default NULL COMMENT 'Discharge Date',
`NwDisc` decimal(15,2) default NULL COMMENT 'Network Discount',
`EmpPay` decimal(15,2) default NULL COMMENT 'Employee Pay',
`ClaimPaid` decimal(15,2) default NULL,
`EmprPay` decimal(15,2) default NULL COMMENT 'Employer Pay',
`ClmTypeId` int(10) unsigned default NULL COMMENT 'Claim Type ID',
`Specialty` varchar(45) NOT NULL default '' COMMENT 'Specialty',
`UandCChrg` decimal(10,0) NOT NULL default '0' COMMENT 'Usual and Customary Charges',
`NDC` varchar(45) NOT NULL default '' COMMENT 'Drug Code',
`RxName` varchar(45) NOT NULL default '' COMMENT 'Name of the Drug',
`RxPaid` decimal(10,0) NOT NULL default '0' COMMENT 'Paid for Drug',
`ICDCode` varchar(20) NOT NULL,
PRIMARY KEY (`ClId`),
KEY `Ref_19` (`CPTCode`),
KEY `Ref_21` (`Id`),
KEY `Ref_22` (`BenClsId`),
KEY `Ref_35` (`MedSrvId`),
KEY `Ref_36` (`LocSrvId`),
KEY `Ref_30` (`PrvId`),
KEY `Ref_18` (`ICDCode`),
CONSTRAINT `Ref_20` FOREIGN KEY (`Id`) REFERENCES `sw_enrollinfo` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `Ref_21` FOREIGN KEY (`Id`) REFERENCES `sw_enrollinfo` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `Ref_22` FOREIGN KEY (`BenClsId`) REFERENCES `sw_benclf` (`BenClsId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `Ref_30` FOREIGN KEY (`PrvId`) REFERENCES `sw_provider` (`PrvId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `Ref_35` FOREIGN KEY (`MedSrvId`) REFERENCES `sw_medsrv` (`MedSrvId`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `Ref_36` FOREIGN KEY (`LocSrvId`) REFERENCES `sw_locofsrv` (`LocSrvId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 10240 kB; (`BENCLASSID`) REFER `sweeper/sw_ben_';
Now I am trying to add FOREIGN KEY on ICDCode(sw_claims) REFERENCES (sw_icdcodes),
I am getting this error
ALTER TABLE `sweepertest`.`sw_claims` ADD CONSTRAINT `FK_sw_claims_7` FOREIGN KEY `FK_sw_claims_7` (`ICDCode`)
REFERENCES `sw_icdcodes` (`ICDCode`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;
MySQL Error Number 1452
Cannot add or update a child row: a foreign key constraint fails (`sweepertest/#sql-260_2216`, CONSTRAINT `FK_sw_claims_7` FOREIGN KEY (`ICDCode`) REFERENCES `sw_icdcodes` (`ICDCode`))
Thanks
Hari
[23 Nov 2006 9:49]
Sveta Smirnova
CREATE TABLE `sw_claims` statement contains mistake. Please provide output of SHOW CREATE TABLE `sw_claims`.
[23 Nov 2006 10:13]
Hari A
Hi, SHOW CREATE TABLE `sw_claims` --------------------------- CREATE TABLE `sw_claims` ( `ClId` int(10) NOT NULL auto_increment, `ClmId` int(10) NOT NULL default '0' COMMENT 'Claim ID', `Id` int(10) NOT NULL default '0' COMMENT 'Enrollment ID', `PrvId` int(10) NOT NULL default '0' COMMENT 'Provider ID', `BenClsId` int(10) NOT NULL default '0' COMMENT 'Benfit ClassificationID', `MedSrvId` int(10) NOT NULL default '0' COMMENT 'Medical Service ID', `CPTCode` varchar(20) default NULL COMMENT 'Current Procedure Terminology Code', `LocSrvId` int(10) NOT NULL default '0' COMMENT 'Location Service ID / Service Location ID', `UsrId` int(11) NOT NULL default '0' COMMENT 'User ID', `ClmDt` date default NULL COMMENT 'Claim Date', `BldChrg` decimal(15,2) NOT NULL default '0.00' COMMENT 'Billed Charges', `ElgChrg` decimal(15,2) NOT NULL default '0.00' COMMENT 'Eligible Charges', `LocId` int(10) NOT NULL default '0' COMMENT 'Location ID', `HpClsId` int(10) NOT NULL default '0' COMMENT 'Health Plan Classification ID', `Deduc` decimal(15,2) NOT NULL default '0.00' COMMENT 'Deductible', `Copay` decimal(15,2) NOT NULL default '0.00' COMMENT 'Co-pay', `COB` decimal(15,2) NOT NULL default '0.00' COMMENT 'Co-ordination of Benefits', `DOSSt` date default NULL COMMENT 'Start date of service', `DOSEnd` date default NULL COMMENT 'End date of service', `MemId` int(10) NOT NULL default '0' COMMENT 'Member ID', `AdmsDt` date default NULL COMMENT 'Admission Date', `DisChrgDt` date default NULL COMMENT 'Discharge Date', `NwDisc` decimal(15,2) default NULL COMMENT 'Network Discount', `EmpPay` decimal(15,2) default NULL COMMENT 'Employee Pay', `ClaimPaid` decimal(15,2) default NULL, `EmprPay` decimal(15,2) default NULL COMMENT 'Employer Pay', `ClmTypeId` int(10) unsigned default NULL COMMENT 'Claim Type ID', `Specialty` varchar(45) NOT NULL default '' COMMENT 'Specialty', `UandCChrg` decimal(10,0) NOT NULL default '0' COMMENT 'Usual and Customary Charges', `NDC` varchar(45) NOT NULL default '' COMMENT 'Drug Code', `RxName` varchar(45) NOT NULL default '' COMMENT 'Name of the Drug', `RxPaid` decimal(10,0) NOT NULL default '0' COMMENT 'Paid for Drug', `ICDCode` varchar(20) NOT NULL, PRIMARY KEY (`ClId`), KEY `Ref_19` (`CPTCode`), KEY `Ref_21` (`Id`), KEY `Ref_22` (`BenClsId`), KEY `Ref_35` (`MedSrvId`), KEY `Ref_36` (`LocSrvId`), KEY `Ref_30` (`PrvId`), KEY `Ref_18` (`ICDCode`), KEY `ICDCode` (`ICDCode`), CONSTRAINT `Ref_20` FOREIGN KEY (`Id`) REFERENCES `sw_enrollinfo` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `Ref_21` FOREIGN KEY (`Id`) REFERENCES `sw_enrollinfo` (`Id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `Ref_22` FOREIGN KEY (`BenClsId`) REFERENCES `sw_benclf` (`BenClsId`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `Ref_30` FOREIGN KEY (`PrvId`) REFERENCES `sw_provider` (`PrvId`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `Ref_35` FOREIGN KEY (`MedSrvId`) REFERENCES `sw_medsrv` (`MedSrvId`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `Ref_36` FOREIGN KEY (`LocSrvId`) REFERENCES `sw_locofsrv` (`LocSrvId`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='InnoDB free: 10240 kB; (`BENCLASSID`) REFER `sweeper/sw_ben_'
[23 Nov 2006 14:58]
Valeriy Kravchuk
Please, try to repeat with a newer version (5.0.27 or 5.1.12) and, in case of similar problem, send also exact CREATE TABLE statements for tables refertenced by FOREIGN KEYs. We need a complete and repeatable test case.
[24 Dec 2006 0:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[31 Jan 2008 6:26]
sanjeev Rao
I think that problem is not with th esyntactically.
While you putting a foreign key on a particular column you need to verify the data in both the columns.
Example:
i hav a table "parent" with column "childid"
table "child" with column "id"
So i want foreign key relation here::(assume "childid" has the data like 1,2,3
and "id" has no values)
So, now if i try to foreign key relation it'll raise error.
So now u insert same child id values into "id" of child table and then try....
......
SANJAY...
Description: Hi , I have two Tables Table1:Icdcodes --------- ICDCode Varchar(20)- P Descr Varchar(200) Table2: Claims -------------- Id int(p) ICDCode Varchar(20) I am trying add FOREIGN KEY Constraint on Claims(ICDCode ) then i will get the error like this ----------------------------------- ALTER TABLE `sweepertest`.`sw_claims` ADD CONSTRAINT `FK_sw_ICDCode` FOREIGN KEY `FK_sw_ICDCode` (`ICDCode`) REFERENCES `sw_icdcodes` (`ICDCode`) ON DELETE RESTRICT ON UPDATE RESTRICT; You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[CONSTRAINT FK_ICDCodes] FOREIGN KEY [FK_ICDCodes] (ICDCode) REFERENCES sw_' at line 2 ------------------------------------ Thanks Hari How to repeat: Problem with FOREIGN KEY Constraint creation for varchar datatype