Board index » delphi » pleassssssssssssssse help interbase foreign key

isaac s. sam

Delphi Developer
Sat, 02 Jul 2005 09:23:46 GMT

pleassssssssssssssse help interbase foreign key

i have a strange problem , i create a master table and it is sql are:

/* Table: CITIES */

SET SQL DIALECT 3;

SET NAMES NONE;

/******************************************************************************/
/*                                   Tables                                   */
/******************************************************************************/

CREATE TABLE CITIES (
    CITY_CODE  VARCHAR(6) CHARACTER SET WIN1252 NOT NULL,
    CITY_NAME  VARCHAR(35) CHARACTER SET WIN1252 NOT NULL
);

/******************************************************************************/
/*                                Primary Keys                                */
/******************************************************************************/

ALTER TABLE CITIES ADD CONSTRAINT PK_CITIES PRIMARY KEY (CITY_CODE);

and detail table :

/* Table: CITY_COST_PRICE */

SET SQL DIALECT 3;

SET NAMES NONE;

/******************************************************************************/
/*                                   Tables                                   */
/******************************************************************************/

CREATE TABLE CITY_COST_PRICE (
    CITY_CODE      VARCHAR(6) CHARACTER SET WIN1252 NOT NULL,
    PACKAGE_PRICE  FLOAT,
    FILL_PRICE     FLOAT
);

/******************************************************************************/
/*                                Primary Keys                                */
/******************************************************************************/

ALTER TABLE CITY_COST_PRICE ADD CONSTRAINT PK_CITY_COST_PRICE PRIMARY KEY (CITY_CODE);

/******************************************************************************/
/*                                Foreign Keys                                */
/******************************************************************************/

ALTER TABLE CITY_COST_PRICE ADD CONSTRAINT FK_CITY_COST_PRICE FOREIGN KEY (CITY_CODE) REFERENCES CITIES (CITY_CODE) ON DELETE CASCADE ON UPDATE CASCADE;

but every time i write data into detail table i got constraint violation error message

please tell me what to do

thanks
isaac

 
Bria

Delphi Developer
Sat, 02 Jul 2005 14:58:15 GMT

Re:pleassssssssssssssse help interbase foreign key


"isaac s. sam" <isaac...@yahoo.com> wrote:

Quote

>i have a strange problem , i create a master table and it is sql are:

>/* Table: CITIES */

>SET SQL DIALECT 3;

>SET NAMES NONE;

>/******************************************************************************/
>/*                                   Tables                                   */
>/******************************************************************************/

>CREATE TABLE CITIES (
>    CITY_CODE  VARCHAR(6) CHARACTER SET WIN1252 NOT NULL,
>    CITY_NAME  VARCHAR(35) CHARACTER SET WIN1252 NOT NULL
>);

>/******************************************************************************/
>/*                                Primary Keys                                */
>/******************************************************************************/

>ALTER TABLE CITIES ADD CONSTRAINT PK_CITIES PRIMARY KEY (CITY_CODE);

>and detail table :

>/* Table: CITY_COST_PRICE */

>SET SQL DIALECT 3;

>SET NAMES NONE;

>/******************************************************************************/
>/*                                   Tables                                   */
>/******************************************************************************/

>CREATE TABLE CITY_COST_PRICE (
>    CITY_CODE      VARCHAR(6) CHARACTER SET WIN1252 NOT NULL,
>    PACKAGE_PRICE  FLOAT,
>    FILL_PRICE     FLOAT
>);

>/******************************************************************************/
>/*                                Primary Keys                                */
>/******************************************************************************/

>ALTER TABLE CITY_COST_PRICE ADD CONSTRAINT PK_CITY_COST_PRICE PRIMARY KEY (CITY_CODE);

>/******************************************************************************/
>/*                                Foreign Keys                                */
>/******************************************************************************/

>ALTER TABLE CITY_COST_PRICE ADD CONSTRAINT FK_CITY_COST_PRICE FOREIGN KEY (CITY_CODE) REFERENCES CITIES (CITY_CODE) ON DELETE CASCADE ON UPDATE CASCADE;

>but every time i write data into detail table i got constraint violation error message

>please tell me what to do

>thanks
>isaac

Since the specified primary key on table CITY_COST_PRICE is the
same as the primary key of table CITIES, it seems that there is
going to be a one-to-one relationship between the two tables,
in which case you can create only one table with all the fields!

Brian

Martijn Tonie

Delphi Developer
Sat, 02 Jul 2005 15:23:15 GMT

Re:pleassssssssssssssse help interbase foreign key


Could you show us the INSERT statements? You do have data in your
master table?

--

With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase & Firebird
Firebird Workbench - the developer tool for Firebird
Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."

Quote

> i have a strange problem , i create a master table and it is sql are:

> /* Table: CITIES */

> SET SQL DIALECT 3;

> SET NAMES NONE;

/******************************************************************************/
Quote
> /*                                   Tables
*/

/******************************************************************************/
Quote

> CREATE TABLE CITIES (
>     CITY_CODE  VARCHAR(6) CHARACTER SET WIN1252 NOT NULL,
>     CITY_NAME  VARCHAR(35) CHARACTER SET WIN1252 NOT NULL
> );

isaac s. sa

Delphi Developer
Sat, 02 Jul 2005 18:18:55 GMT

Re:pleassssssssssssssse help interbase foreign key


Quote
"Martijn Tonies" <m.tonies@upscene_remove.com> wrote:
>Could you show us the INSERT statements? You do have data in your
>master table?

>--

>With regards,

>Martijn Tonies
>InterBase Workbench - the developer tool for InterBase & Firebird
>Firebird Workbench - the developer tool for Firebird
>Upscene Productions
>http://www.upscene.com

>"This is an object-oriented system.
>If we change anything, the users object."

>> i have a strange problem , i create a master table and it is sql are:

>> /* Table: CITIES */

>> SET SQL DIALECT 3;

>> SET NAMES NONE;

>/******************************************************************************/
>> /*                                   Tables
>*/

>/******************************************************************************/

>> CREATE TABLE CITIES (
>>     CITY_CODE  VARCHAR(6) CHARACTER SET WIN1252 NOT NULL,
>>     CITY_NAME  VARCHAR(35) CHARACTER SET WIN1252 NOT NULL
>> );

i need one general table to lookup the data from it
and yes i have data in it also i select the exist record

is there an possibility it is a language problem

Martijn Tonie

Delphi Developer
Sat, 02 Jul 2005 18:51:29 GMT

Re:pleassssssssssssssse help interbase foreign key


Quote
> >> i have a strange problem , i create a master table and it is sql are:

> >> /* Table: CITIES */

> >> SET SQL DIALECT 3;

> >> SET NAMES NONE;

>/******************************************************************************
/
> >> /*                                   Tables
> >*/

>/******************************************************************************
/

> >> CREATE TABLE CITIES (
> >>     CITY_CODE  VARCHAR(6) CHARACTER SET WIN1252 NOT NULL,
> >>     CITY_NAME  VARCHAR(35) CHARACTER SET WIN1252 NOT NULL
> >> );

> i need one general table to lookup the data from it
> and yes i have data in it also i select the exist record

> is there an possibility it is a language problem

What is your exact error message?

--

With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase & Firebird
Firebird Workbench - the developer tool for Firebird
Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."

isaac s. sa

Delphi Developer
Sat, 02 Jul 2005 20:18:23 GMT

Re:pleassssssssssssssse help interbase foreign key


Quote
"Martijn Tonies" <m.tonies@upscene_remove.com> wrote:

>> >> i have a strange problem , i create a master table and it is sql are:

>> >> /* Table: CITIES */

>> >> SET SQL DIALECT 3;

>> >> SET NAMES NONE;

>>/******************************************************************************
>/
>> >> /*                                   Tables
>> >*/

>>/******************************************************************************
>/

>> >> CREATE TABLE CITIES (
>> >>     CITY_CODE  VARCHAR(6) CHARACTER SET WIN1252 NOT NULL,
>> >>     CITY_NAME  VARCHAR(35) CHARACTER SET WIN1252 NOT NULL
>> >> );

>> i need one general table to lookup the data from it
>> and yes i have data in it also i select the exist record

>> is there an possibility it is a language problem

>What is your exact error message?

>--

>With regards,

>Martijn Tonies
>InterBase Workbench - the developer tool for InterBase & Firebird
>Firebird Workbench - the developer tool for Firebird
>Upscene Productions
>http://www.upscene.com

>"This is an object-oriented system.
>If we change anything, the users object."

the error message is "violation of foreign key constraint"
 and this problem only occure if the field id varchar if i use the integer type i have no problem but i need varchar becuase the data contain phone codes like 012, 035 and so on
Martijn Tonie

Delphi Developer
Sat, 02 Jul 2005 20:32:20 GMT

Re:pleassssssssssssssse help interbase foreign key


Quote
> >> i need one general table to lookup the data from it
> >> and yes i have data in it also i select the exist record

> >> is there an possibility it is a language problem

> >What is your exact error message?

> the error message is "violation of foreign key constraint"
>  and this problem only occure if the field id varchar if i use the integer

type i have no problem but i need varchar becuase the data >contain phone codes
like 012, 035 and so on

I remember such an error - don't know how (and if) I solved it... :-/

Does changing to CHAR help? Do you connect with the WIN1252 charset?

--

With regards,

Martijn Tonies
InterBase Workbench - the developer tool for InterBase & Firebird
Firebird Workbench - the developer tool for Firebird
Upscene Productions
http://www.upscene.com

"This is an object-oriented system.
If we change anything, the users object."

Other Threads

1. About FOREIGN KEY WITH UNIQUE in INTERBASE

2. Interbase:Foreign key: null value

3. Interbase Foreign Key question

4. Interbase: Foreign Key

5. Foreign key and Interbase 5.0

6. Interbase Indexes on Primary and Foreign keys.

7. Define foreign key failure, Interbase v 6

8. Problems with Interbase 6.0 and FOREIGN KEY ...

9. Modify Key Field ( when it's foreign key in another table)

10. Foreign keys and Secondary keys