Results 1 to 3 of 3
  1. #1
    Bazsl is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jun 2016
    Posts
    47

    ON UPDATE CASCADE error

    Can anyone tell me what is wrong with the following DDL statement? I get the error "Syntax error in CONSTRAINT clause." with the word UPDATE highlighted. Thanks.



    ALTER TABLE [Checkout Untagged]
    ADD CONSTRAINT fk_Customer
    FOREIGN KEY ([Customer Number])
    REFERENCES Customer([Customer Number]) ON UPDATE CASCADE;

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I have not been able to get the DDL with the "ON UPDATE CASCADE" to execute; however I have a couple of comments.

    1) You should NEVER have spaces in object names.
    ie table name "Checkout Untagged" - "CheckoutUntagged" or "Checkout_Untagged" are better choices for the table name.
    field name "Customer Number" - "CustomerNumber" or "Customer_Number" are better choices for the field name.


    2) This says it better than I could:
    "Primary keys are supposed to be imutable, never changing, constant. It is an excessively bad practice to have to update them ever. If there is a 0.00001% chance you will have to update a primary key -- then it is not a primary key, its a surrogate key and you need to find the true primary key (even if you have to make it up via a sequence)"
    Which means you should not (IMO) use "UPDATE CASCADE" in your table relationships.......

  3. #3
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to Steve's comments which I agree with, you could try this type of construct to do you ALTER statement if you insist on the Update Cascade .<br><br>
    Code:
    CurrentProject.Connection.Execute "ALTER TABLE JPersons DROP CONSTRAINT chk_person"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 03-17-2016, 02:10 PM
  2. Replies: 1
    Last Post: 09-22-2014, 08:54 AM
  3. Replies: 2
    Last Post: 04-18-2013, 05:56 AM
  4. To "Cascade Update" or Not
    By Jester0001 in forum Database Design
    Replies: 1
    Last Post: 04-10-2012, 03:00 PM
  5. Replies: 7
    Last Post: 02-09-2012, 11:42 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums