7 Replies Latest reply on May 31, 2016 2:06 PM by beverly

    Unable to modify options for table field pkey

    lfnunley

      Hello all,

       

      I have a database in Filemaker 12 Pro Advanced which is connected via ODBC to a Microsoft SQL database. In most of my tables I have the pkey being auto-incremented by SQL. I have one table which for some reason is set in Filemaker to be an auto-enter serial, even though in SQL it is an identity primary key. This means I am unable to add entries to the table in Filemaker because I get an error from SQL about filemaker trying to insert a serial.

       

      I can not modify the field options to turn off auto-enter for some reason, even though I can modify all of my other fields. The actual options button in the 'Manage Database' window is greyed out.

       

      Any ideas? Thank you!

        • 1. Re: Unable to modify options for table field pkey
          mikebeargie

          Check the SQL schema to see why that primary key differs from all the other ones that work.

           

          Then in the filemaker table tab (file > manage > database - "table" tab) there is a "sync" button that will re-pull the schema details from ODBC.

           

          It sounds like something is off in the SQL side, and FM is having a hard time determining what's up with that field.

          1 of 1 people found this helpful
          • 2. Re: Unable to modify options for table field pkey
            lfnunley

            Thank you, I think you are right in that is is on the SQL side, as syncing does not modify the issue. I was wondering if you could assist me with figuring out what the discrefency is in SQL?

             

            Here is the problematic Primary Key:

             

            Here is a Primary Key which has no issues:

             

            The problematic key has a lot more set values, even though I did not have different methods for initializing the tables if I recall correctly. How would I go about remaking the table in such a way that the extra values are not set?

             

            Also just for my future knowledge are you able to tell which values are causing the problems with filemaker?

             

            Thank you for your help!

             

            Edit:

             

            Here is the way I created the problematic primary key:

            CREATE TABLE FOO (PKEY INT IDENTITY(1,1) PRIMARY KEY, ...);

            • 3. Re: Unable to modify options for table field pkey
              mikebeargie

              In SQL I'd imagine you would use an ALTER statement to modify the schema. Or if you have a GUI based manager that supports it, just use that. I use "Sequel Pro" for OSX, and I believe it supports straight SQL sources as well.

               

              I'm most familiar with MySQL which has distinct differences than straight SQL. I think someone like beverly has more straight SQL experience than I do and can hopefully chime in here.

               

              I'd only be speculating as what affects filemaker in the way you described, but if I had to wager an educated guess, I would say the "Allow Nulls - No" may affect FileMaker since it's not transactional by default.

              1 of 1 people found this helpful
              • 4. Re: Unable to modify options for table field pkey
                lfnunley

                Alright, thank you for the information!

                 

                I am going to just recreate the table by copying a working table and deleting all but the pkey and just adding my columns. This should work and in the future I'll look out for that.

                • 5. Re: Unable to modify options for table field pkey
                  beverly

                  Not a lot of difference "LIMIT" (MySQL) & "TOP" for MS SQL, for example, Mike.

                   

                  But comparing the two keys screenshots the 'NOT NULL' may be the difference. However, you want that field to be auto-enter, serial (as it is) AND you want it to be not null. IF you are pushing data from FM to SQL, then you do NOT push that PK value (on INSERT). PK can be used for UPDATE (in fact is required), but is never set (except by the SQL db).

                   

                  So "Allow Nulls" should be No and the value is never set by importing (INSERT).

                   

                  My method to work around this is to have another INT that is called something like "PFKEY_from_FM" so that it can be imported by data in FM. This would match whatever primary key as FM knows it, but is not a SQL primary key.

                   

                  If you are doing something else with the data once in SQL, you can certainly query this field and use for "relationships" (even though not a primary key once in SQL).

                   

                  Does that help? The above work-around" is useful for all flavors of SQL for which I have worked.

                  beverly

                  1 of 1 people found this helpful
                  • 6. Re: Unable to modify options for table field pkey
                    lfnunley

                    beverly, thank you so much!

                     

                    I was a bit confused about filemaker pkeys vs sql pkeys. It looks like my org has been primarily using filemaker serials and just not been having sql generate them, but I was importing a few hundred records from some spreadsheets so I had to have sql make pkeys instead.

                     

                    I appreciate the information, having a second temporary pkey is a good idea.

                     

                     

                    Thank you both of you for your assistance, I'm slowly wrapping my head around sql and filemaker!

                    • 7. Re: Unable to modify options for table field pkey
                      beverly

                      Awesome! one thing I failed to remember: FMP<->SQL db (via ODBC) needs that primary key (in SQL), so that's another reason to always have one in every SQL table and don't try to get FMP primary key actually be the same as the SQL primary key.

                       

                      beverly