can unique index be created if the include col makes the index unique?

create index idx_tab1_col1col2

on dbo.tab1([col1])

include([col2])

on tab1_index

My question is this. I require this index to be created. col1 doesn't have a lot of unique values but col2 is our pk and adding col2 to the index makes it unique. Can I make this a unique index then? Also, can someone explaing me a bit as to why it's possible or y not? Thank you very much for your help.

I dare say that you could have created the index as unique in less time than it took to navigate to here and write the question. But you didn't so let's try it together.

Create Table dbo.tab1 (Col1 varchar(5) null, Col2 int not null primary key)

Insert Into dbo.tab1
Select 'aaaaa', 1
Union
Select 'bbbbb', 2
Union
Select 'ccccc', 3
Union
Select 'ccccc', 4
Union
Select 'ddddd', 5

create unique index idx_tab1_col1col2
on dbo.tab1([col1])
include([col2])

Msg 1505, Level 16, State 1, Line 2
CREATE UNIQUE INDEX terminated because a duplicate key was found for object name 'dbo.tab1' and index name 'idx_tab1_col1col2'. The duplicate key value is (ccccc).
The statement has been terminated.

So, no, you can't. Uniqueness must exist within the indexed fields without regard to any included columns.

|||Include column can not make index unique. Because include column is not part of index key.|||Hey Robert. Yes, you are correct but at that time, I was in middle of implememnting a checkdb process for my company, which was never in place. Well, thank you very much for your time and effort and the answer.

can primary key be derived/created from 2 columns?

Hello,
In MS Access a primary key can be created from 2 (or more) non-unique
columns which would comprise a unique column -- as follows (from these 2
repeating columns) using the Indexes dialog box in table design and this
places a Key symbol on the respective columns:
col1 col2
1 mon
1 tue
1 wed
1 thu
1 fri
2 mon
2 tue
2 wed
2 thu
2 fri
...
The combination of col1 and col2 would comprise the primary key here. So in
Access I create a PrimaryKey name and just add fields to that name. Is
something like this doable in sql Server?
I have been able to create a unique key from 2 non-unique fields in a sql
server table and they serve as a constraint. But I don't get the Primary Ke
y
symbol on the columns like in Access. Matter of fact, I observed that only
one column can take the Primary Key symbol. I guess my real question (or
next question) is what the significance is of the Primary Key Symbol and if
it is correct that only one column in a sql server table can have the Primar
y
Key symbol on it?
The purpose of my question(s) is to have as much understanding of Primary
Keys in Sql Server as I can. Right now, it appears that a primary key serve
s
mainly as a constraint. What are other significances of the Primary key?
Thanks,
RichNever mind about the question for getting the double keys. I just figured i
t
out. But if I want to join the primary key to a foreign key in another
table, how can I make a unique join to the primary key from the other table?
Thanks,
Rich|||>> But I don't get the Primary Key symbol on the columns like in Access. <<
What does that mean? It sounds like you are drawing pictures to
program! Do you know how to program? With real code, in a language?
While youy are learning to be a real programmer, instead of a video
game, learn DDL and the "PRIMARY KEY(<col1>, <col2> )" syntax.|||Rich (Rich@.discussions.microsoft.com) writes:
> Never mind about the question for getting the double keys. I just
> figured it out. But if I want to join the primary key to a foreign key
> in another table, how can I make a unique join to the primary key from
> the other table?
SELECT ...
FROM a
JOIN b ON a.col1 = b.col1
AND a.col2 = b.col2
Or did you ask how to do this through point and click? I'm afraid that
I don't know the answer to that question. I know that there is a query
designer in Enterprise Manager, and also in Management Studio. But
this designer is a very limited tool, that only can handle simple queries.
If you feed it more complex queries, you will find that it is prone to
rewrite the queries, so that the meaning of them changes.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:86745BD5-0521-43BB-B615-D1C68A78ABFB@.microsoft.com...
> Hello,
> In MS Access a primary key can be created from 2 (or more) non-unique
> columns which would comprise a unique column -- as follows (from these 2
> repeating columns) using the Indexes dialog box in table design and this
> places a Key symbol on the respective columns:
> col1 col2
> 1 mon
> 1 tue
> 1 wed
> 1 thu
> 1 fri
> 2 mon
> 2 tue
> 2 wed
> 2 thu
> 2 fri
> ...
> The combination of col1 and col2 would comprise the primary key here. So
> in
> Access I create a PrimaryKey name and just add fields to that name. Is
> something like this doable in sql Server?
> I have been able to create a unique key from 2 non-unique fields in a sql
> server table and they serve as a constraint. But I don't get the Primary
> Key
> symbol on the columns like in Access. Matter of fact, I observed that
> only
> one column can take the Primary Key symbol. I guess my real question (or
> next question) is what the significance is of the Primary Key Symbol and
> if
> it is correct that only one column in a sql server table can have the
> Primary
> Key symbol on it?
> The purpose of my question(s) is to have as much understanding of Primary
> Keys in Sql Server as I can. Right now, it appears that a primary key
> serves
> mainly as a constraint. What are other significances of the Primary key?
> Thanks,
> Rich
SQL Server isn't Access. Try learning SQL rather than playing with the
Enterprise Manager interface. It may seem hard work for you right now but
eventually you'll benefit from more control, better results and better
understanding of what you are doing. Example:
ALTER TABLE your_table
ADD CONSTRAINT pk_your_table
PRIMARY KEY (col1,col2);

> Right now, it appears that a primary key serves
> mainly as a constraint. What are other significances of the Primary key?
Primary key is a constraint. SQL Server also creates an index for a primary
key constraint but the first purpose of a primary key is to support entity
integrity and referential integrity.
David Portas
SQL Server MVP
--|||Thank you very much for your explanantion. This was very informative. My
learning is derived from reading on the subject, doing, and asking a lot of
questions. Thus, I thank you kindly for sharing your explanation and for
giving an example. I was not aware that you could use more than one column
in this statement:
ALTER TABLE your_table
ADD CONSTRAINT pk_your_table
PRIMARY KEY (col1,col2);
These are the little details that I miss in my reading, and so I ask the
question and someone points out these more obscure details.
Thanks again,
Rich
"David Portas" wrote:

> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:86745BD5-0521-43BB-B615-D1C68A78ABFB@.microsoft.com...
> SQL Server isn't Access. Try learning SQL rather than playing with the
> Enterprise Manager interface. It may seem hard work for you right now but
> eventually you'll benefit from more control, better results and better
> understanding of what you are doing. Example:
> ALTER TABLE your_table
> ADD CONSTRAINT pk_your_table
> PRIMARY KEY (col1,col2);
>
> Primary key is a constraint. SQL Server also creates an index for a primar
y
> key constraint but the first purpose of a primary key is to support entity
> integrity and referential integrity.
> --
> David Portas
> SQL Server MVP
> --
>
>|||Thank you for your explanation on how to join tables where the primary key
consists of more than one column.
Rich
"Erland Sommarskog" wrote:

> Rich (Rich@.discussions.microsoft.com) writes:
> SELECT ...
> FROM a
> JOIN b ON a.col1 = b.col1
> AND a.col2 = b.col2
> Or did you ask how to do this through point and click? I'm afraid that
> I don't know the answer to that question. I know that there is a query
> designer in Enterprise Manager, and also in Management Studio. But
> this designer is a very limited tool, that only can handle simple queries.
> If you feed it more complex queries, you will find that it is prone to
> rewrite the queries, so that the meaning of them changes.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Try using the product tools before bashing somebody.
The symbol the OP is talking about is on Enterprise Manager table designer.
Not everybody wants to learn DDL, they just want a simple method of storing
data for their application - that can all be done through the GUI now,
despite what you might say, it is a good thing and widens database use.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1136241163.051698.144990@.g49g2000cwa.googlegroups.com...
> What does that mean? It sounds like you are drawing pictures to
> program! Do you know how to program? With real code, in a language?
> While youy are learning to be a real programmer, instead of a video
> game, learn DDL and the "PRIMARY KEY(<col1>, <col2> )" syntax.
>