difference between Primary and Unique index?

[deko]

I have a Make Table query that creates a fairly large table. The Make Table
query populates the new table with one AutoNumber field (which is taken form
another unrelated table as part of the query).

I want to make the AutoNumber field the Primary Key:

DoCmd.OpenQuery "qryXL_To" ' this is the make table query
DoCmd.RunSQL "CREATE UNIQUE INDEX idxTxAcct ON tblXL_To (TxAcct_ID)"

This seems to work okay, but it does not create a "Primary" key -- do I need
a primary key?

Also, why do I need to name the Index -- when I create a table manually,
I've never had to name the Index/Primary Key...

thanks in advance!

DJ -

Well here is the syntax per Access 97 Help:

CREATE [ UNIQUE ] INDEX index
ON table (field [ASC|DESC][, field [ASC|DESC], ...])
[WITH { PRIMARY | DISALLOW NULL | IGNORE NULL }]

and....

'Designate the indexed field or fields as the primary key by using the
PRIMARY reserved word. This implies that the key is unique, so you can
omit the UNIQUE reserved word.'

I think that when you create a table in design mode the default name for
the PK is PrimaryKey. But for all other indexes you have to supply a
name. And the PK doesn't have to be called 'PrimaryKey'.

Just my $0.02 worth...

- Brian

In article <nu************ *****@newssvr29 .news.prodigy.c om>,
dj****@hotmail. com says...

I have a Make Table query that creates a fairly large table. The Make Table
query populates the new table with one AutoNumber field (which is taken form
another unrelated table as part of the query).

I want to make the AutoNumber field the Primary Key:

DoCmd.OpenQuery "qryXL_To" ' this is the make table query
DoCmd.RunSQL "CREATE UNIQUE INDEX idxTxAcct ON tblXL_To (TxAcct_ID)"

This seems to work okay, but it does not create a "Primary" key -- do I need
a primary key?

Also, why do I need to name the Index -- when I create a table manually,
I've never had to name the Index/Primary Key...

thanks in advance!

--
Remove NOSPAM to reply...

[deko]

This seems to work:

DoCmd.RunSQL "CREATE UNIQUE INDEX idxTxID ON tblXL_Tx (Tx_ID) WITH
PRIMARY"

The reason I was having trouble is because I did not include a name for the
index.

"deko" <dj****@hotmail .com> wrote in message
news:nu******** *********@newss vr29.news.prodi gy.com...

I have a Make Table query that creates a fairly large table. The Make Table query populates the new table with one AutoNumber field (which is taken form another unrelated table as part of the query).

I want to make the AutoNumber field the Primary Key:

DoCmd.OpenQuery "qryXL_To" ' this is the make table query
DoCmd.RunSQL "CREATE UNIQUE INDEX idxTxAcct ON tblXL_To (TxAcct_ID)"

This seems to work okay, but it does not create a "Primary" key -- do I need a primary key?

Also, why do I need to name the Index -- when I create a table manually,
I've never had to name the Index/Primary Key...

thanks in advance!

[HSalim]

A primary Key does not allow nulls, a unique indes will allow one null
value.
Every object has to have a name. When you use the user interface,
Access generates a random name on your behalf for the index .

A table can have one and only one primary key (which could be composite key
combining several columns) , but as many unique indexes as you need
Although unique keys can and are sometimes used to identify a row
(individual records), it is the
primary key that is predominantly used to identify a row.

Explicitly providing an index name can be done only through code, and can be
advantageous
because you can now address that index specifically - for example you could
say
drop index (myIndex), instead of iterating thtough collections etc.

HS
"deko" <dj****@hotmail .com> wrote in message
news:nu******** *********@newss vr29.news.prodi gy.com...

I have a Make Table query that creates a fairly large table. The Make Table query populates the new table with one AutoNumber field (which is taken form another unrelated table as part of the query).

I want to make the AutoNumber field the Primary Key:

DoCmd.OpenQuery "qryXL_To" ' this is the make table query
DoCmd.RunSQL "CREATE UNIQUE INDEX idxTxAcct ON tblXL_To (TxAcct_ID)"

This seems to work okay, but it does not create a "Primary" key -- do I need a primary key?

Also, why do I need to name the Index -- when I create a table manually,
I've never had to name the Index/Primary Key...

thanks in advance!

[deko]

I've discovered the make table query and will be setting a lot of PK's...
thanks for the helpful explanation!

"HSalim" <On************ ******@msn.com> wrote in message
news:h5******** **********@nwrd ny02.gnilink.ne t...

A primary Key does not allow nulls, a unique indes will allow one null
value.
Every object has to have a name. When you use the user interface,
Access generates a random name on your behalf for the index .

A table can have one and only one primary key (which could be composite key combining several columns) , but as many unique indexes as you need
Although unique keys can and are sometimes used to identify a row
(individual records), it is the
primary key that is predominantly used to identify a row.

Explicitly providing an index name can be done only through code, and can be advantageous
because you can now address that index specifically - for example you could say
drop index (myIndex), instead of iterating thtough collections etc.

HS
"deko" <dj****@hotmail .com> wrote in message
news:nu******** *********@newss vr29.news.prodi gy.com...

I have a Make Table query that creates a fairly large table. The Make

Table

query populates the new table with one AutoNumber field (which is taken

form

another unrelated table as part of the query).

I want to make the AutoNumber field the Primary Key:

DoCmd.OpenQuery "qryXL_To" ' this is the make table query
DoCmd.RunSQL "CREATE UNIQUE INDEX idxTxAcct ON tblXL_To (TxAcct_ID)"

This seems to work okay, but it does not create a "Primary" key -- do I

need

a primary key?

Also, why do I need to name the Index -- when I create a table manually,
I've never had to name the Index/Primary Key...

thanks in advance!