Partial Transactions in MYSQL

--0-742742391-1102312722=:67015
Content-Type: text/plain; charset=us-ascii

Dear All,

I have problem with partial transactions. I am using MYSQL.NET data provider(MySQL Connector Net 1.0.2) for 1.1 .net framework. the database version is 5.02 . the table structure is like this


CREATE TABLE CLIENT_MAIN

(

IR_CLIENT_ID INT NOT NULL,

CLIENT_USERID VARCHAR(20) NOT NULL,

CLIENT_PASSWORD VARCHAR(20) NOT NULL,

REGISTRATION_DATE DATE NULL,

REGISTRATION_EXPIRY_DATE DATE NULL,

COMPANY_ID VARCHAR(20) NOT NULL,

MANAGER_ID INT NULL,

PRIMARY KEY (IR_CLIENT_ID)

) TYPE = INNODB;







CREATE TABLE CLIENT_PERSONAL_DATA

(

PERSON_ID INT NOT NULL ,

IR_CLIENT_ID INT NOT NULL ,

PERSON_DATA_TYPE VARCHAR(10) NOT NULL,

TITLE VARCHAR(5) NULL,

LAST_NAME VARCHAR(50) NULL,

FIRST_NAME VARCHAR(50) NULL,

MIDDLE_INITIAL VARCHAR(1) NULL,

MARITAL_STATUS VARCHAR(10) NULL,

DATE_OF_BIRTH DATE NULL,

SEX VARCHAR(10) NULL,

EMAIL VARCHAR(50) NULL,

WORK_PHONE_NUMBER VARCHAR(15) NULL,

HOME_PHONE_NUMBER VARCHAR(15) NULL,

CELL_PHONE_NUMBER VARCHAR(15) NULL,

ADDRESS_1 VARCHAR(80) NULL,

ADDRESS_2 VARCHAR(80) NULL,

STATE VARCHAR(20) NULL,

ZIP VARCHAR(20) NULL,

CITY VARCHAR(20) NULL,

SOCIAL_SECURITY_NUMBER VARCHAR(12) NULL,

CLIENT_STATUS VARCHAR(20) NULL ,

DATE_CREATED DATE,

DATE_LAST_MODIFIED DATE,

PRIMARY KEY(PERSON_ID),

INDEX (IR_CLIENT_ID),

FOREIGN KEY (IR_CLIENT_ID) REFERENCES CLIENT_MAIN (IR_CLIENT_ID)

)TYPE = INNODB;





i am using another table called

CREATE TABLE PRIMARY_KEY_GENERATOR

(

TABLE_ID VARCHAR(80) NOT NULL,

LAST_NUMBER INT NOT NULL,

PRIMARY KEY(TABLE_ID)

) TYPE = INNODB; to strore primary keys in this table.



the operation is insert. first i insert into the master table client_main and then insert a row in client_personal_data. during insert i get an error

cannot add or update . foreign key constraint error #23000. for primary key generation commit is explicticly called in the code where as for other tables it is one time commit means the data inserts in two tables .

can anybody resolve this . i am new to mysql . i have tested the same operation on sqlserver 2000 and oracle 9.2 which works peacefully .







---------------------------------
Do you Yahoo!?
The all-new My Yahoo! – Get yours free!
--0-742742391-1102312722=:67015--