Solved

SQL Query: ON UPDATE CASCADE

Posted on 2000-02-16
17
954 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
  • 7
  • 6
  • 3
  • +1
17 Comments
 
LVL 57
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 57
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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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 57
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 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