Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Middlemarch is offline Competent Performer
    Windows 10 Access 2019
    Join Date
    Mar 2015
    Posts
    340

    Primary Key

    Hi, Is this the code to remove a primary key in a table ?



    ALTER TABLE tableName DROP CONSTRAINT fieldName

    if so, is it replaced with

    ALTER TABLE tableName ADD CONSTRAINT fieldName

    It may be something quite different but thought it worth asking before I try. I need to remove the PK to change it's value, without access complaining that it can't.
    Then put it back afterwards.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why don't you copy table and try?

    Why would Access complain about changing PK value? This is not an autonumber field?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Middlemarch is offline Competent Performer
    Windows 10 Access 2019
    Join Date
    Mar 2015
    Posts
    340
    Maybe it's not a PK. It's giving a mix of errors

    Cannot execute data definition statements on linked data
    sources.


    MicrosoftAccess didn't update 0 field(s) due to a type conversion failure, 1 record(s) due to key violations, 0 record(s) due to lock violations: and 0 record(s) due to validation rule violations.
    Do you want to continue running this type of action query anyway?
    To ignore the error(s) and run the query, click Yes.

  4. #4
    Middlemarch is offline Competent Performer
    Windows 10 Access 2019
    Join Date
    Mar 2015
    Posts
    340
    I have removed the Primary Key and can run my changes ok. I guess it wasn't used as no ill effects arose.
    But what if I could not remove it, and needed to temporarily disable it. Can this be done ?
    It's also a linked table so does that cause further problems?
    Thanks for any info.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Did you delete field?

    Why would you have to disable or remove a primary key? Why are your 'changes' limited by presence of PK? Doesn't matter if it's local or linked table, those questions apply.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by Middlemarch View Post
    Hi, Is this the code to remove a primary key in a table ?

    ALTER TABLE tableName DROP CONSTRAINT fieldName

    if so, is it replaced with

    ALTER TABLE tableName ADD CONSTRAINT fieldName

    It may be something quite different but thought it worth asking before I try. I need to remove the PK to change it's value, without access complaining that it can't.
    Then put it back afterwards.
    It is the same if you try and modify any BE table from the FE intercatively.
    You have to make changes in the actual table in the BE.
    After all a link is just that, a link to the table.

    So you would need to open the BE and then start mucking about with that code.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #7
    Middlemarch is offline Competent Performer
    Windows 10 Access 2019
    Join Date
    Mar 2015
    Posts
    340
    Thanks @Welshgasman, got it. So it can't be done from the FE. I will try no more!

    @June, I had to remove or disable the PK in order to avoid the errors in msg 3. I don't know why its presence caused such an issue, but it certainly did.
    I did not delete the field, but did change it's contents.

  8. #8
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by Middlemarch View Post
    Thanks @Welshgasman, got it. So it can't be done from the FE. I will try no more!

    @June, I had to remove or disable the PK in order to avoid the errors in msg 3. I don't know why its presence caused such an issue, but it certainly did.
    I did not delete the field, but did change it's contents.
    Sounds like you are going down a dodgy path there, removing validation for a PK?
    I always made mine the autonumber, then any other field could be changed, with little or no consequences, well certainly less than dabbling with the PK.
    It is called a Primary Key for a reason.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  9. #9
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    I guess it wasn't used as no ill effects arose.
    Are you sure? a PK keeps your data safe by preventing doubles. It is also used to link the tables. If you have doubles there, some existing queries may return double and incorrect results after the change. If it's only one or two records, you might not notice this directly.

  10. #10
    Middlemarch is offline Competent Performer
    Windows 10 Access 2019
    Join Date
    Mar 2015
    Posts
    340
    I've not really used PK in anything I've done. I did wonder what it was for (this db isn't one I made). There is a subForm and a join where recordsource is
    Code:
    SELECT DISTINCTROW Tracks.*, *
    FROM Disks RIGHT JOIN Tracks ON Disks.dNum = Tracks.iNum
    ORDER BY Tracks.iNum, Tracks.tNum;
    Does this indicate a PK is required ?
    By "preventing doubles" do you mean duplicates? If yes could I set Indexed to Yes (No Duplicates) ? I'll try that and see if its value can change without an error.
    It would be helpful to know why it did, when PK. Perhaps further compounded by being in the BE.

  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I've not really used PK in anything I've done. I did wonder what it was for
    https://learnsql.com/blog/why-use-pr...y-foreign-key/

  12. #12
    Middlemarch is offline Competent Performer
    Windows 10 Access 2019
    Join Date
    Mar 2015
    Posts
    340
    I changed Disks.dNum to indexed(no Duplicates) and I could still change it's value ok.
    @CJ_London, I read through the link. Thanks. A lot there. Does every PK need a FK? How is a FK identified ?

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No, a PK is not required to have a related FK. However, not much reason to designate a primary key if it is not going to have dependent child records.

    There is no way to 'identify' a FK, not in the same sense that a PK is identified. You just know that field should be dependent on PK field in design. Set up relationships in Relationship builder if you want. You can give field a name that clues as to its purpose - CustNo_FK
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    Middlemarch is offline Competent Performer
    Windows 10 Access 2019
    Join Date
    Mar 2015
    Posts
    340
    Thanks @June7 the PK was already in this db (I didn't put it there).
    Having removed it and no change, maybe it wasn't used.
    But - if 2 tables are 'joined' as in msg 10 does that indicate the db is 'relational' ? And the PK should stay ?

  15. #15
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    How is a FK identified ?
    for normal and easy use I use the name - I use a PK suffix for primary key and FK for foreign key e.g. customerPK, customerFK, hakes it easy to know which 'end' of a relationship the field belongs to

    If relationships have been set up then

    1. you can identify them by looking at the relationships window
    2. by inspecting the msysRelationships table
    3. the information is also available in the tabledef index properties

    1 is easiest!

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Two key as primary
    By johnseito in forum Database Design
    Replies: 3
    Last Post: 05-13-2019, 02:24 PM
  2. Get ID of Primary Key using VBA
    By jaryszek in forum Access
    Replies: 8
    Last Post: 10-06-2017, 10:18 AM
  3. Primary key
    By vugar in forum Access
    Replies: 15
    Last Post: 09-07-2015, 02:30 AM
  4. primary key
    By handsomealso in forum Access
    Replies: 1
    Last Post: 04-09-2013, 06:07 AM
  5. Primary Key Help
    By phoenix13 in forum Access
    Replies: 4
    Last Post: 07-30-2009, 12:36 PM

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