mrichmon
asked on
Unique Key vs Index vs Unique Constraint
I have a table in MS SQL 2005
It looks similar to:
PrimaryId
Name
GroupId
Where PrimaryId is the primary key for the table.
I want to enforce that within a group, names are unique
I have read a lot of places that say you should prefer a unique constraint over a a unique key. However, looking at the interface for SQL 2005 I see the option for Indexes/Keys and Check Constraints.
From the Indexes/Keys, I see that I could add a new key and then choose that it is of type Unique Key, and specify information. I do not see a way to create a unique constraint from the check constraints (i.e. what expression would I use?)
Is the Unique Key the optimal way to do this?
What is the difference between creating an index and specifying "Is Unique" to "Yes" vs creating a Unique Key and specifying "Is Unique" to "Yes"?
Also I know that in general the naming convention is:
PK_ for primary keys
FK_ for foreign keys
IX_ for indexes
Is there a naming convention for unique keys? Maybe UK_
Thanks.
It looks similar to:
PrimaryId
Name
GroupId
Where PrimaryId is the primary key for the table.
I want to enforce that within a group, names are unique
I have read a lot of places that say you should prefer a unique constraint over a a unique key. However, looking at the interface for SQL 2005 I see the option for Indexes/Keys and Check Constraints.
From the Indexes/Keys, I see that I could add a new key and then choose that it is of type Unique Key, and specify information. I do not see a way to create a unique constraint from the check constraints (i.e. what expression would I use?)
Is the Unique Key the optimal way to do this?
What is the difference between creating an index and specifying "Is Unique" to "Yes" vs creating a Unique Key and specifying "Is Unique" to "Yes"?
Also I know that in general the naming convention is:
PK_ for primary keys
FK_ for foreign keys
IX_ for indexes
Is there a naming convention for unique keys? Maybe UK_
Thanks.
ASKER
That is not what I was asking.
There are a bunch of ways that "should work fine"
I am trying to look at the optimal way to do this and the difference between the methods.
There are a bunch of ways that "should work fine"
I am trying to look at the optimal way to do this and the difference between the methods.
If you require this data to be unique (and a check is necessary) then this is the optimal way
ASKER
As far as I can see this is your opinion. Where is any supporting documentation?
I don't mean to be rude, but I have had other people post answers stating "this is the optimal way" when it was in fact a terrible way to accomplish the stated goal. Therefore, I ask that supporting documentation be provided, or at least detailed reasoning/analysis of you opinion, rather than just a statement indicating it is true, with no proof or justification.
I don't mean to be rude, but I have had other people post answers stating "this is the optimal way" when it was in fact a terrible way to accomplish the stated goal. Therefore, I ask that supporting documentation be provided, or at least detailed reasoning/analysis of you opinion, rather than just a statement indicating it is true, with no proof or justification.
Naming convention, you do what you like, but UX_ would be a good idea...
Key and Index... what is the difference... good question
Effectively no difference, Unique Constraints are implmented as Unique INdexes the main difference is probably how you write your code... (constraints within table difinition or a separate index create statement)... you can check books online for the proof... look into the architecture part.
Key and Index... what is the difference... good question
Effectively no difference, Unique Constraints are implmented as Unique INdexes the main difference is probably how you write your code... (constraints within table difinition or a separate index create statement)... you can check books online for the proof... look into the architecture part.
Einstine98,
<<Key and Index... what is the difference... good question>>
A primary key is a logical concept while an index is a physical pointer to retrieve values faster. There are separate concepts even though SQL Server does not implement unicity otherwise than by indexing.
mrichmon,
<<I am trying to look at the optimal way to do this and the difference between the methods.>>
Yep there's one and it's called *normalization*. The rest is cookbook approach with no scientific foundation.
If the table looks like...
PrimaryId
Name
GroupId
and the ASSUMING that the concatenation of (Name + Groupid) UNIQUELY identifies the record AND has characteristics of a primary key then:
> You must create a NON NULL UNIQUE INDEX on (Name + GroupID).
> You must create a NON NULL UNIQUE INDEX on PrimaryID to insure the 1:1 cardinality between PrimaryID and (Name + GroupID) is respected.
For more information onto how to do sharp design (determine a primary key, normalize...), checkout
Introduction to Database Theory CJ Date...8th edition
They have lots of information concerning the question of primary keys and how it should be selected.
For correctly implementing a primary key under SQL Server, MS reference should be sufficient.
Hope this helps...
<<Key and Index... what is the difference... good question>>
A primary key is a logical concept while an index is a physical pointer to retrieve values faster. There are separate concepts even though SQL Server does not implement unicity otherwise than by indexing.
mrichmon,
<<I am trying to look at the optimal way to do this and the difference between the methods.>>
Yep there's one and it's called *normalization*. The rest is cookbook approach with no scientific foundation.
If the table looks like...
PrimaryId
Name
GroupId
and the ASSUMING that the concatenation of (Name + Groupid) UNIQUELY identifies the record AND has characteristics of a primary key then:
> You must create a NON NULL UNIQUE INDEX on (Name + GroupID).
> You must create a NON NULL UNIQUE INDEX on PrimaryID to insure the 1:1 cardinality between PrimaryID and (Name + GroupID) is respected.
For more information onto how to do sharp design (determine a primary key, normalize...), checkout
Introduction to Database Theory CJ Date...8th edition
They have lots of information concerning the question of primary keys and how it should be selected.
For correctly implementing a primary key under SQL Server, MS reference should be sufficient.
Hope this helps...
SQL server enforces uniqueness the same way regardless of what syntax you specify - it's not like they said "let's use the fast code for constraints".
To enforce uniqueness, you keep a list of the values and check that list against proposed new values on updates and inserts. Any other way would likely be "sub-optimal" assuming there's a reasonable amount of insert and update activity.
Alternate syntax likely exists to comply with various SQL standards.
Where you can easily get into a discussion of what is optimal is if there are many queries out there that are range queries on Name and, say, Address - then you might want to include address in the index - or if there are many range queries on groupID vs Name, you may want to specify it first in the index... but as far as what it would take *strictly* to keep those values unique... question answered.
MDD
To enforce uniqueness, you keep a list of the values and check that list against proposed new values on updates and inserts. Any other way would likely be "sub-optimal" assuming there's a reasonable amount of insert and update activity.
Alternate syntax likely exists to comply with various SQL standards.
Where you can easily get into a discussion of what is optimal is if there are many queries out there that are range queries on Name and, say, Address - then you might want to include address in the index - or if there are many range queries on groupID vs Name, you may want to specify it first in the index... but as far as what it would take *strictly* to keep those values unique... question answered.
MDD
<<SQL server enforces uniqueness the same way regardless of what syntax you specify>>
That is not entirely true.
Perfomance in a database is not just response time. It is also *maintainability*, meaning the time and cost spent maintainning the database in a specific period of time. Enforcing uniqueness through indexing constraints or through checking existence in application (stored procs, TSQL) is *not* the same and does not involve the same amount of resource consumption, be it CPU or time of maintainance.
Scenario 1: you implement uniqueness through application (by using EXISTS)
Imagine you have 40 procedures inserting in the same table. On each insert/update, you must do checking of existing values... right. Therefore you will have to recode 40 different procedures to maintain your code. On each recode you will have to consider code versionning, errors, stored proc weight etc...
Scenario 2: you implement a unique constraint over the table. Therefore you have to recode only ONE table definition. In *any* case, it is a more efficient approach. Besides if you have to modify the constraint, you modify in one place, not 40 places...
<<To enforce uniqueness, you keep a list of the values and check that list against proposed new values on updates and inserts. Any other way would likely be "sub-optimal" assuming there's a reasonable amount of insert and update activity.>>
One *optimal* way to implement uniqueness through database, domain, table and column constraints as opposed to applicative implementation.
Regards...
That is not entirely true.
Perfomance in a database is not just response time. It is also *maintainability*, meaning the time and cost spent maintainning the database in a specific period of time. Enforcing uniqueness through indexing constraints or through checking existence in application (stored procs, TSQL) is *not* the same and does not involve the same amount of resource consumption, be it CPU or time of maintainance.
Scenario 1: you implement uniqueness through application (by using EXISTS)
Imagine you have 40 procedures inserting in the same table. On each insert/update, you must do checking of existing values... right. Therefore you will have to recode 40 different procedures to maintain your code. On each recode you will have to consider code versionning, errors, stored proc weight etc...
Scenario 2: you implement a unique constraint over the table. Therefore you have to recode only ONE table definition. In *any* case, it is a more efficient approach. Besides if you have to modify the constraint, you modify in one place, not 40 places...
<<To enforce uniqueness, you keep a list of the values and check that list against proposed new values on updates and inserts. Any other way would likely be "sub-optimal" assuming there's a reasonable amount of insert and update activity.>>
One *optimal* way to implement uniqueness through database, domain, table and column constraints as opposed to applicative implementation.
Regards...
Correct - you can definitely avoid an index by maintaining uniqueness through your application - but if you want SQL server to do it for you, it *will* use an index.
A discussion on manual applicative maintenance vs. unique indexes/constraints is, perhaps, worthwhile, but the question was specifically constraints vs. indices.
You can also implement "applicative" maintenance in a trigger and avoid changing 40 procedures... but we usually don't specifically look for ways to force table scans...
If there are virtually no inserts and updates, and you just can't afford the space for an index (which would definitely be a problem in and of itself) you could consider applicative maintenance, but it *almost* cannot make sense...
MDD
A discussion on manual applicative maintenance vs. unique indexes/constraints is, perhaps, worthwhile, but the question was specifically constraints vs. indices.
You can also implement "applicative" maintenance in a trigger and avoid changing 40 procedures... but we usually don't specifically look for ways to force table scans...
If there are virtually no inserts and updates, and you just can't afford the space for an index (which would definitely be a problem in and of itself) you could consider applicative maintenance, but it *almost* cannot make sense...
MDD
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<<I would go further saying that it does not make sense at all in a dbms>>
...which is precisely why I didn't mention it in the first place - making your argument somewhat moot...
What I am saying is that the following 2 things:
ALTER TABLE dbo.<tablename> ADD CONSTRAINT
<namingconventionofyourcho
(
<columnname>
) ON [PRIMARY]
CREATE UNIQUE NONCLUSTERED INDEX <namingconventionofyourcho
(
<columnname>
) ON [PRIMARY]
do exactly the same thing... so in the end, there's really only one way to enforce uniqueness that makes any kind of sense.
Can we put this to bed now?
...which is precisely why I didn't mention it in the first place - making your argument somewhat moot...
What I am saying is that the following 2 things:
ALTER TABLE dbo.<tablename> ADD CONSTRAINT
<namingconventionofyourcho
(
<columnname>
) ON [PRIMARY]
CREATE UNIQUE NONCLUSTERED INDEX <namingconventionofyourcho
(
<columnname>
) ON [PRIMARY]
do exactly the same thing... so in the end, there's really only one way to enforce uniqueness that makes any kind of sense.
Can we put this to bed now?
<<Can we put this to bed now?>>
As far as I am concerned, It never *left* bed...;)
I believe the questioner has now a more complete picture and more than sufficient information to help respond to his answer...which was his request after your initial response...
Regards...
As far as I am concerned, It never *left* bed...;)
I believe the questioner has now a more complete picture and more than sufficient information to help respond to his answer...which was his request after your initial response...
Regards...
fair enough - anything else, mrichmon?
ASKER
Racimo, I understand primary keys and normalization, etc. My example here is contrived, aybe not the best one, but what I came up with as typing out the post.
As to the question of "anything else"?
Well I really don't feel that my question was really addressed, but maybe I didn't explain well enough.
I am looking at what is the difference between:
ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [UK_Table_1] UNIQUE NONCLUSTERED ([Field2])
and
CREATE UNIQUE NONCLUSTERED INDEX [IX_Table_1] ON [dbo].[Table_1] ([Field1])
mdefehr, you say they do exactly the same thing, but why then does SQL distinguish the two, even when later scripting the table? They are always treated differently - so how can they be "exactly the same"?
Statements like "Alternate syntax likely exists to comply with various SQL standards." indicates that you are guessing and that you have no supporting documentation.
I also asked about a naming standard and the only responses were "namingconventionofyourcho
neither of which is what I asked about if there is a standard.
I still feel no closer to either of these. I am not like some posters here just asking how to get something done - I know how to do that. I am trying to find answers to more theoretical questions, and **supporting documentation**. That is how I learn.
mdefehr, the point is not when you feel the question is answered, as you have stated multiple times in this thread, but when I feel it is answered. If you want to "put this to bed" then simply unsubscribe and go on your own way.
As to the question of "anything else"?
Well I really don't feel that my question was really addressed, but maybe I didn't explain well enough.
I am looking at what is the difference between:
ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [UK_Table_1] UNIQUE NONCLUSTERED ([Field2])
and
CREATE UNIQUE NONCLUSTERED INDEX [IX_Table_1] ON [dbo].[Table_1] ([Field1])
mdefehr, you say they do exactly the same thing, but why then does SQL distinguish the two, even when later scripting the table? They are always treated differently - so how can they be "exactly the same"?
Statements like "Alternate syntax likely exists to comply with various SQL standards." indicates that you are guessing and that you have no supporting documentation.
I also asked about a naming standard and the only responses were "namingconventionofyourcho
neither of which is what I asked about if there is a standard.
I still feel no closer to either of these. I am not like some posters here just asking how to get something done - I know how to do that. I am trying to find answers to more theoretical questions, and **supporting documentation**. That is how I learn.
mdefehr, the point is not when you feel the question is answered, as you have stated multiple times in this thread, but when I feel it is answered. If you want to "put this to bed" then simply unsubscribe and go on your own way.
<<I am looking at what is the difference between:
1) ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [UK_Table_1] UNIQUE NONCLUSTERED ([Field2])
and
2 )CREATE UNIQUE NONCLUSTERED INDEX [IX_Table_1] ON [dbo].[Table_1] ([Field1])>>
1) does not allow you to name the index created.
2) does not allow you to name the constraint created.
But basically the two statements do the same thing which means creating an nonclustered index then implementing a constraint of unicity on that index. You may want to do tests by running both on a test table, then you may find out. No need for documentation on that one.
<<I also asked about a naming standard and the only responses were "namingconventionofyourcho
I believe the most important thing is that the naming convention you adopt remains coherent throughout the db life cycle. If you adopt a naming convention based on abbreviations such as *UX_*, keep in mind that, it is likely that it makes sense to you but not to somebody else. Personally, I prefer explicit naming convention such as clustered_index. But that's just me. I do not recall any serious material being written on the subject but I will try to find out.
Hope this helps...
1) ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [UK_Table_1] UNIQUE NONCLUSTERED ([Field2])
and
2 )CREATE UNIQUE NONCLUSTERED INDEX [IX_Table_1] ON [dbo].[Table_1] ([Field1])>>
1) does not allow you to name the index created.
2) does not allow you to name the constraint created.
But basically the two statements do the same thing which means creating an nonclustered index then implementing a constraint of unicity on that index. You may want to do tests by running both on a test table, then you may find out. No need for documentation on that one.
<<I also asked about a naming standard and the only responses were "namingconventionofyourcho
I believe the most important thing is that the naming convention you adopt remains coherent throughout the db life cycle. If you adopt a naming convention based on abbreviations such as *UX_*, keep in mind that, it is likely that it makes sense to you but not to somebody else. Personally, I prefer explicit naming convention such as clustered_index. But that's just me. I do not recall any serious material being written on the subject but I will try to find out.
Hope this helps...
ASKER
Thanks for the comments.
>>1) does not allow you to name the index created.
>>2) does not allow you to name the constraint created.
Yes it does. The name is specified as UK_Table_1 for the first one and IX_Table_1 for the second. I could put any names there I want.
>>You may want to do tests by running both on a test table
I did that before posting. Yes they both enforce uniqueness, however SQL still treats them differently if you try to script them back out.
There are naming standards that are more widespread such as FK for foriegn key, PK for primary key, IX for index, which also is the default way SQL names these. But those are more universal - you will see them in many books and other databases as well. I was wondering if there was a more universal one for unique keys - or if they tend to treat it as a unique index and use the IX naming.
>>1) does not allow you to name the index created.
>>2) does not allow you to name the constraint created.
Yes it does. The name is specified as UK_Table_1 for the first one and IX_Table_1 for the second. I could put any names there I want.
>>You may want to do tests by running both on a test table
I did that before posting. Yes they both enforce uniqueness, however SQL still treats them differently if you try to script them back out.
There are naming standards that are more widespread such as FK for foriegn key, PK for primary key, IX for index, which also is the default way SQL names these. But those are more universal - you will see them in many books and other databases as well. I was wondering if there was a more universal one for unique keys - or if they tend to treat it as a unique index and use the IX naming.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
<< I was wondering if there was a more universal one for unique keys>>
None that I know of. And none that is technology independent.
<< - or if they tend to treat it as a unique index and use the IX naming.>>
Sure..
As I told you previously, primary keys are a logical concepts not to be mistaken with indexes which are physical pointers. Primary keys on a *pure* logical standpoint are not *named*, they are identified among attributes or combinations of attributes:
> a natural key based on one attribute. The key primary *is* the attribute.
> a concatenated key with 2 or more attributes.
> A surrogate key is a generated primary with a 1:1 cardinality to any natural key or concatenated key. IN that case you create a new attribute then implement the 1:1 cardinality.
In the case of SQL Server, primary keys are physically implemented by indexing and because SQL Server implement them by indexing and because indexes must be named then you must give a name to the physical implementation of the key. In that case you have total control over the name of the index which is by default generated by SQL Server. I do not know if ORACLE does the same but index naming convention are purely *technology dependent*. The fact that index is named *IX_* is no standard (as defined by a commission of scientist) but rather the fact that somebody at MICROSOFT sat down and said *let's use IX_ for index*. So I am afraid you are running behind something that does not exists ;)
Hope that helped...
None that I know of. And none that is technology independent.
<< - or if they tend to treat it as a unique index and use the IX naming.>>
Sure..
As I told you previously, primary keys are a logical concepts not to be mistaken with indexes which are physical pointers. Primary keys on a *pure* logical standpoint are not *named*, they are identified among attributes or combinations of attributes:
> a natural key based on one attribute. The key primary *is* the attribute.
> a concatenated key with 2 or more attributes.
> A surrogate key is a generated primary with a 1:1 cardinality to any natural key or concatenated key. IN that case you create a new attribute then implement the 1:1 cardinality.
In the case of SQL Server, primary keys are physically implemented by indexing and because SQL Server implement them by indexing and because indexes must be named then you must give a name to the physical implementation of the key. In that case you have total control over the name of the index which is by default generated by SQL Server. I do not know if ORACLE does the same but index naming convention are purely *technology dependent*. The fact that index is named *IX_* is no standard (as defined by a commission of scientist) but rather the fact that somebody at MICROSOFT sat down and said *let's use IX_ for index*. So I am afraid you are running behind something that does not exists ;)
Hope that helped...
ASKER
>>please keep in mind that we are trying to help you - we are not being paid for this
I am well aware of this. You have only about 9000 points total. I have over 2 million, with over 45,000 this month alone. Additionally I volunteer as a page editor and cleanup volunteer on this site. So don't lecture me on volunteering time on this site.
I am well aware of this. You have only about 9000 points total. I have over 2 million, with over 45,000 this month alone. Additionally I volunteer as a page editor and cleanup volunteer on this site. So don't lecture me on volunteering time on this site.
Thank you for your contribution to this site - yes I am new to (contributing to) the site - I'm trying it out - it's going lovely so far..., but I assure you I'm not new to SQL server. I regret that I did not look you up before I started to "lecture" you.
My apologies
My apologies
Racimo... when did I mention Primary keys?
An index is created to enforce uniqueness irrespective what syntax you use... that is the basic answer to the question...!
An index is created to enforce uniqueness irrespective what syntax you use... that is the basic answer to the question...!
<<Racimo... when did I mention Primary keys?>>
If you mention a keys and speak about uniqueness and indexes what else than a primary key could it be ?
Regards...
If you mention a keys and speak about uniqueness and indexes what else than a primary key could it be ?
Regards...
ASKER
>>If you mention a keys and speak about uniqueness and indexes what else than a primary key could it be ?
A unique key - something that is unique at any given point in time, but may change or be re-used at some later point such that making it a primary key is a bad idea.
Just because something is unique and indexed does not make it a good candidate for a primary key. We have several good examples of this in our organization.
For example, think of a large organization where - due to legacy systems logins are restricted in number of characters. At any given time a login must be unique, however, as people leave and new people come in those logins are recycled. If an old person who left comes back, we still need to identify them, but they will have a new login if the old was has since been re-used.
Not an ideal system, I know, but that is something that needs to be worked with and not uncommon. There are examples outside of our organization as well.
For example, consider social security numbers. While alive you have a social security number. But those are recycled and later assigned to other people. Using that as a primary key, means if your system covers a long enough period of time (i.e. historical data), then you may have overlapping social security numbers. You may want to enforce uniqueness at any given time, but have some other value as the primary key.
Hence a unique indexable key that should not be used as a primary key.
A unique key - something that is unique at any given point in time, but may change or be re-used at some later point such that making it a primary key is a bad idea.
Just because something is unique and indexed does not make it a good candidate for a primary key. We have several good examples of this in our organization.
For example, think of a large organization where - due to legacy systems logins are restricted in number of characters. At any given time a login must be unique, however, as people leave and new people come in those logins are recycled. If an old person who left comes back, we still need to identify them, but they will have a new login if the old was has since been re-used.
Not an ideal system, I know, but that is something that needs to be worked with and not uncommon. There are examples outside of our organization as well.
For example, consider social security numbers. While alive you have a social security number. But those are recycled and later assigned to other people. Using that as a primary key, means if your system covers a long enough period of time (i.e. historical data), then you may have overlapping social security numbers. You may want to enforce uniqueness at any given time, but have some other value as the primary key.
Hence a unique indexable key that should not be used as a primary key.
<<If you mention a keys and speak about uniqueness and indexes what else than a primary key could it be ?>>
Mmm..my comment was a response to Einstine98 but since it seems to trigger your interest let's see some of the issues you rightfully pointed out...and clarify some terminology issues (for such reason I will put terms to be used between asterisks).
<<A unique key - something that is unique at any given point in time, but may change or be re-used at some later point such that making it a primary key is a bad idea.>>
Agreed. That *unique key* is called a *candidate key* which was not selected as *primary key* because it does not meet one of the condition of *stability*.
<<Just because something is unique and indexed does not make it a good candidate for a primary key. We have several good examples of this in our organization>>
Agreed.
<<For example, think of a large organization where - due to legacy systems logins are restricted in number of characters. At any given time a login must be unique, however, as people leave and new people come in those logins are recycled. If an old person who left comes back, we still need to identify them, but they will have a new login if the old was has since been re-used.>>
Agreed.
Several issues are pointed out in your comment...
1) Key length --> *business rule* constraint also called *domain* constraints
That's more a matter of *business rule* constraint rather than a matter of *uniqueness* constraint . Systems where logins were selected as *primary natural key* should have an additional *business rule* constraint (such as len(pkey) < 10 ) implemented additionally to the *uniqueness* constraint. As organization evolves, one should be able to update the *business rule* constraint *independently* of the *uniqueness* constraint. For instance, you may update the *domain* constraint from (Len(pkey) < 10) to (Len(pkey) < 20)
2) Recycling keys --> A key that does not fit all business requirement and is not unique can not be selected as a *primary key*.
That is a matter of correctness of design. If a login was selected as *primary key* but the designer did not evaluate the business requirement that somebody may leave the company and come back and reuse the same login OR that many people maybe using the same login, it is clear that login is not a *primary key* (by definition, primary keys are *unique*). In that case, a better *candidate key* could have been a concatenation between the (login + initial_entry_date) which basically would solve the 2 requiremnt you pointed out. Besides *primary key* can be revised..
<<For example, consider social security numbers. While alive you have a social security number. But those are recycled and later assigned to other people. Using that as a primary key, means if your system covers a long enough period of time (i.e. historical data), then you may have overlapping social security numbers. You may want to enforce uniqueness at any given time, but have some other value as the primary key.>>
That is also matter of design correcteness...
In the system you desribed, *social system number* is not a *primary key* and should have been discarded at design time because it does not meet the requirement *a ssn may be reassigned after somebody's death*. A wiser *primary key* may have been (ssn+birth_date)...
<<Hence a unique indexable key that should not be used as a primary key.>>
Agreed...If you ask me, it is good to know that some people still ask the right questions about key selection.
I hope this helped clarify further some issues about keys...
Regards...
Mmm..my comment was a response to Einstine98 but since it seems to trigger your interest let's see some of the issues you rightfully pointed out...and clarify some terminology issues (for such reason I will put terms to be used between asterisks).
<<A unique key - something that is unique at any given point in time, but may change or be re-used at some later point such that making it a primary key is a bad idea.>>
Agreed. That *unique key* is called a *candidate key* which was not selected as *primary key* because it does not meet one of the condition of *stability*.
<<Just because something is unique and indexed does not make it a good candidate for a primary key. We have several good examples of this in our organization>>
Agreed.
<<For example, think of a large organization where - due to legacy systems logins are restricted in number of characters. At any given time a login must be unique, however, as people leave and new people come in those logins are recycled. If an old person who left comes back, we still need to identify them, but they will have a new login if the old was has since been re-used.>>
Agreed.
Several issues are pointed out in your comment...
1) Key length --> *business rule* constraint also called *domain* constraints
That's more a matter of *business rule* constraint rather than a matter of *uniqueness* constraint . Systems where logins were selected as *primary natural key* should have an additional *business rule* constraint (such as len(pkey) < 10 ) implemented additionally to the *uniqueness* constraint. As organization evolves, one should be able to update the *business rule* constraint *independently* of the *uniqueness* constraint. For instance, you may update the *domain* constraint from (Len(pkey) < 10) to (Len(pkey) < 20)
2) Recycling keys --> A key that does not fit all business requirement and is not unique can not be selected as a *primary key*.
That is a matter of correctness of design. If a login was selected as *primary key* but the designer did not evaluate the business requirement that somebody may leave the company and come back and reuse the same login OR that many people maybe using the same login, it is clear that login is not a *primary key* (by definition, primary keys are *unique*). In that case, a better *candidate key* could have been a concatenation between the (login + initial_entry_date) which basically would solve the 2 requiremnt you pointed out. Besides *primary key* can be revised..
<<For example, consider social security numbers. While alive you have a social security number. But those are recycled and later assigned to other people. Using that as a primary key, means if your system covers a long enough period of time (i.e. historical data), then you may have overlapping social security numbers. You may want to enforce uniqueness at any given time, but have some other value as the primary key.>>
That is also matter of design correcteness...
In the system you desribed, *social system number* is not a *primary key* and should have been discarded at design time because it does not meet the requirement *a ssn may be reassigned after somebody's death*. A wiser *primary key* may have been (ssn+birth_date)...
<<Hence a unique indexable key that should not be used as a primary key.>>
Agreed...If you ask me, it is good to know that some people still ask the right questions about key selection.
I hope this helped clarify further some issues about keys...
Regards...
ASKER
>>I hope this helped clarify further some issues about keys...
Maybe for others, but it was nothing new to me :o)
As for the issues in my intial questions.
I have been doing a lot more research and it seems that while FK, PK and IX **are** in fact standards across different databases, there seems to be no set standard for unique keys. IX is also less of a standard than FK and PK, but still recognized as a standard. And no this is not coming from just because Microsoft decided to use that. It turns out from what I have seen that they use that because it is the simplest standard that goes cross system.
As far as the difference between unique keys and indexes marked as unique, I really feel no further in understanding the differences in SQL 2005, and there really was no documentation anyone else was able to point to justifying the guesses that they were identical. SOme of the claims made here I can show are not entirely accurate, so I will close off this question and pursue it further on my own. as we are going in circles here.
Thanks for the input.
Maybe for others, but it was nothing new to me :o)
As for the issues in my intial questions.
I have been doing a lot more research and it seems that while FK, PK and IX **are** in fact standards across different databases, there seems to be no set standard for unique keys. IX is also less of a standard than FK and PK, but still recognized as a standard. And no this is not coming from just because Microsoft decided to use that. It turns out from what I have seen that they use that because it is the simplest standard that goes cross system.
As far as the difference between unique keys and indexes marked as unique, I really feel no further in understanding the differences in SQL 2005, and there really was no documentation anyone else was able to point to justifying the guesses that they were identical. SOme of the claims made here I can show are not entirely accurate, so I will close off this question and pursue it further on my own. as we are going in circles here.
Thanks for the input.
Thanks for the points...
If interested, here are a few additional pointers for further reading on the concept of keys and databases. (Both can be found at Amazon)
Introduction on Database Theory (CJ Date)
Practical Issues in Database Management - A reference for thinking practitioners (Fabian Pascal)
If interested, here are a few additional pointers for further reading on the concept of keys and databases. (Both can be found at Amazon)
Introduction on Database Theory (CJ Date)
Practical Issues in Database Management - A reference for thinking practitioners (Fabian Pascal)
The exam answer is that you use a unique constraint when you want to specify uniqueness, and you use a unique index when you need a unique index. The difference is not in the implementation, as has been rather exhaustively stated herein. The difference is in the intent communicated by the choice you make.
David
David
anyoneis
Look, i agree that there is a fair amount of nonsense and incomplete statements in this thread. But you are merely adding to that, not correcting or clarifying anything; the statement you make is incorrect.
Cheers
Look, i agree that there is a fair amount of nonsense and incomplete statements in this thread. But you are merely adding to that, not correcting or clarifying anything; the statement you make is incorrect.
Cheers
Introducton
Good question, horrible answers so far, that begs correction. Let's try to take it in chronological order, so as to minimise the back-and-forth. Also the people (above) use the term 'standard' very loosely; I will use it in its exact techical meaning only. There is also a lot of half-baked information above (uniqueness without index, "unicity", dear God) as well as semantic mumbo jumbo and the columbian side-step (uniquenes vs unique vs unique constraint vs unique index), you will have to pick through that yourself; I am not going to correct all errors above, I will just answer the original question completely.
Several posters confuse rather than clarify, database concepts (the intent or design) with SQL syntax (the implementation); I will constrain this post to the latter, I am sure you can figure out the former for yourself.
Chronology
American National Standards Institute (the standards body) took over the maintenance of Structured Query Language from IBM (the creator), following which, SQL became the standard qeury language for RDBMS (what the vendors had to implement). As usual, vendor implemented extensions to the standard, in order to provide a competitive edge; each vendor had their own name for their version of SQL (Transact-SQL, etc). The standard is expressed as ANSI SQL 89, etc. Sometimes the extensions became common across the board, and that became the next rendition of standard ANSI SQL.
1 Early versions of ANSI SQL (up to but excluding ANSI SQL 92) did not have an Unique or Primary Key Constraint. That requirement was filled for many years by:
3.2 They are all Indices, regardless of creation syntax. All that [3.1] is irrelevant if you understand this.
3.3 Therefore there is no "optimal way" re Constraint/Create Index to create them. Any "optimal way" merely depends on what tools you have; clicks vs typing. Where performance is relevant, that applies to logical and physical design, which is outside this scope.
- For my purposes (consultant, many customers and sites, site-independent scripts heavily used), the index form is unlimited, the Constraint form is limited; therefore I use the Index form only (but I have nothing against a mix, as long as you apply the caveats here).
- IIRC MS is the same, Sybase will not let you maintain Indices created with the Constraint syntax, using non-Constraint syntax. That is to say: you are restricted to the syntax that you used to create the object; for Constraint syntax, you can only use Constraint maintenance syntax. Strict implementation of the ASNI Constraint vs Create Index issue. Not really a big deal, but worth noting if "optimal" is a consideration.
3.4 There is a significant performance issue in that the Clustered Index should never be a monotonically increasing value (Identity, NextSequential, Datetime); it could be the PK but such a PK should never be the CI. This is not a problem in Normalised, standard-compliant databases; and it is commonly a problem in sub-standard data heaps.
Naming Convention
Very important. I will not justify them here, their value will be obvious in usage and when they show up in error messages. Since we are dealing with SQL and SQL syntax, that is clearly the physical realm, the implementation, the logical-only concepts and the difference between the logical-physical are irrelevant (only worthy of semantic discussion). Eg. the only unique thing is an Unique Index, regardless of the creation syntax. I disagree with the suggestions in the OP.
4 Index
Parent _ { Child | Role } _FK
- Parent and Child should be self-explanatory
- Role is used where there is more than one relation etween the Parent and Child, eg. a BillOfMaterials structure. Eg. Part_Assembly_FK and Part_Component_FK between Part and "Where Part Used" tables.
- The "FK" is not redundant here, because there are many Constraint types (others are not addressed here).
- Importantly, Foreign Keys do not need an Index (in the Parent, there are already an Unique ["Primary Key" includes "Unique"] Index)
Note
i. The duplicate indices I have found and dropped, as an aside to a straight performance tuning exercises, must number over 2,000. You can save yourself a lot of grief, and enhance your performance, simply by looking for and removing these unintended Indices. Importantly, it is usually due to lack of understanding re the subject of this thread in general, and [3.1] in particular. Which is the reason for my post.
Cheers
Good question, horrible answers so far, that begs correction. Let's try to take it in chronological order, so as to minimise the back-and-forth. Also the people (above) use the term 'standard' very loosely; I will use it in its exact techical meaning only. There is also a lot of half-baked information above (uniqueness without index, "unicity", dear God) as well as semantic mumbo jumbo and the columbian side-step (uniquenes vs unique vs unique constraint vs unique index), you will have to pick through that yourself; I am not going to correct all errors above, I will just answer the original question completely.
Several posters confuse rather than clarify, database concepts (the intent or design) with SQL syntax (the implementation); I will constrain this post to the latter, I am sure you can figure out the former for yourself.
Chronology
American National Standards Institute (the standards body) took over the maintenance of Structured Query Language from IBM (the creator), following which, SQL became the standard qeury language for RDBMS (what the vendors had to implement). As usual, vendor implemented extensions to the standard, in order to provide a competitive edge; each vendor had their own name for their version of SQL (Transact-SQL, etc). The standard is expressed as ANSI SQL 89, etc. Sometimes the extensions became common across the board, and that became the next rendition of standard ANSI SQL.
1 Early versions of ANSI SQL (up to but excluding ANSI SQL 92) did not have an Unique or Primary Key Constraint. That requirement was filled for many years by:
CREATE [UNIQUE] [CLUSTERED] INDEX index_name ON table (col1, col2, ...) [many options ...]
This was implemented in the physical realm (the object in the server) as an Index:
- UNIQUE
- NOT NULL (if the columns(s) were not null)
- CLUSTERED
Notice the key phrase "PRIMARY KEY" was not used. Primary Key remained a technical database term, not an implemented item.
This was implemented in the physical realm (the object in the server) as an Index:
- UNIQUE
- NOT NULL (if the columns(s) were not null)
- CLUSTERED
Notice the key phrase "PRIMARY KEY" was not used. Primary Key remained a technical database term, not an implemented item.
1.1 For the various 3rd party tools to operate on the database, Primary keys and Foreign keys were defined (only the definition was required, regardless of whether there was an index to support it) by:
sp_primarykey ...
sp_foreignkey
2 The advent of ANSI SQL 92 (the standard) provided for a number of constraints, which were implemented by some of the vendors (in order to declare ANSI SQL 92 Compliance, which they could not otherwise declare). The constraints relevant to this thread were:sp_foreignkey
2.1 { CREATE | ALTER } TABLE
<col1 definition, col2 definition, ...>
{ ADD | DROP } CONSTRAINT constr_name
{ PRIMARY KEY | UNIQUE } [ CLUSTERED ] ON (col1, col2, ...)
(Yes, the syntax allows for placement of the above following a column definition, but that restricts keys to single columns; in any case, there is no difference in the context of the thread; so I wil use just the one syntax above.)
This was implemented in the physical realm as (wait for it) an Index (uniqueness is not enforced by magic, it requires an index; Indices need maintenance, they are clearly identified):
- PRIMARY KEY = UNIQUE and NOT NULL
- UNIQUE
- NOT NULL (if the columns(s) were not null)
- CLUSTERED
2.2 FOREIGN KEY (referencing_col1, col2, ...) REFERENCES referenced_table (referenced_col1, col2, ...)
This requires an unique index in the referenced (parent) table, and nothing in the referencing (child) table.
For purposes of clarity, it is best to understand, maintain and discuss [any type of] Keys in the logical database design and application realm, and not in the physical realm (in that realm, there are only Indices). Unfortunately, PK is now in the physical realm.
3 Quite unsurprisingly, ANSI did not implement all the options that the vendors provided in their versions of [1], in ANSI SQL 92 Constraint Declarations. Users now [then] had the option of implementing Indices by declaring SQL Constraints in the middle of their table definitions or by independent Index definitions. Notice the previous 'or' was not an 'xor'. All this leads to a number of anomalies, which exist to this day (Jul 2009).<col1 definition, col2 definition, ...>
{ ADD | DROP } CONSTRAINT constr_name
{ PRIMARY KEY | UNIQUE } [ CLUSTERED ] ON (col1, col2, ...)
(Yes, the syntax allows for placement of the above following a column definition, but that restricts keys to single columns; in any case, there is no difference in the context of the thread; so I wil use just the one syntax above.)
This was implemented in the physical realm as (wait for it) an Index (uniqueness is not enforced by magic, it requires an index; Indices need maintenance, they are clearly identified):
- PRIMARY KEY = UNIQUE and NOT NULL
- UNIQUE
- NOT NULL (if the columns(s) were not null)
- CLUSTERED
2.2 FOREIGN KEY (referencing_col1, col2, ...) REFERENCES referenced_table (referenced_col1, col2, ...)
This requires an unique index in the referenced (parent) table, and nothing in the referencing (child) table.
For purposes of clarity, it is best to understand, maintain and discuss [any type of] Keys in the logical database design and application realm, and not in the physical realm (in that realm, there are only Indices). Unfortunately, PK is now in the physical realm.
3.1 A mix of Indices, created by either ANSI SQL 92 Constraint Declarations or Transact-SQL Create Index statements. There is no problem if you understand that (and there is if you don't):
- duplicate indices (two indices on the exact same set of columns) with different index/constraint_names (you created a dupe Index because you did not realise one already existed) [i]
- you can also make mistakes such as PK Constraint and a Unique on the same set of columns; create more than one index on the same set of columns; or create combinations of Constraints and Indices that result in duplicate Indices [i]
- Some but not all, Indices that can be viewed/administered from one or the other GUI window (eg. all Indices will not show up under "Primary Keys" or "Unique Keys") (DBArtisan is the only tool that I have seen that gives this area the correct treatment)
- while some innocent folk like simple rules such as "Indices only" or "Constraints only", this is not feasible in the real world
- in order to set [performance, etc] options on the indices, one has to use the Index (and not the Constraint) form. - If you use either form, 3rd party tools will not always find the PKs and FKs correctly, therefore for the conservative, engineering types, [1.1] must be used regardless.
- duplicate indices (two indices on the exact same set of columns) with different index/constraint_names (you created a dupe Index because you did not realise one already existed) [i]
- you can also make mistakes such as PK Constraint and a Unique on the same set of columns; create more than one index on the same set of columns; or create combinations of Constraints and Indices that result in duplicate Indices [i]
- Some but not all, Indices that can be viewed/administered from one or the other GUI window (eg. all Indices will not show up under "Primary Keys" or "Unique Keys") (DBArtisan is the only tool that I have seen that gives this area the correct treatment)
- while some innocent folk like simple rules such as "Indices only" or "Constraints only", this is not feasible in the real world
- in order to set [performance, etc] options on the indices, one has to use the Index (and not the Constraint) form. - If you use either form, 3rd party tools will not always find the PKs and FKs correctly, therefore for the conservative, engineering types, [1.1] must be used regardless.
3.2 They are all Indices, regardless of creation syntax. All that [3.1] is irrelevant if you understand this.
3.3 Therefore there is no "optimal way" re Constraint/Create Index to create them. Any "optimal way" merely depends on what tools you have; clicks vs typing. Where performance is relevant, that applies to logical and physical design, which is outside this scope.
- For my purposes (consultant, many customers and sites, site-independent scripts heavily used), the index form is unlimited, the Constraint form is limited; therefore I use the Index form only (but I have nothing against a mix, as long as you apply the caveats here).
- IIRC MS is the same, Sybase will not let you maintain Indices created with the Constraint syntax, using non-Constraint syntax. That is to say: you are restricted to the syntax that you used to create the object; for Constraint syntax, you can only use Constraint maintenance syntax. Strict implementation of the ASNI Constraint vs Create Index issue. Not really a big deal, but worth noting if "optimal" is a consideration.
3.4 There is a significant performance issue in that the Clustered Index should never be a monotonically increasing value (Identity, NextSequential, Datetime); it could be the PK but such a PK should never be the CI. This is not a problem in Normalised, standard-compliant databases; and it is commonly a problem in sub-standard data heaps.
Very important. I will not justify them here, their value will be obvious in usage and when they show up in error messages. Since we are dealing with SQL and SQL syntax, that is clearly the physical realm, the implementation, the logical-only concepts and the difference between the logical-physical are irrelevant (only worthy of semantic discussion). Eg. the only unique thing is an Unique Index, regardless of the creation syntax. I disagree with the suggestions in the OP.
4 Index
[U] [C] _ { PK | Base | ColumnName }
where:
U = Unique
C = Clustered
PK = defined Primary Key (note, not nec. Primary Key Constraint)
Example:C = Clustered
PK = defined Primary Key (note, not nec. Primary Key Constraint)
UC_SecurityId = Unique Clustered Index on single column
ProductClass = Non-unique, nonclustered Index
U_CustomerNo = Unique nonclustered Index on single column
U_PK = Unique nonclustered Index on composite key, which is Primary
Note, "I" is not used, it is redundant, they are all Indices
5 ConstraintProductClass = Non-unique, nonclustered Index
U_CustomerNo = Unique nonclustered Index on single column
U_PK = Unique nonclustered Index on composite key, which is Primary
Note, "I" is not used, it is redundant, they are all Indices
Parent _ { Child | Role } _FK
- Parent and Child should be self-explanatory
- Role is used where there is more than one relation etween the Parent and Child, eg. a BillOfMaterials structure. Eg. Part_Assembly_FK and Part_Component_FK between Part and "Where Part Used" tables.
- The "FK" is not redundant here, because there are many Constraint types (others are not addressed here).
- Importantly, Foreign Keys do not need an Index (in the Parent, there are already an Unique ["Primary Key" includes "Unique"] Index)
i. The duplicate indices I have found and dropped, as an aside to a straight performance tuning exercises, must number over 2,000. You can save yourself a lot of grief, and enhance your performance, simply by looking for and removing these unintended Indices. Importantly, it is usually due to lack of understanding re the subject of this thread in general, and [3.1] in particular. Which is the reason for my post.
Cheers
<<There is also a lot of half-baked information above (uniqueness without index, "unicity", dear God)>>
Well I assume this comment is directed towards me.
First, I would like to apologize to the OP for the use of the term *unicity* instead of *uniqueness*. Since English is *not* my native language, I have made a *translation hickup* (French word for *uniqueness* is *unicité*). Such hickups do happen from time to time in online exchange and, thanks god, a vast majority of people do not make a sarcastic fuss about it.
Second, I find your comment regarding *half baked information* disrespectful and unprofessional for the people who tried to answer the question in a helping spirit. Such sarcasm brings negative spirit in the thread and break the following rule I invite you to re-read.
<guidelines>
Be professional: Treat the asker and your colleagues as professionals. Check your ego and your attitude at the door; *rudeness, derogatory comments, and sarcastic remarks are uncalled for and unnecessary*.
Avoid criticizing: There's nothing to be gained by criticizing another Member when disagreeing with his/her suggestions. Don't take a critical comment personally; stay focused on the object -- solving the asker's problem.
</guidelines>
Third, I have read your response and I do not agree with neither its relevance in this thread context nor with some of its content. Case closed.
Well I assume this comment is directed towards me.
First, I would like to apologize to the OP for the use of the term *unicity* instead of *uniqueness*. Since English is *not* my native language, I have made a *translation hickup* (French word for *uniqueness* is *unicité*). Such hickups do happen from time to time in online exchange and, thanks god, a vast majority of people do not make a sarcastic fuss about it.
Second, I find your comment regarding *half baked information* disrespectful and unprofessional for the people who tried to answer the question in a helping spirit. Such sarcasm brings negative spirit in the thread and break the following rule I invite you to re-read.
<guidelines>
Be professional: Treat the asker and your colleagues as professionals. Check your ego and your attitude at the door; *rudeness, derogatory comments, and sarcastic remarks are uncalled for and unnecessary*.
Avoid criticizing: There's nothing to be gained by criticizing another Member when disagreeing with his/her suggestions. Don't take a critical comment personally; stay focused on the object -- solving the asker's problem.
</guidelines>
Third, I have read your response and I do not agree with neither its relevance in this thread context nor with some of its content. Case closed.
Racimo
No need to apologise or explain. Your contribution stands out as the one clear mind, and you have th epoints to prove it, you supplied the need to someone who had fixed ideas and an incomplete understanding of the problem/concepts.
Second, your English is so good, I did not realise you were translating, I took your 'unicity' to mean what was written, I had noo idea it was a French word. No offence intended.
Maybe I should retract "half-baked". But how is one going to deal with the mish-mash of incomplete and inaccurate concepts which are bandied around with and accepted (by all except you), and argued, without resolution ? One has to call it something, one has to identify the problem clearly before one can rise above it and identify the solution. Please supply another descriptive word for this mess, and I will replace my term with it.
There was no criticism intended, to you or the others, I was merely describing the mess. Keeping that in mind, May I suggest:
Simple facts are:
- OP in the first post did not understand or could not differentiate certain concepts, the supplied functions, and how to implement what he wanted.
- In the thread it became clear that many people did not understand the simple fact that, if you create a PK/Unique Constraint or create an Index, you will get an index. The stated question "Unique Key vs Index vs Unique Constraint" was not being dealt with directly
- Even though you stated it (although less definitively than I) OP argued, others added to the confusion and misunderstandings.
- I posted a clarification to the original issues (I specifically excluded the rest of the peripheral discussions)
- due to the misunderstood concepts bandied about, but not nailed, I had to post something that clarified a few of those terms as well, otherwise it would be meaningless. (There are more concepts in this convoluted thread that could be nailed, and of course, peripheral concepts/names which are unworthy of address.)
- this clarification is pretty much what you stated (although less definitively than I), which was accepted
- your own accepted contribution is in fact close to mine
- so your statement now, contradicts not just my contribution but your own accepted contribution
- cest la vie
Again, I acknowledge your contribution as clarity, standing out in a sea of confusion; I apologise for not making that clear. Please see if you can accept that.
Cheers
>> <<There is also a lot of half-baked information above (uniqueness without index, "unicity", dear God)>>
> Well I assume this comment is directed towards me.
Certainly not, and I apologise for not making that clear. I can see that you have done a good job of helping someone in an environment filled with misconstrued and misunderstood concepts and definitions, dealing with people with half-baked understanding of same. It is a mess. I tried to supply clear definitions, which means I had to step outside that.> Well I assume this comment is directed towards me.
No need to apologise or explain. Your contribution stands out as the one clear mind, and you have th epoints to prove it, you supplied the need to someone who had fixed ideas and an incomplete understanding of the problem/concepts.
Second, your English is so good, I did not realise you were translating, I took your 'unicity' to mean what was written, I had noo idea it was a French word. No offence intended.
Maybe I should retract "half-baked". But how is one going to deal with the mish-mash of incomplete and inaccurate concepts which are bandied around with and accepted (by all except you), and argued, without resolution ? One has to call it something, one has to identify the problem clearly before one can rise above it and identify the solution. Please supply another descriptive word for this mess, and I will replace my term with it.
There was no criticism intended, to you or the others, I was merely describing the mess. Keeping that in mind, May I suggest:
Don't take a critical comment personally; stay focused on the object -- solving the asker's problem.
> I do not agree with neither its relevance in this thread context nor with some of its content
Fine, but that is surprising coming from the person who supplied the accepted solution. I do not look at MS, I was referred to this thread; I know from experience in MS shops that this area is a common problem (people with half-baked [please replace] understanding of concepts trying to do their work on sub-standard software that let's you tie yourself up in knots); I noticed that the entire thread was filled with people stepping around each other without actually identifying and dealing with the exact issue (you providing the best information in that environment). Since there were many hits on it, for the value of the EE archive, I decided to correct it.Simple facts are:
- OP in the first post did not understand or could not differentiate certain concepts, the supplied functions, and how to implement what he wanted.
- In the thread it became clear that many people did not understand the simple fact that, if you create a PK/Unique Constraint or create an Index, you will get an index. The stated question "Unique Key vs Index vs Unique Constraint" was not being dealt with directly
- Even though you stated it (although less definitively than I) OP argued, others added to the confusion and misunderstandings.
- I posted a clarification to the original issues (I specifically excluded the rest of the peripheral discussions)
- due to the misunderstood concepts bandied about, but not nailed, I had to post something that clarified a few of those terms as well, otherwise it would be meaningless. (There are more concepts in this convoluted thread that could be nailed, and of course, peripheral concepts/names which are unworthy of address.)
- this clarification is pretty much what you stated (although less definitively than I), which was accepted
- your own accepted contribution is in fact close to mine
- so your statement now, contradicts not just my contribution but your own accepted contribution
- cest la vie
Again, I acknowledge your contribution as clarity, standing out in a sea of confusion; I apologise for not making that clear. Please see if you can accept that.
Cheers
<<Again, I acknowledge your contribution as clarity, standing out in a sea of confusion; I apologise for not making that clear. Please see if you can accept that.>>
As I said it is a case closed.
As I said it is a case closed.
...should work fine