what is relationship between Primary key and Indentity? 2

[Rousseau10]

I was told in Access a primary key is called a "primary key", but in SQL a primary key is called the "identity". Is this true?

What is the relationship between Primary Key and indentiy as a column attribute?


What is the seed and increment attribute used for the Identity?

TANX!

I was the first American Soldier to put a basketball hoop up in Iraq, only to have it stolen by a soldier from different camp. Newbee - Adam

 

[hmckillop]

No a primary key is not called an identity. Identity refers to an automatic incremental option you can set for integer field types. It is used to autogenerate a sequence of numbers without having to worry about inputting them yourself.

A primary key is defined when this field will uniquely identify the row.

There is no relationship between the identity and primary key unless you make your primary key an identity field.

Another consideration is when creating a primary key is the default action is to make this the clustered index, and this is not always the best thing. But thats another story.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[Scheck]

A primary key is really the same in Access and SQL Server. It is a column that must contain unique values and does not allow NULLS.

An identity column is one where the value (a number) is automatically generated by SQL Server, much the same as an AutoNumber column in Access. An Identity column is often used for the Primary Key in a table because it automatically conforms to the rules that a primary key enforces.

You can set the initial value for the Identity Column by setting the Seed, and you can also set the increment amount. For example, if you want the consecutive primary key values to be 100, 110, 120, 130, 140, etc., you would set the Seed to 100 and set the increment to 10.

The values for an Identity keep incrementing for every attempted row insert (even if the insert is rolled back) and even if you delete every row in the table. Truncating a table will delete all rows AND reset the Seed of the Identity column.

 

[Scheck]

To clarify the first line of my last posting - a primary key is not a column, but a constraint on a column or columns...

 

[Rousseau10]

I knew what you meant Scheck!
These answers really helped me. Now I realize the insturctor was saying the identity is similar to the autonumber in Access, not the primary key.

Also I did not realize the seed was the starting point, I was thinking it was the max autogenerated number. Silly me.

Sue per tanx!



since you mentioned it, what exactly does it mean to "rollback" to take back an insert or update statement, If so, why would this need to happen??

I was the first American Soldier to put a basketball hoop up in Iraq, only to have it stolen by a soldier from different camp. Newbee - Adam

 

[Scheck]

A rollback is a way of undoing or erasing data modifications in a transaction.
Sometimes you need to perform several actions (insert, update, delete) in sequence and all must complete successfully for business or data integrity reasons.

Example: ATM transfer of funds ($100) from account A to account B. The first action is to reduce the balance of account A by $100. The second action is to increase the balance of account B by $100. If the second action fails, you have to roll back the entire transaction or it will be invalid (and you'll be missing $100).

You can wrap your action statements within a transaction, and check for errors after each action. If any one of the actions fail, you can rollback (undo) the entire transaction (or part of the transaction to a savepoint). If at the end there are no errors, you commit the transaction and the modifications are saved.

 

[Rousseau10]

great answer! It makes perfect sense now. I was reading a few years ago about this in a microsoft book an did somewhat remember it was integral for finacial transactions!

I certainly would not want to be missing 100 bucks!

I was the first American Soldier to put a basketball hoop up in Iraq, only to have it stolen by a soldier from different camp. Newbee - Adam