Solved

SQL Query: ON UPDATE CASCADE

Posted on 2000-02-16
17
928 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

911 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

21 Experts available now in Live!

Get 1:1 Help Now