Solved

SQL Query: ON UPDATE CASCADE

Posted on 2000-02-16
17
974 Views
Last Modified: 2011-10-03
it seems Access doesn't like:
"CREATE TABLE tbl_Style (ID AUTOINCREMENT CONSTRAINT bla PRIMARY KEY, Header1 INTEGER CONSTRAINT C1 REFERENCES tbl_Logo (ID) ON UPDATE CASCADE)"

It just gives me the 'error in create statement' and points to the update keyword. (same thing with on delete cascade).

How do I do this? some other keyword in stead of UPDATE?
thanks bunch
0
Comment
Question by:niven
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 3
  • +1
17 Comments
 
LVL 58
ID: 2528978
The short answer is no, there is no other keyword.  JET's version of SQL is not the same in many respects to ANSI SQL.  

  It's been getting closer and closer with each release, but it's still not totally there yet.

  You didn't mention version, so I can't be more specific then that.

JimD.
0
 
LVL 58
ID: 2528980
Just one additional note: if you using A2000 with a adp project then it's a whole different story.

JimD.
0
 

Author Comment

by:niven
ID: 2529082
I use Access 2000
(bunch 'O SPs installed of course, winnt5)
no adp project (whatever that is, just sql)
(which is being generated by another app.)
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 7

Expert Comment

by:Dedushka
ID: 2529086
Hi,
some info from online Help:
-----------------------
This example creates a new table with two text fields and an Integer field. The SSN field is the primary key.

Sub CreateTableX3()
     Dim dbs As Database

    ' Modify this line to include the path to Northwind
    ' on your computer.

    Set dbs = OpenDatabase("Northwind.mdb")

    ' Create a table with three fields and a primary
    ' key.

    dbs.Execute "CREATE TABLE NewTable " _
        & "(FirstName CHAR, LastName CHAR, " _
        & "SSN INTEGER CONSTRAINT MyFieldConstraint " _
        & "PRIMARY KEY);"

    dbs.Close

End Sub

Syntax for constraint:

CONSTRAINT name
    {PRIMARY KEY (primary1[, primary2 [, ...]]) |
    UNIQUE (unique1[, unique2 [, ...]]) |
    NOT NULL (notnull1[, notnull2 [, ...]]) |
    FOREIGN KEY [NO INDEX] (ref1[, ref2 [, ...]]) REFERENCES foreigntable [(foreignfield1 [, foreignfield2 [, ...]])]
    [ON UPDATE CASCADE | SET NULL]
    [ON DELETE CASCADE | SET NULL]}

Consider the following definition of the table Orders, which defines a foreign key relationship referencing the primary key of the Customers table:

CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) REFERENCES Customers ON UPDATE CASCADE ON DELETE CASCADE
-----------------------------
Now, instead of

"CREATE TABLE tbl_Style (ID AUTOINCREMENT CONSTRAINT bla PRIMARY KEY, Header1 INTEGER CONSTRAINT C1 REFERENCES tbl_Logo (ID) ON UPDATE CASCADE)"

try to use:

CREATE TABLE tbl_Style (ID AUTOINCREMENT PRIMARY KEY, Header1 INTEGER, CONSTRAINT C1 FOREIGN KEY (ID) REFERENCES  tbl_Logo (ID) ON UPDATE CASCADE);

HTH,
Dedushka
0
 

Author Comment

by:niven
ID: 2529111
tried that, exactly the same error :(
(error in create, update is highlighted)

Does that work with your version of access btw?
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2529137
Just a moment, I'll try to test it...
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2529245
First of all you want to have one-to-many relationship from tbl_Logo (where ID is primary key) to tbl_Style, so your ID field in tbl_Style can not be Autonumber and unique, it must be LONG and Foreign key, so use this:

Sub CreateTableX3()
     Dim dbs As Database

    ' Modify this line to include the path to Northwind
    ' on your computer.

    Set dbs = OpenDatabase("Northwind.mdb")

    ' Create a table
    dbs.Execute "CREATE TABLE tbl_Style " _
    & "(ID LONG CONSTRAINT C1 REFERENCES tbl_Logo, " _
    & "Header1 INTEGER);"

    dbs.Close

End Sub

The procedure creates tbl_Style and set relationship, I tested it. But unfortunately, when I try to use ON UPDATE CASCADE, I get an error. Maybe we need to create a table and then use ALTER for establish Cascade update/delete?
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2529302
ALTER TABLE  also don't recognize ON UPDATE CASCADE, so I can propose to Use the DAO CreateRelation method instead.

Here is sample from Acc2000 online Help:
-------------
CreateRelation Method Example (MDB)

The following example creates a new Relation object that defines a relationship between a Categories table and a Products table. The Categories table is the primary table in the relationship, and the Products table is the foreign table. The CategoryID field is the primary key in the Categories table, and a foreign key in the Products table.

To test this example in the Northwind sample database, click Relationships on the Tools menu, and delete the relationship between the Categories table and the Products table. Close the Relationships window, saving the current configuration at the prompt. Run the following procedure. Then view the Relationships window again to see the new relationship.

Sub NewRelation()
    Dim dbs As Database, rel As Relation, fld As Field
   
    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Create new Relation object and specify foreign table.
    Set rel = dbs.CreateRelation("CategoryProducts", _
        "Categories", "Products")
    ' Set attributes to enforce referential integrity.
    rel.Attributes = dbRelationUpdateCascade And _
        dbRelationDeleteCascade
    ' Create field in Relation object.
    Set fld = rel.CreateField("CategoryID")
    ' Specify field name in foreign table.
    fld.ForeignName = "CategoryID"
    ' Append Field object to Fields collection of Relation object.
    rel.Fields.Append fld
    ' Append Relation object to Relations collection.
    dbs.Relations.Append rel
    dbs.Relations.Refresh
    Set dbs = Nothing
End Sub
--------

Best regards,
Dedushka
0
 

Author Comment

by:niven
ID: 2529567
That might be the problem, the createmethod works, but not when you write it out in an sql statement,which is (unfortunately) what I need...
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2529618
Can I do something else for you?
0
 

Author Comment

by:niven
ID: 2530663
Well, I'd like to know if Access2000 just doesn't support ansi-sql and isn't able to execute the 'on update' statements,
Or if it can be done, and if so, then how?

Frankly, I'm becoming pretty fed up with Access,
might just swith to sql-server...

Thanks anyway
0
 
LVL 7

Expert Comment

by:Dedushka
ID: 2530739
It seems that Acc2000 don't support ON UPDATE in SQL statement. I've tried several different ways without success. Certainly you can open relationships window and manually set reference integrity constrains for cascade update and cascade delete.
BTW I never use in code data definition language (DDL)statements. All DB designing tasks (table and relationships defining, setting all restriction and constraints, etc.) I perform through database window.
Are you absolutely need to set ON UPDATE CASCADE using DDL?

Thank you for good question,
best regards,
Dedushka.
0
 

Author Comment

by:niven
ID: 2530752
I'm afraid so, our application generates tables dynamically, we don't really want to tell the users to manually set relationships :)

But it can be done in sql-server I believe,
and we're going to switch to that anyway,
it would have been nice in access...

Thanks again,
regards,
niven
0
 
LVL 7

Accepted Solution

by:
Dedushka earned 350 total points
ID: 2531026
OK,
I'll post here additional info if I found new one.
If you want to grade me this points for my hard efforts, I'll take it with greate pleasure :-)))
Have a nice day,
Dedushka
0
 
LVL 58
ID: 2531441
Niven,

  As I said right off the bat, the keyword is not supported in the SQL statement.  If you want to create table relationships dynamically, then you'll have to use the DAO method.

JimD.
0
 

Author Comment

by:niven
ID: 2531602
Thanks,
I'll now go and whine to M$ :)
0
 

Expert Comment

by:LStampfli
ID: 13774947
I know that this is an old post, but if you are searching and using it like I am, you might like to know that there is an error in the code sample written above.  If used as is, it will create the relation, but NOT the cascade delete option.  The line which reads:

   rel.Attributes = dbRelationUpdateCascade And _
        dbRelationDeleteCascade

Should read:

   rel.Attributes = dbRelationUpdateCascade + _
        dbRelationDeleteCascade

If you make this change, the example will do what it is supposed to do.

It is certainly quite lame that Access SQL does not support the SQL keyword ON DELETE CASCADE.

Lisa S
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question