Index, Unique, Pk

[garf]

I was wondering what's a PK?

I thought PK is a 'unique index', but if so – isn't there an option in SQLyog for a 'non-unique' index? Or for 'unique, and not index' fields?

SQLyog just gives the option to define a field as PK.

Thanks for any help given.

[peterlaursen]

MySQL supports both 'Primary Keys' and 'Unique Indexes' – and also SQLyog supports both.

In SQLyog you define the Primary Key from 'CREATE/ALTER table' – Unique Keys form 'Manage Indexes' from the table (context) menu.

A PK is unique.  There can only be one PK in a table.  There can be more unique indexes, however.  Often the PK is based on a autoincremented Integer column.  This is practical in a lot of situations but any unique (combination) of column(s) can be the PK.

There need not be a PK, but it is recommended.  SQLyog uses the PK efficiently when updating and deleting from the DATA and RESULT grids.  Also with SQLyog/SJA data synchronisation you will benefit from having a PK. 

[garf]

thanks.

So what's the difference between PK and a unique index which is not PK?

And anoter question (didn't know where to put it):

I used this code to add a record to table 'adItems' when I used access.

once I added the record, I pulled the new id of it (just after it was created).

[codebox]

Set rs = Server.CreateObject(“ADODB.Recordset”)

rs.open “select * from adItems”,conn,3,3

rs.AddNew

rs(“catId”) = catId

rs(“expiry”) = expiry

rs(“adTitle”) = adTitle

rs.Update

newid = rs(“id”)

rs.close[/codebox]

now, that I moved to MySql, newid remains blank. why is that? Any idea?

Thanks again

[Samer Fayssal]

PK allows you to reset the table counter. It means the following: if you add a new record and the table automatically gives this record a new serial number, and then you decide to delete one of those records, pk allows the table to reconfigure itself and reassign the old serial bumber to another record. You need to check your environment before using it.

[DonQuichote]

Samer Fayssal wrote on Jun 9 2008, 08:58 PM:

PK allows you to reset the table counter. It means the following: if you add a new record and the table automatically gives this record a new serial number, and then you decide to delete one of those records, pk allows the table to reconfigure itself and reassign the old serial bumber to another record. You need to check your environment before using it.

That is exactly what a primary key should not be. It is true that in most databases there are hacks to reuse primary key values, but they are really hacks. Primary key values should not be reused by their very nature. A primary key value is not only unique, it is unique along the life span of the system. A normal unique key is just unique “at any monent”. So if you have an old primary key value that is not in the table, you should be confident that that record was deleted. The other way around, if you delete a record, you should be confident that no other record will take its primary key value and therefore its identity.

A primary key denotes identity. Identity is somewhat abstract. Two rows can carry the same data, but still be “brothers”. They are not one row. Like twin brothers are different, even if almost every property of them is the same. So make this identity visible and workable, it is usually embodied in an autonumber that is added to the “data columns”. This is also the reason why a primary key should never have any meaning as data: equal dat would reduce to equal identity, which is just not true.

[DonQuichote]

garf wrote on Mar 13 2007, 01:31 PM:

And anoter question (didn't know where to put it):

I used this code to add a record to table 'adItems' when I used access.

once I added the record, I pulled the new id of it (just after it was created).

[codebox]

Set rs = Server.CreateObject(“ADODB.Recordset”)

rs.open “select * from adItems”,conn,3,3

rs.AddNew

rs(“catId”) = catId

rs(“expiry”) = expiry

rs(“adTitle”) = adTitle

rs.Update

newid = rs(“id”)

rs.close[/codebox]

now, that I moved to MySql, newid remains blank. why is that? Any idea?

You add a new record to a SELECT query. Not to a table. This code is probably from an MS-Access database, where you open a recordset based on a table and add a row. You might see what happens if you do that for MySQL. I don't know how intelligent the ODBC driver is. In general, build an INSERT query and check for the value of LAST_INSERT_ID().

[peterlaursen]

I think what DQ writes applies to any auto_increment key .. whether PK or not!

But if another type of column (like a string column) is used as the PK, then it will not necessarily be 'unique in system life time'.

In practical life and in 99% of cases the auto_increment key will be the PK too. Renumbering can be done (if there are no Foreing Keys on it of course), but only if and when the upper value limit for the column type has been reached and if there a 'holes' in the numbering there is normally any reason to do.

Also, I do no want to interfere in your discussion on this, however there is one other important difference between a PK and a Unique Key: A Unique Key may have NULL values and even more rows may have a NULL value for a Unique Key column. So a Unique Key is actually non-unique for rows containing such NULL values (I think that applies from MySQL 4.1 only .. and is similar to UK implementation in most other RDBMS (but I have been told that SQL standards are ambigous on this actually!)).

[Author]

Posts