Newbie Constraint Setting Problem
1 posts in topic
Flat View  Flat View
TOPIC ACTIONS:
 

Posted By:   asad_razvi
Posted On:   Thursday, December 6, 2001 05:44 PM

Hiya People ! I have problems when I execute the queries below. I am using Access 2000, the JdbcOdbc Driver and WinME. I have also listed the specific error codes that I got. I am really confused and would really appreciate any help that could be offered. ALTER TABLE CoffeePurchase ADD CONSTRAINT fk_purchase_id FOREIGN KEY (PurchaseID) REFERENCES Purchase (PurchaseID); ALTER TABLE Purchase ADD CONSTRAINT ch_purchase_paymethod CHECK (PayMethod IN('visa', 'amex', 'switch', 'solo', 'electron',   More>>

Hiya People !


I have problems when I execute the

queries below. I am using Access 2000,

the JdbcOdbc Driver and WinME.


I have also listed the specific error codes

that I got. I am really confused and would

really appreciate any help that could be

offered.



ALTER TABLE CoffeePurchase

ADD CONSTRAINT fk_purchase_id

FOREIGN KEY (PurchaseID)

REFERENCES Purchase (PurchaseID);



ALTER TABLE Purchase

ADD CONSTRAINT ch_purchase_paymethod

CHECK (PayMethod IN('visa', 'amex', 'switch',

'solo', 'electron', 'mastercard'));



SQLState : 37000

Message : [Microsoft][ODBC Microsoft Access Driver] Syntax error in CONSTRAINT clause.

Code : -3550



SQLState : 37000

Message : [Microsoft][ODBC Microsoft Access Driver] Syntax error in CONSTRAINT clause.

Code : -3550



regards, A.    <<Less

Re: Newbie Constraint Setting Problem

Posted By:   Bernie_Acs  
Posted On:   Friday, December 7, 2001 03:25 PM

  • Query #1 is okay as demo'd by the snipplet of code which worked for me ( I would assume an extranious character or missing space in the string construction..
  • Query #2 on the other hand will not be executeable
    Constraints indicates that the syntax being issued is correct but further points out some limitations in the note section following the sample. The check constraint statement can only be executed through the Jet OLE DB provider and ADO



    my working method sample


    public static void someWork(java.sql.Statement statmnt){
    try{
    statmnt.execute("Alter Table table3 drop CONSTRAINT fk_t12t2_id;" );
    System.out.println( " dropped constraint " );
    } catch ( java.sql.SQLException e) {
    if (! e.getMessage().endsWith("does not exist") ) {
    e.printStackTrace();
    return;
    }
    }
    try{

    statmnt.execute("Alter Table table3 add CONSTRAINT fk_t12t2_id "+
    "FOREIGN KEY ( ID ) references table2 ( ID );" ) ;
    System.out.println( " added FK constraint " );

    /*
    ** The following statement breaks using this driver
    */
    statmnt.execute(
    "ALTER TABLE table2 ADD CONSTRAINT ch_clo2 CHECK "+
    " (col2 IN ('visa', 'amex', 'switch','solo', 'electron', 'mastercard'));"
    );
    System.out.println( " added CK constraint " );

    } catch ( java.sql.SQLException e) {
    e.printStackTrace();
    }

    }
  • About | Sitemap | Contact