Using SQL to create 2 Primary/Unique Keys in a Table
Author |
Message |
Dave #1 / 9
|
 Using SQL to create 2 Primary/Unique Keys in a Table
I have a table in Access that uses one column as it's Primary Key. I would like to change it so that it uses a combination of 2 columns as it's Primary Key.
|
Tue, 29 Nov 2005 02:32:43 GMT |
|
 |
Ronald Dodg #2 / 9
|
 Using SQL to create 2 Primary/Unique Keys in a Table
When you create a table in Access, you can create a table with multiple columns as it's primary key, of which some users do this with tables that deals with Many to Many relationships. One such example is that items are purchased from time to time, so just one item may have a history of many POs, but at the same time, each Purchase Order may have many items on it, so to resolve this, one would have an item table with the basic info for the item, a PO header table that has basic info for the PO, and then another table that has both the Item number and the PO number with other information relavent to that particular item and PO. Now one of 2 things could be done with this many to many table, either a unique ID field could be created (which is normally highly suggested such as in this case), or both, the Item number and the PO number fields get selected, then the Primary Key toolbar button is clicked on (What you are looking for from what I could tell). By doing this, this allows for duplicate Item numbers and PO numbers, but it does not allow for any 2 records to have the exact same Item number and PO number. Example, no 2 records can contain the Item number of 333333 and PO number of 666666. I don't normally do this type of task in VB environment cause this is more or less a design issue normally speaking, so when I have a change like this, I just go to the table and make the change. Now if there are several tables that needs similar adjustments, that might be one of those exceptions that I would make to not using VBA for design type issues. -- Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000
Quote: > I have a table in Access that uses one column as it's > Primary Key. I would like to change it so that it uses a > combination of 2 columns as it's Primary Key.
|
Wed, 30 Nov 2005 05:04:36 GMT |
|
 |
Tim Ferguso #3 / 9
|
 Using SQL to create 2 Primary/Unique Keys in a Table
Quote: > I have a table in Access that uses one column as it's > Primary Key. I would like to change it so that it uses a > combination of 2 columns as it's Primary Key.
You would have to do a ALTER TABLE MyTable DROP CONSTRAINT PrimaryKey and then another ALTER TABLE MyTable ADD CONSTRAINT idxNewPrimary PRIMARY KEY (OneField, TwoField); Mind you, you'd first have to drop all the relationships that refer to this key. You'd also have to de-duplicate the data in the two fields, and make sure that there are no nulls. Next you'd have to reconfigure all the tables that refer to this key to make sure that both parts of the key are available and correct. Then you need to rewrite all the queries that use this table, oh and all the queries that refer to the related tables you had to fix too. Then you can create the new primary key. Then you can recreate all the dependent relationships. Now test all the joins. And the queries. And redesign the forms, and the reports, and the export queries. And all the applications that handle the export queries. And retrain the users in the new forms. And remind the customers that their reports are going to look a bit different. You did remember to back up first, didn't you? Ronald is not joking when he says that the choice of PK is a pretty central part of the design, and there is almost no reason to want to do this programmatically: it's very much a one-off affair which is why Access gave us the Table Design window. Best wishes Tim F
|
Wed, 30 Nov 2005 22:35:58 GMT |
|
 |
Ronald Dodg #4 / 9
|
 Using SQL to create 2 Primary/Unique Keys in a Table
I didn't even realize there was so much stuff that had to be redone, but then I guess that's one such reason why I don't like to use too much of the automation stuff in Access. All of this rework makes me think of the issues that I had with creating tables in Access 2.0. That issue in Access 2.0 was so bad, if you even made one mistake, you couldn't just correct and move on, but rather you had to delete the entire table and then start from scratch again. However, this type of situation does bring out one very good point that needs to be taken right from the beginning of any development project. For anything that's being developed, you must plan out your development before you get started. Does this mean you have to have every little code planned before developing? No, but you do at least need to have general guidelines planned out and not only that, but try to think about the various possible issues that you as the developer may run into. For the project that I'm working on as we speak, I spent at least a good solid 2 months planning before I even touched any code for the project. This 2 months just mainly dealt with having to setup general guidelines of the purpose of the program, how it was going to address the different issues, what sort of things are going to have to be dealt with, and whatever else may come at my way. As I got started with the coding, some other issues started popping up at me, and I'm still having to address those other issues I still use the Primary key in Access, but I don't use the relationship tool in Access. I personally have not had much success with it's use in regards to queries when it comes to more complex DB systems, so I instead use SQL with the proper joins to run queries. Of course, the project I'm working on, it would be a lot easier if I was able to do it in SQL server environment, but corporate politics are at play, so I have to make due with what I have, which makes the objective, "Striking a balance among the 3 points of view" a lot tougher: Administrator/Owner/Programmer (I.e. IT Department, Top management, and the developer), Casual user of the information (I.e. Supervisors, middle and upper management, Accounting Department, etc), and those that inputs the information (Data Transcriber, Operators, other temp or entry level positions in various departments, Sales force) The issue that we currently face is that the User Friendliness of the main DB system that the company use is not there. I could care less what the administrator says with regards to that, but there are various issues at hand, thus why we don't have our operators using the interface to report the data. Here's a list of the different issues at hand: It's all text based. Some may not find this so bad, but others barely even know how to turn on the system let alone having to try to know all of the different codes or know how to really type as some of them are the 1 (or maybe 2) finger peck and point type. Some may find it easy to use, but also find it's real easy to fudge the numbers too thus again doesn't allow for accurate reports There are no real controls in place to rectify the problem above within the main DB program (the adminstrator disagrees with me by saying the supervisors can view a report. Yeah, this can be checked for certain things, but it's still not near as accurate as it would be with the proper controls in place) Times has to be calculated by hand before they can enter that into the system (I have seen some horrible math errors with regards to this, and not only that, but when they report the amount of times, like say they spent 30 minutes, some will type .50 (the correct way) while others will type .30 (the wrong way). Now to address these different issues, I have created a temporary program of which it took me 1 month to create it, but cause of the various issues and it was done from within Excel (given at the time, I didn't know Access objects too well from the VBA side and I only had 4 weeks tops to get this program developed), I told them specifically that this program is only meant as an intermediate program until I can get a more long term solution in place. This long term type solution not only has to meet the different goals of the Production department, but it must also meet a long list of various objectives as a major aspect of it is going to be dealing with Production and Inventory Control while at the same time being able to provide such things like Cost Accounting capabilities, so I will also have to be able to put in it's program the various stipulations dealing with the Accounting profession. Of course, this DB program will have to bear all of the essentials of the different business and computer requirements. -- Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000
Quote:
> > I have a table in Access that uses one column as it's > > Primary Key. I would like to change it so that it uses a > > combination of 2 columns as it's Primary Key. > You would have to do a > ALTER TABLE MyTable > DROP CONSTRAINT PrimaryKey > and then another > ALTER TABLE MyTable > ADD CONSTRAINT idxNewPrimary PRIMARY KEY (OneField, TwoField); > Mind you, you'd first have to drop all the relationships that refer to this > key. You'd also have to de-duplicate the data in the two fields, and make > sure that there are no nulls. Next you'd have to reconfigure all the tables > that refer to this key to make sure that both parts of the key are > available and correct. Then you need to rewrite all the queries that use > this table, oh and all the queries that refer to the related tables you had > to fix too. Then you can create the new primary key. Then you can recreate > all the dependent relationships. Now test all the joins. And the queries. > And redesign the forms, and the reports, and the export queries. And all > the applications that handle the export queries. And retrain the users in > the new forms. And remind the customers that their reports are going to > look a bit different. > You did remember to back up first, didn't you? > Ronald is not joking when he says that the choice of PK is a pretty central > part of the design, and there is almost no reason to want to do this > programmatically: it's very much a one-off affair which is why Access gave > us the Table Design window. > Best wishes > Tim F
|
Fri, 02 Dec 2005 20:17:36 GMT |
|
 |
Tim Ferguso #5 / 9
|
 Using SQL to create 2 Primary/Unique Keys in a Table
Quote: > I still use the Primary key in Access, but I don't use the > relationship tool in Access. I personally have not had much success > with it's use in regards to queries when it comes to more complex DB > systems, so I instead use SQL with the proper joins to run queries.
This is a bit like a learner driver saying he doesn't like messing about with gear sticks and clutches, but is quite happy storing his suitcase in the boot (trunk?). Get this: queries and relationships are completely different things, despite the Access team's attempts to mix them up together. SELECT queries are structured windows on your data; alternatively think of them as intelligent switchboard operators who can get all the people you want to talk to connected in the right order at the right time. They are only about data output. To be sure, update and insert queries are about altering data in the tables, but without proper protection they are just plain dangerous. Relationships constitute the basic anatomy of your data structure: tables can be persuaded to hold any old garbage and it is only the constraint rules - unique indexes, validation rules, and foreign keys - that prevent them all turning into a grey goo. So what are you going to do with the orders charged to non-existent customer accounts? What happens to all the students when someone accidentally deletes the Maths department record? If none of this stuff matters to you, then perhaps you would be better off working in Excel, which has much better list-handling logic than Access. The only job of a rdbms is the maintenance of a robust and complex data model (ok, and a sophisticated security system), and if you are spitting at that then I don't see why you are using Access at all. I understand that you are a mathematician and should know all this already, so forgive me if this feels like old hat. As you've discovered, Access is unlike all the other Office products where the enduser can simply start it up and start typing. System analysis is a skill rather than an instinct and does need learning; I would say that you do need much more than "general guidelines" before starting to draw up tables. There is a body of knowledge to be acquired too, which should be fairly easy since it's all based on set theory anway! If this is a system on which peoples livelihoods are going to depend, you might like to take some further advice. All the best Tim F
|
Sat, 03 Dec 2005 02:07:15 GMT |
|
 |
Ronald Dodg #6 / 9
|
 Using SQL to create 2 Primary/Unique Keys in a Table
Okay, maybe we got a bit of a misunderstanding cause you right, you do have to plan to great depth, but if you are trying to say that you have to plan to the exact code, even further than having to use psuedo coding, that's where I disagree with. =====Clip===== Quote: > System analysis is a skill rather than an instinct and > does need learning; I would say that you do need much more than "general > guidelines" before starting to draw up tables. There is a body of knowledge > to be acquired too, which should be fairly easy since it's all based on set > theory anway! If this is a system on which peoples livelihoods are going to > depend, you might like to take some further advice.
============== As I did hint at, there are several different things that has to be taken into account, of which your are absolutely correct in that security of the data must be taken into account, and how does orphins (data that no longer has a parent data anymore) get handled? Besides just math and logics, does the term "FlowChart" come to mind? How about Document Control, Data Validation not only to be sure the right type of data is entered, if the data exists for things that deals with lookups, but also is it reasonable and other issues? How are the reports going to get the data it needs? How must the data be stored to support the various requirements while at the same time allowing for efficiency use of the system resources (Data normalization)? What safeguards are in place to help prevent human errors and fudging of data? Who has what authorization of what data and to what level of the data? What sort of data backup is there planned and/or in place, and when the any part (especially the main part) of the system, or even the entire system goes down, what sort of backup plan is in place? The list can go on for quite a ways, but I think you get the idea. Do you think I have not had experience with a lot of these different issues? Are we going to have a perfect system, probably not, but we can certainly try to work towards it. Would I care about all of this, if I wasn't concerned about accuracy of my reports? Would the users be willing to use the system, if they can't use it too well? You are right to the extent that one can't just instinctly use Access, cause one is most defintitely gonna have to spend some time learning Access. However, if something doesn't work too good or I don't really care for something (I.e. some of the error messages, ordinary users won't understand too well, so I create my own instead), I will create work arounds. Just as I did in Excel using VBA to get around a lot of the issues that I had on the spreadsheet side, I will use the same type tools and patterns within Access. Now for me, I still had to go through this same process just like anyone else, though I may have went through it a lot faster, but that's only cause of the years of experience I have had on the software side, my experience with SQL (Showcase Strategy) and VBA (Excel then MS Project, and now Access). Granted, Showcase's SQL may be a large part written from visual choices, but when it comes to some of the more complex issues like certain criterias, I most certainly had to learn the SQL language. Even if I do know the stuff inside out programming wise, does that necessarily mean I know how to put together a good system. Probably not, but I come with an Accounting Education and APICS training background along with working in the Production department (about 1/4 of my time is on the floor either doing things or helping others). The main thing I was getting at, one should not take their planning down to the exact code. They do have to plan for various things, but even with all of the proper planning, things may happen along the way of building the program that will cause the develop to go back and make minor adjustments to the plans or even have to refer back to the customer in certain cases (though this should most definitely be kept to a limit as this can get old pretty fast). I was not trying to say that Access is necessarily a bad product, but there are issues within Access that needs to be addressed (some of which are also within VBA). As far as certain issues are concerned such as bugs, once I create a work around, I will stick to that work around until there's a later documentation that states otherwise that the issue(s) has been resolved, which brings me to another point, do you not think I am constantly looking around doing some research trying to find ways to improve? As one last comment, I'm not trying to say that I know everything as that would be far from the truth, but you did come over me basically as if I knew next to nothing when it comes to this DB stuff. I have been working with computer systems probably longer than you may think. I started out working on computers on the software side, messing around with different things like spreadsheet formulaes and such. I was even introduced to computers initially by a teacher who had me write my own spelling program in BASIC. She had all of the codes typed out on paper and everything, so all I had to do was type it in and run it, but none the less, that's what started my whole computer experience. Now that I have worked with various systems and codes, I do have a high tendency to find things out a lot faster than most ppl. I guess that's also cause I have developed tools and methods along the way. Even with Paradox 4, of which I didn't have any DB experience prior to then, it only took me 20 actual hours to learn it. Added to that, as I was learning it on my own, I showed 3 students how to do a couple of things and their comments were that I tought them more in that short few minutes than what the instructor taught in the whole 1.5 hours. There were times when I have had to turn for help with learning different things, but most of the time, I learned from playing around in the systems and from well written technical manuals. -- Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000
|
Sat, 03 Dec 2005 04:06:58 GMT |
|
 |
Tim Ferguso #7 / 9
|
 Using SQL to create 2 Primary/Unique Keys in a Table
Ronald First thing is to apologise if you felt I was disregarding your experience, but I was attempting to steer a line between your professional standing and what was clearly a naive question. Quote: > The main thing I was getting at, one should not take their planning > down to the exact code. They do have to plan for various things, but > even with all of the proper planning, things may happen along the way > of building the program that will cause the develop to go back and > make minor adjustments to the plans or even have to refer back to the > customer in certain cases (though this should most definitely be kept > to a limit as this can get old pretty fast).
I disagree. There is plenty of space between "the exact code" and rushing into building tables before the analysis is complete. A rough rule-of-thumb is 90% analysis and 10% keyboard time -- but this may be for people who are a bit impatient! As you have discovered, an error like having to change a PK after implementation is more than a PITA -- it costs the customer and should have been picked up at the pencil-and-paper stage. This is not "the exact code", but more like changing your mind about where to put the engine. Quote: > I was not trying to say > that Access is necessarily a bad product, but there are issues within > Access that needs to be addressed (some of which are also within VBA). > As far as certain issues are concerned such as bugs,
Access is not perfect, but it is pretty mature and there are fewer bugs than user misperceptions. What issues are you referring to? Quote: > do you not think I am > constantly looking around doing some research trying to find ways to > improve?
Again, I appreciate that I am on thin ice here, but you might consider some formal training in RDBMS design and analysis, or at least some reading that addresses the theory and knowledge base than underlies it. The old file managers like Paradox and dBase are not good starters for life in the real database world. Best wishes Tim F
|
Sun, 04 Dec 2005 02:58:55 GMT |
|
 |
Ronald Dodg #8 / 9
|
 Using SQL to create 2 Primary/Unique Keys in a Table
Maybe, we again have a differences in how we interpret "the exact code", as I would say it does need to be taken down to psuedo coding (I hope you know what psuedo coding means as it's a programming term that has been used quite often). Basically putting it, it's a writing of all of the logical steps that the program is going to be running through, but yet, this writing may not necessarily be in the proper syntax for the programming language that is being used. When I mention "all of the logical steps", what I mean is every little step that the code has to do, sort of like asking a classroom full of students, "How is the robot going to make a peanut butter sandwich?" One student may say, put the peanut butter on one slice. How is the robot going to do that, use it's metal limbs? Dump it on the slice? Which slice? How much? So psuedo code does work out the details (if done properly), just not taken to the syntax level of the actual programming language, which actually allows some flexibility in that regards cause even if the developer realized one language isn't working out, maybe another language would work and the psuedo code can work for that language too. This particular tool is one that I use quite often to help me work through the different issues while at the same time, not having to use up too much time having to worry about putting these steps into the required syntax code that the language requires. Does this tool resolve all of the different issues? No, not the least, cause you still have things like what code is going to be modulated, how is the code going to be setup to be more easily debugged. However, as far as the question, "How is the code going to be modulated?", that can normally be answered by the different patterns that noticed as the psuedo code is being written down. As far as how to setup the code to make it easier to debug, use the resources efficiently, and other similar issues, that's a matter of having standards of coding in place, which these standards should be well documented so as others can follow through with them. I"m not sure if I really go along with the 10% keyboard and 90% analysis cause part of that 90% analysis would be using the keyboard for writing test code to test things out, thus what had me find these issues pretty quickly. As I go through the process of setting up the code, I tend to do various tests as I have setup some pretty high standards of having the code work as it should be while at the same time, having it user friendly. Let's face it, there are a lot of ppl out there that barely even knows how to turn on a system much less know how to work through different issues. There are 2 particular issues of recent that I have ran into, one of which is something I will just have to deal with regardless and the other, I am working on creating an emulation for. Issue 1: KeyCodes for Keypad When any of the white keys (0-9 and .) is pressed to return an action, not the digit or decimal, it does not return the keycode that's in the KeyCode Constants list, and regardless if Numlock or Shift is used or not, the "Enter" key on the keypad returns the value of "13", not the value of "108", of which even the work around based on article 188550 (VB6), does not work either. Issue 2: DataValidation of when it should take place I am in the process of creating forms, but when you don't want data validation to take place, such as if there is a help button on the same form as the form that the user is working in, you don't want data validation to take place on the active textbox (if it currently has the focus) when the user goes to click on this "Help" button. As it currently stands, there is no default way of allowing this option to take place within the Events of Access (97, 2000 or 2002). VB6 has a way to address this sort of issue. The way VB6 addresses it, it uses the "CausesValidation" property for the object that is recieving the focus, and the "Validate" Event for the object that will lose the focus. In this example with regards to the "Help" command button, this particular button would have it's "CausesValidation" property set to "False" thus the "Validate" event would NOT be triggered on the textbox that just had the focus. Pretty much all other controls would have this same "CausesValidation" property set to "True" thus when any of them does get triggered to recieve the focus, the Validate Event of the previous control (if it's setup) gets triggered and ran. There are 3 general reasons why I can see validation of the data itself would not be needed, and for that matter, probably wouldn't want to have taken place. 1: Getting help on something 2: Resetting the form (Some warning may be in place, but this would be within the Click event of the command button) 3: Backing out of the form (Some warning may be in place, but this would be within the Click event of the command button) So to address this particular issue, I tried to see what I could do via keystroke method, and that's when I learned of the issues at hand with the KeyCode Values. -- Ronald R. Dodge, Jr. Production Statistician Master MOUS 2000
Quote:
> Ronald > First thing is to apologise if you felt I was disregarding your experience, > but I was attempting to steer a line between your professional standing and > what was clearly a naive question. > > The main thing I was getting at, one should not take their planning > > down to the exact code. They do have to plan for various things, but > > even with all of the proper planning, things may happen along the way > > of building the program that will cause the develop to go back and > > make minor adjustments to the plans or even have to refer back to the > > customer in certain cases (though this should most definitely be kept > > to a limit as this can get old pretty fast). > I disagree. There is plenty of space between "the exact code" and rushing > into building tables before the analysis is complete. A rough rule-of-thumb > is 90% analysis and 10% keyboard time -- but this may be for people who are > a bit impatient! As you have discovered, an error like having to change a > PK after implementation is more than a PITA -- it costs the customer and > should have been picked up at the pencil-and-paper stage. This is not "the > exact code", but more like changing your mind about where to put the > engine. > > I was not trying to say > > that Access is necessarily a bad product, but there are issues within > > Access that needs to be addressed (some of which are also within VBA). > > As far as certain issues are concerned such as bugs, > Access is not perfect, but it is pretty mature and there are fewer bugs > than user misperceptions. What issues are you referring to? > > do you not think I am > > constantly looking around doing some research trying to find ways to > > improve? > Again, I appreciate that I am on thin ice here, but you might consider some > formal training in RDBMS design and analysis, or at least some reading that > addresses the theory and knowledge base than underlies it. The old file > managers like Paradox and dBase are not good starters for life in the real > database world. > Best wishes > Tim F
|
Sun, 04 Dec 2005 06:05:14 GMT |
|
 |
Tim Ferguso #9 / 9
|
 Using SQL to create 2 Primary/Unique Keys in a Table
Quote: > I"m not sure if I really go along with the 10% keyboard and 90% > analysis cause part of that 90% analysis would be using the keyboard > for writing test code to test things out, thus what had me find these > issues pretty quickly.
There is a problem of viewpoint here. I appreciate that you posted your question in a programming NG, but the subject itself revealed a basic error in the db schema design process -- which is way, way, way previous to any kind of coding. Franky, if you are still messing about choosing what to use for a primary key, all the coding you are doing now is a Waste Of Money, Brains And Time. You are painting the hull without knowing how many masts are going up. Quote: > Let's face it, there are a lot of ppl > out there that barely even knows how to turn on a system much less > know how to work through different issues.
If this is meant to say that interviewing users as part of systems analysis is hard, then you are right! That is why it takes the lion's share of the project time. Users do not know how they do things, they don't know what they want, they don't understand their own business practices -- but that is no excuse for you not getting all that information out of them! It takes a long time, it's boring, and the project managers hate you for it: but they'll hate you a lot more if you attemtp to short-circuit the process and deliver a useless product on time or, more likely, complete 90% of it and then rip it all up and start from scratch because there is an error on page 2. Best wishes Tim F
|
Mon, 05 Dec 2005 06:04:30 GMT |
|
|
|