If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multi-Field Primary Key
I have 2 fields: [PHONE] and [AUTONUMBER]
I would like to define the Primary Key as a multi-field primary key as the concatenation of [PHONE]+[AUTONUMBER] Now, [PHONE] may be null, but [AUTONUMBER] will always contain a unique autonumber. The problem is that Access won't allow null in [PHONE] even though the multi-field concatenation of [PHONE]+[AUTONUMBER] would be unique. Why not? Is this a bug? I thought that is the whole purpose of doing multi-fields is to achieve guaranteed uniqueness? |
#2
|
|||
|
|||
Multi-Field Primary Key
Not sure I follow the logic here. AutoNumbers are normally unique on their
own? So why would you want to use an autonumber plus another field? If you really want to do it anyway, presumably Phone is Text field (not a number field), so you can set these properties: Allow Zero Length Yes Required Yes Default Value "" The field will now contain a zero-length string (ZLS) instead of a Null where this is no phone number, and so you can use it as part of the primary key. You will also need to code so that the field is set to a ZLS when a phone number is deleted. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "nickh" wrote in message ... I have 2 fields: [PHONE] and [AUTONUMBER] I would like to define the Primary Key as a multi-field primary key as the concatenation of [PHONE]+[AUTONUMBER] Now, [PHONE] may be null, but [AUTONUMBER] will always contain a unique autonumber. The problem is that Access won't allow null in [PHONE] even though the multi-field concatenation of [PHONE]+[AUTONUMBER] would be unique. Why not? Is this a bug? I thought that is the whole purpose of doing multi-fields is to achieve guaranteed uniqueness? |
#3
|
|||
|
|||
Multi-Field Primary Key
I use the [PHONE] portion only to link in one-to-many to another table that
contains records of activity on that [PHONE]. I am assuming that the primary key designation causes a faster lookup during an innerjoin than just an indexed field? (Is that assumption correct?) I.E., the [PHONE]+[AUTONUMBER] would be in the same position in the primary key index as [PHONE] alone, but the [PHONE]+[AUTONUMBER] achieves uniqueness whereas [PHONE] alone isn't. Actually the problem is that a lot of records have a null [PHONE] (because we don't have a phone], so then we need the [AUTONUMBER] portion to achieve uniqueness. So with your ZLS the index would look like ""1 ""154 ""894 ""954 et cetera whereas the non-null records would have 10-digit [PHONE] + [AUTONUMBER]: 2125551212955 2135551212956 2145551212957 et cetera Maybe my logic is just totally flawed here. I always learned in db class that the primary key is faster than just indexing alone, so you always want to try to create a primary key to maximize the speed of lookups and joins. "Allen Browne" wrote: Not sure I follow the logic here. AutoNumbers are normally unique on their own? So why would you want to use an autonumber plus another field? If you really want to do it anyway, presumably Phone is Text field (not a number field), so you can set these properties: Allow Zero Length Yes Required Yes Default Value "" The field will now contain a zero-length string (ZLS) instead of a Null where this is no phone number, and so you can use it as part of the primary key. You will also need to code so that the field is set to a ZLS when a phone number is deleted. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "nickh" wrote in message ... I have 2 fields: [PHONE] and [AUTONUMBER] I would like to define the Primary Key as a multi-field primary key as the concatenation of [PHONE]+[AUTONUMBER] Now, [PHONE] may be null, but [AUTONUMBER] will always contain a unique autonumber. The problem is that Access won't allow null in [PHONE] even though the multi-field concatenation of [PHONE]+[AUTONUMBER] would be unique. Why not? Is this a bug? I thought that is the whole purpose of doing multi-fields is to achieve guaranteed uniqueness? |
#4
|
|||
|
|||
Multi-Field Primary Key
On Thu, 4 Jan 2007 21:41:01 -0800, nickh
wrote: I use the [PHONE] portion only to link in one-to-many to another table that contains records of activity on that [PHONE]. I am assuming that the primary key designation causes a faster lookup during an innerjoin than just an indexed field? (Is that assumption correct?) No, it is not. Put an Index (unique or not as appropriate) on the Phone field, distinct from the Primary Key. The Autonumber alone should be the PK; the index on Phone will make your joins work just fine (better than a composite index would do in fact). John W. Vinson[MVP] |
#5
|
|||
|
|||
Multi-Field Primary Key
On Jan 5, 5:06 am, "Allen Browne" wrote: I have 2 fields: [PHONE] and [AUTONUMBER] I would like to define the Primary Key as a multi-field primary key as the concatenation of [PHONE]+[AUTONUMBER] Now, [PHONE] may be null, but [AUTONUMBER] will always contain a unique autonumber. The problem is that Access won't allow null in [PHONE] even though the multi-field concatenation of [PHONE]+[AUTONUMBER] would be unique. Why not? Is this a bug? Such a key could implemented using a UNIQUE constraint i.e. UNIQUE([PHONE], [AUTONUMBER]). In relational terms, a key is a key and 'primary' has no special meaning. The UNIQUE constraint can still be used in a FOREIGN KEY constraint (see OT note below). Perhaps the better approach is to ask: what does PRIMARY KEY give me that UNIQUE does not, aside from requiring that all values be not null? (FWIW columns in the PRIMARY KEY designation can be nullable, it's just you can't actually put the null value in them)... The key could implemented using a UNIQUE constraint i.e. UNIQUE([PHONE], [AUTONUMBER]). In relational terms, a key is a key. The UNIQUE constraint can still be used in a FOREIGN KEY. AutoNumbers are normally unique on their own? So why would you want to use an autonumber plus another field? A valid answer to your question is clustering or physical ordering on disk. The better 'phone book' example would be the desire to cluster on the subscribers' names. The phone number (using a natural key) or autonumber would be required to break duplicates because subscribers' names may not be unique. Care is required in declaring the columns in the correct order i.e. PRIMARY KEY (name, phone_number) i.e. the columns should be in left-to-right order of significance to the clustering. Clustering on name would favour BETWEEN (e.g. grab all names beginning with 'B') and GROUP BY on the name because the rows would be physically contiguous (and on the same page) to begin with and would be faster to fetch. Think how 'useful' a paper phone book physically order on telephone number would be to the average user. Clustering on phone number or *random* autonumber on the other hand would favour concurrency but a sequential autonumber PRIMARY KEY may be the worst choice in any case. [OT ] FWIW nulls in foreign key constraints is one of the few areas where the Jet implementation is IMO better than SQL Server's (and, Allen, I know you are a fan of the 'CASCADE to null' feature in Jet 4.0 g). SQL Server will disregard all values in the referencing columns if one of then contains a null and also will not cascade them e.g. from a null value to a not null value. When you see how Jet handles it, it makes the SQL Server approach seem very odd e.g. CREATE TABLE Table1 ( key_col1 INTEGER NOT NULL, key_col2 INTEGER, UNIQUE (key_col1, key_col2) ) ; CREATE TABLE Table2 ( key_col1 INTEGER NOT NULL, key_col2 INTEGER, FOREIGN KEY (key_col1, key_col2) REFERENCES Table1 (key_col1, key_col2) ON DELETE CASCADE ON UPDATE CASCADE ) ; INSERT INTO Table1 (key_col1, key_col2) VALUES (1, NULL) ; INSERT INTO Table1 (key_col1, key_col2) VALUES (2, NULL) ; INSERT INTO Table1 (key_col1, key_col2) VALUES (3, 3) ; INSERT INTO Table2 (key_col1, key_col2) VALUES (1, NULL) ; INSERT INTO Table2 (key_col1, key_col2) VALUES (2, NULL) ; INSERT INTO Table2 (key_col1, key_col2) VALUES (3, NULL) ; That last insert would fail in Jet but in SQL Server it would succeed on the logic, I guess, that Table1.col2 = Table2.col2 returns unknown therefore should not fail; Jet's apparent logic 'NULL must equal NULL' is harder to defend 'relationally'. However, what seems strange IMO is the SQL Server CASCADE behaviour: UPDATE Table1 SET key_col2 = 1 WHERE key_col1 = 1 ; The above change does not cascade to the referencing table (Table2) in SQL Server. While the inability to compare null values between tables may be sound 'relationally' it would seem to be unintuitive and not the desired effect. Similarly, when deleting: DELETE FROM Table1 WHERE key_col1 = 2 the row is removed from the referencing table in Jet but not in SQL Server. I think the Jet behaviour has more utility. Such 'practical' decisions are hardly unprecedented, even my the famously strict ANSI SQL standards committees. For example, if one null value cannot be compared as being equal to another null value then why do they GROUP together? e.g. DELETE FROM Table1 ; INSERT INTO Table1 (key_col1, key_col2) VALUES (1, NULL) ; INSERT INTO Table1 (key_col1, key_col2) VALUES (2, NULL) ; INSERT INTO Table1 (key_col1, key_col2) VALUES (3, 3) ; SELECT key_col2, COUNT(*) FROM Table1 GROUP BY key_col2 ; The answer is, at least in part, because most people would expect it to work this way and such an approach has the greater practical utility. Jamie. -- |
#6
|
|||
|
|||
Multi-Field Primary Key
It is probably more important for the Inner Join to put an Index on the
ForeignKey [frg_PHONE] in the "Many" Table in this case. Something troubles me: Do you have Activities that don't involve [PHONE]? -- HTH Van T. Dinh MVP (Access) "nickh" wrote in message news I use the [PHONE] portion only to link in one-to-many to another table that contains records of activity on that [PHONE]. I am assuming that the primary key designation causes a faster lookup during an innerjoin than just an indexed field? (Is that assumption correct?) I.E., the [PHONE]+[AUTONUMBER] would be in the same position in the primary key index as [PHONE] alone, but the [PHONE]+[AUTONUMBER] achieves uniqueness whereas [PHONE] alone isn't. Actually the problem is that a lot of records have a null [PHONE] (because we don't have a phone], so then we need the [AUTONUMBER] portion to achieve uniqueness. So with your ZLS the index would look like ""1 ""154 ""894 ""954 et cetera whereas the non-null records would have 10-digit [PHONE] + [AUTONUMBER]: 2125551212955 2135551212956 2145551212957 et cetera Maybe my logic is just totally flawed here. I always learned in db class that the primary key is faster than just indexing alone, so you always want to try to create a primary key to maximize the speed of lookups and joins. |
#7
|
|||
|
|||
Multi-Field Primary Key
So, Jamie, if I am understanding your points, you are saying that there
actually is a benefit to my idea in terms of the physical fragmentation on the disk. I.E. the worst arrangement is to have the autonumber alone be the primary key because it is completely random and irrelevant and there will be the most disk head movement as I try to fetch phone#'s from the table (assuming I am fetching them in sequential phone# order). My idea to have PHONE+AUTONUMBER would be good because at least the phone #'s will be sequentially next to each other on the physical disk. This database is quite large already at 1.2 million records, and growing by 220,000 records per month. I really have 2 different types of records mixed together in 1 table, which may be the root problem here (that I should redesign the schema into 2 tables!) I.E., I am storing records with phones, and also records without phones (by the way these are sales leads purchased from InfoUSA; some have phones which we telemarket to, and others are name/address only no phone which we mail to). We are mixing the 2 records together in the same table since they are both "leads". Those records with phones the obvious lookup key is the phone (appended with an autonumber to break ties because we do receive dupe phones occassionally in the daily feed). Those records that are name/address only no phone, then I would rely on the autonumber to be the key because the phone would be null or zerolengthstring "Jamie Collins" wrote: On Jan 5, 5:06 am, "Allen Browne" wrote: I have 2 fields: [PHONE] and [AUTONUMBER] I would like to define the Primary Key as a multi-field primary key as the concatenation of [PHONE]+[AUTONUMBER] Now, [PHONE] may be null, but [AUTONUMBER] will always contain a unique autonumber. The problem is that Access won't allow null in [PHONE] even though the multi-field concatenation of [PHONE]+[AUTONUMBER] would be unique. Why not? Is this a bug? Such a key could implemented using a UNIQUE constraint i.e. UNIQUE([PHONE], [AUTONUMBER]). In relational terms, a key is a key and 'primary' has no special meaning. The UNIQUE constraint can still be used in a FOREIGN KEY constraint (see OT note below). Perhaps the better approach is to ask: what does PRIMARY KEY give me that UNIQUE does not, aside from requiring that all values be not null? (FWIW columns in the PRIMARY KEY designation can be nullable, it's just you can't actually put the null value in them)... The key could implemented using a UNIQUE constraint i.e. UNIQUE([PHONE], [AUTONUMBER]). In relational terms, a key is a key. The UNIQUE constraint can still be used in a FOREIGN KEY. AutoNumbers are normally unique on their own? So why would you want to use an autonumber plus another field? A valid answer to your question is clustering or physical ordering on disk. The better 'phone book' example would be the desire to cluster on the subscribers' names. The phone number (using a natural key) or autonumber would be required to break duplicates because subscribers' names may not be unique. Care is required in declaring the columns in the correct order i.e. PRIMARY KEY (name, phone_number) i.e. the columns should be in left-to-right order of significance to the clustering. Clustering on name would favour BETWEEN (e.g. grab all names beginning with 'B') and GROUP BY on the name because the rows would be physically contiguous (and on the same page) to begin with and would be faster to fetch. Think how 'useful' a paper phone book physically order on telephone number would be to the average user. Clustering on phone number or *random* autonumber on the other hand would favour concurrency but a sequential autonumber PRIMARY KEY may be the worst choice in any case. [OT ] FWIW nulls in foreign key constraints is one of the few areas where the Jet implementation is IMO better than SQL Server's (and, Allen, I know you are a fan of the 'CASCADE to null' feature in Jet 4.0 g). SQL Server will disregard all values in the referencing columns if one of then contains a null and also will not cascade them e.g. from a null value to a not null value. When you see how Jet handles it, it makes the SQL Server approach seem very odd e.g. CREATE TABLE Table1 ( key_col1 INTEGER NOT NULL, key_col2 INTEGER, UNIQUE (key_col1, key_col2) ) ; CREATE TABLE Table2 ( key_col1 INTEGER NOT NULL, key_col2 INTEGER, FOREIGN KEY (key_col1, key_col2) REFERENCES Table1 (key_col1, key_col2) ON DELETE CASCADE ON UPDATE CASCADE ) ; INSERT INTO Table1 (key_col1, key_col2) VALUES (1, NULL) ; INSERT INTO Table1 (key_col1, key_col2) VALUES (2, NULL) ; INSERT INTO Table1 (key_col1, key_col2) VALUES (3, 3) ; INSERT INTO Table2 (key_col1, key_col2) VALUES (1, NULL) ; INSERT INTO Table2 (key_col1, key_col2) VALUES (2, NULL) ; INSERT INTO Table2 (key_col1, key_col2) VALUES (3, NULL) ; That last insert would fail in Jet but in SQL Server it would succeed on the logic, I guess, that Table1.col2 = Table2.col2 returns unknown therefore should not fail; Jet's apparent logic 'NULL must equal NULL' is harder to defend 'relationally'. However, what seems strange IMO is the SQL Server CASCADE behaviour: UPDATE Table1 SET key_col2 = 1 WHERE key_col1 = 1 ; The above change does not cascade to the referencing table (Table2) in SQL Server. While the inability to compare null values between tables may be sound 'relationally' it would seem to be unintuitive and not the desired effect. Similarly, when deleting: DELETE FROM Table1 WHERE key_col1 = 2 the row is removed from the referencing table in Jet but not in SQL Server. I think the Jet behaviour has more utility. Such 'practical' decisions are hardly unprecedented, even my the famously strict ANSI SQL standards committees. For example, if one null value cannot be compared as being equal to another null value then why do they GROUP together? e.g. DELETE FROM Table1 ; INSERT INTO Table1 (key_col1, key_col2) VALUES (1, NULL) ; INSERT INTO Table1 (key_col1, key_col2) VALUES (2, NULL) ; INSERT INTO Table1 (key_col1, key_col2) VALUES (3, 3) ; SELECT key_col2, COUNT(*) FROM Table1 GROUP BY key_col2 ; The answer is, at least in part, because most people would expect it to work this way and such an approach has the greater practical utility. Jamie. -- |
#8
|
|||
|
|||
Multi-Field Primary Key
nickh wrote: So, Jamie, if I am understanding your points, you are saying that there actually is a benefit to my idea in terms of the physical fragmentation on the disk. I.E. the worst arrangement is to have the autonumber alone be the primary key because it is completely random and irrelevant and there will be the most disk head movement as I try to fetch phone#'s from the table (assuming I am fetching them in sequential phone# order). My idea to have PHONE+AUTONUMBER would be good because at least the phone #'s will be sequentially next to each other on the physical disk. This database is quite large already at 1.2 million records, and growing by 220,000 records per month. Yes, that's the theory. However, like anything relating to 'performance', you will need to test to determine what works best in your particular circumstances e.g. something that benefits one query may penalise another so you have to make a judgement on that which is critical path. A physical in index may be 'better' than a logical one but you will use both flavours (knowingly or otherwise). Be wary of anyone (including me g) who suggests they may be able to determine what is best for you based on the little information you have posted here. Note the mdb must be compacted for the 'clustered' index to be rebuilt. See the following Microsoft articles: ACC2000: Defragment and Compact Database to Improve Performance http://support.microsoft.com/default...b;en-us;209769 "If a primary key exists in the table, compacting re-stores table records into their Primary Key order. This provides the equivalent of Non-maintained Clustered Indexes, and makes the read-ahead capabilities of the Microsoft Jet database engine much more efficient." New Features in Microsoft Jet Version 3.0 http://support.microsoft.com/default...b;en-us;137039 "Compacting the database now results in the indices being stored in a clustered-index format. While the clustered index isn't maintained until the next compact, performance is still improved." I really have 2 different types of records mixed together in 1 table, which may be the root problem here (that I should redesign the schema into 2 tables!) I.E., I am storing records with phones, and also records without phones (by the way these are sales leads purchased from InfoUSA; some have phones which we telemarket to, and others are name/address only no phone which we mail to). We are mixing the 2 records together in the same table since they are both "leads". Consider 'subclassing', where the attributes common to all 'leads' are found in a 'superclass' table. Do a google search on the exact phrase "CREATE TABLE Vehicles". Jamie. -- |
#9
|
|||
|
|||
Multi-Field Primary Key
I'll just mention that a many people, including Access MVPs, consider
autonumber to be a perfectly acceptable and efficient choice for PK. However, even if physical ordering on the disk has enough of an effect on performance that multi-field PKs are worth the extra effort, it may be worth asking yourself if the order of phone numbers is less "random" than the order of autonumbers. It's true that phone numbers have area codes and exchanges so they can be grouped by state, etc., but does that help you? "nickh" wrote in message ... So, Jamie, if I am understanding your points, you are saying that there actually is a benefit to my idea in terms of the physical fragmentation on the disk. I.E. the worst arrangement is to have the autonumber alone be the primary key because it is completely random and irrelevant and there will be the most disk head movement as I try to fetch phone#'s from the table (assuming I am fetching them in sequential phone# order). My idea to have PHONE+AUTONUMBER would be good because at least the phone #'s will be sequentially next to each other on the physical disk. This database is quite large already at 1.2 million records, and growing by 220,000 records per month. I really have 2 different types of records mixed together in 1 table, which may be the root problem here (that I should redesign the schema into 2 tables!) I.E., I am storing records with phones, and also records without phones (by the way these are sales leads purchased from InfoUSA; some have phones which we telemarket to, and others are name/address only no phone which we mail to). We are mixing the 2 records together in the same table since they are both "leads". Those records with phones the obvious lookup key is the phone (appended with an autonumber to break ties because we do receive dupe phones occassionally in the daily feed). Those records that are name/address only no phone, then I would rely on the autonumber to be the key because the phone would be null or zerolengthstring "Jamie Collins" wrote: On Jan 5, 5:06 am, "Allen Browne" wrote: I have 2 fields: [PHONE] and [AUTONUMBER] I would like to define the Primary Key as a multi-field primary key as the concatenation of [PHONE]+[AUTONUMBER] Now, [PHONE] may be null, but [AUTONUMBER] will always contain a unique autonumber. The problem is that Access won't allow null in [PHONE] even though the multi-field concatenation of [PHONE]+[AUTONUMBER] would be unique. Why not? Is this a bug? Such a key could implemented using a UNIQUE constraint i.e. UNIQUE([PHONE], [AUTONUMBER]). In relational terms, a key is a key and 'primary' has no special meaning. The UNIQUE constraint can still be used in a FOREIGN KEY constraint (see OT note below). Perhaps the better approach is to ask: what does PRIMARY KEY give me that UNIQUE does not, aside from requiring that all values be not null? (FWIW columns in the PRIMARY KEY designation can be nullable, it's just you can't actually put the null value in them)... The key could implemented using a UNIQUE constraint i.e. UNIQUE([PHONE], [AUTONUMBER]). In relational terms, a key is a key. The UNIQUE constraint can still be used in a FOREIGN KEY. AutoNumbers are normally unique on their own? So why would you want to use an autonumber plus another field? A valid answer to your question is clustering or physical ordering on disk. The better 'phone book' example would be the desire to cluster on the subscribers' names. The phone number (using a natural key) or autonumber would be required to break duplicates because subscribers' names may not be unique. Care is required in declaring the columns in the correct order i.e. PRIMARY KEY (name, phone_number) i.e. the columns should be in left-to-right order of significance to the clustering. Clustering on name would favour BETWEEN (e.g. grab all names beginning with 'B') and GROUP BY on the name because the rows would be physically contiguous (and on the same page) to begin with and would be faster to fetch. Think how 'useful' a paper phone book physically order on telephone number would be to the average user. Clustering on phone number or *random* autonumber on the other hand would favour concurrency but a sequential autonumber PRIMARY KEY may be the worst choice in any case. [OT ] FWIW nulls in foreign key constraints is one of the few areas where the Jet implementation is IMO better than SQL Server's (and, Allen, I know you are a fan of the 'CASCADE to null' feature in Jet 4.0 g). SQL Server will disregard all values in the referencing columns if one of then contains a null and also will not cascade them e.g. from a null value to a not null value. When you see how Jet handles it, it makes the SQL Server approach seem very odd e.g. CREATE TABLE Table1 ( key_col1 INTEGER NOT NULL, key_col2 INTEGER, UNIQUE (key_col1, key_col2) ) ; CREATE TABLE Table2 ( key_col1 INTEGER NOT NULL, key_col2 INTEGER, FOREIGN KEY (key_col1, key_col2) REFERENCES Table1 (key_col1, key_col2) ON DELETE CASCADE ON UPDATE CASCADE ) ; INSERT INTO Table1 (key_col1, key_col2) VALUES (1, NULL) ; INSERT INTO Table1 (key_col1, key_col2) VALUES (2, NULL) ; INSERT INTO Table1 (key_col1, key_col2) VALUES (3, 3) ; INSERT INTO Table2 (key_col1, key_col2) VALUES (1, NULL) ; INSERT INTO Table2 (key_col1, key_col2) VALUES (2, NULL) ; INSERT INTO Table2 (key_col1, key_col2) VALUES (3, NULL) ; That last insert would fail in Jet but in SQL Server it would succeed on the logic, I guess, that Table1.col2 = Table2.col2 returns unknown therefore should not fail; Jet's apparent logic 'NULL must equal NULL' is harder to defend 'relationally'. However, what seems strange IMO is the SQL Server CASCADE behaviour: UPDATE Table1 SET key_col2 = 1 WHERE key_col1 = 1 ; The above change does not cascade to the referencing table (Table2) in SQL Server. While the inability to compare null values between tables may be sound 'relationally' it would seem to be unintuitive and not the desired effect. Similarly, when deleting: DELETE FROM Table1 WHERE key_col1 = 2 the row is removed from the referencing table in Jet but not in SQL Server. I think the Jet behaviour has more utility. Such 'practical' decisions are hardly unprecedented, even my the famously strict ANSI SQL standards committees. For example, if one null value cannot be compared as being equal to another null value then why do they GROUP together? e.g. DELETE FROM Table1 ; INSERT INTO Table1 (key_col1, key_col2) VALUES (1, NULL) ; INSERT INTO Table1 (key_col1, key_col2) VALUES (2, NULL) ; INSERT INTO Table1 (key_col1, key_col2) VALUES (3, 3) ; SELECT key_col2, COUNT(*) FROM Table1 GROUP BY key_col2 ; The answer is, at least in part, because most people would expect it to work this way and such an approach has the greater practical utility. Jamie. -- |
#10
|
|||
|
|||
Multi-Field Primary Key
BruceM wrote: I'll just mention that a many people, including Access MVPs, consider autonumber to be a perfectly acceptable and efficient choice for PK. I'll just mention that many people, including the Emperor's most trusted advisors, considered his New Clothes to be a perfectly acceptable choice for a state occasion g. Researching a natural key can involve time and effort (though usually a google search will quickly reveal an industry-standard key, ISO standard, etc). Designing your own key can be hard work. Is there anything simpler than incrementing an integer? Sure, an integer that is incremented for you! It is mere coincidence in this case that 'the easy way out' is the not 'the road less travelled'? Jamie. -- |
|
Thread Tools | |
Display Modes | |
|
|