Solved

SQL Query: ON UPDATE CASCADE

Posted on 2000-02-16
17
925 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now