So I have this table that has only one index. That index has four fields (composite key). From what I've read on the sage knowledgebase, the first index defined inside a table is THE Primary Key. However, Somehow I am able to write data to this table, with duplicate keys. I understand databases and primary keys. What I don't understand is how Sage is actually dealing with them.
How do I get primary keys to work? Or is there even a way to define a primary key on a table? I tested this on another server. At first it was promising, the primary key prevented duplicates. I wanted to test if the index defined on the table actually did anything so I changed it, and sure enough I was able to add records that I couldn't before (because before, they violated the primary key). When I tried to change the Primary Key back to its original form I even got a beautiful message telling me there were duplicate records. So i deleted those records and was able to change the primary key to its original state. However, now I'm getting duplicate records violating the key!
What's going on? I'm not observing any consistent behavior. If you change the index to a table, is a table validation not enough? What else do you have to do?
Because at this point I'm about to write code that will validate the key before writing to the table. Which is not good practice but apparently the constraints configured aren't functioning properly.
