Briefly on the meaning of primary key and identity (1,1)

PRIMARY KEY Constraint

A table often has a combination of columns or columns whose values uniquely identify each row in the table. One or more of these columns is called the primary key of a table, through which you can enforce the entity integrity of a table. You can create a primary key by defining a PRIMARY key constraint when you create or change a table.



A table can have only one PRIMARY key constraint, and a column in a PRIMARY KEY constraint cannot accept null values. Because the PRIMARY KEY constraint ensures unique data, it is often used to define identity columns.



When you specify a PRIMARY KEY constraint for a table, Microsoft®sql Server? 2000 the uniqueness of mandatory data by creating a unique index from the primary key column. When you use a primary key in a query, the index can also be used to quickly access the data.



If the PRIMARY key constraint is defined on more than one column, the values in one column can be duplicated, but the values of all the columns in the PRIMARY KEY constraint definition must be unique.



As shown in the following illustration, the au_id and title_id columns in the titleauthor table make up the combined PRIMARY KEY constraints of the table to ensure that the combination of au_id and title_id is unique.







When a join is made, the PRIMARY KEY constraint connects one table to another. For example, to determine the relationship between an author and a book title, you can use a three-direction join of the authors table, the titles table, and the titleauthor table. Because titleauthor contains au_id and title_id two columns, access to the titles table can be made by the association between titleauthor and titles.





Please see



Creating and modifying PRIMARY KEY constraints



©1988-2000 Microsoft Corporation. All rights reserved.







--------------







Transact-SQL Reference





IDENTITY (function)

Used only in SELECT statements with an into table clause to insert an identity column into a new table.



Although similar, the identity function is not an identity property that is used with the CREATE table and ALTER table.



Grammar

IDENTITY (data_type [, Seed, increment]) as column_name



Parameters

Data_type



Identifies the data type of the column. Valid data types for identity columns can be data types for any integer data type category (except for bit data types) or decimal data types.



Seed



The value to assign to the first row in the table. Assigns the next identity value to each subsequent row, which equals the previous identity value plus the increment value. If seed is not specified and increment is not specified, they all default to 1.



Increment



The increment used to add to the seed value to obtain successive rows in the table.



column_name



The name of the column that will be inserted into the new table.



return type

Returns the same type as data_type.



Comments

Because the function creates a column in a table, you must specify the name of the column in the select list in one of the following ways:



--(1)

SELECT IDENTITY (int, 1,1) as Id_num

Into newtable

From oldtable



--(2)

SELECT id_num = IDENTITY (int, 1, 1)

Into newtable

From oldtable



Example

The following example inserts all rows from the employee table in the pubs database into a new table named employees. Use the identity function to start the identification number from 100 instead of 1 in the Employees table.



Use pubs

IF EXISTS (SELECT table_name from INFORMATION_SCHEMA. TABLES

WHERE table_name = ' employees ')

DROP TABLE Employees

Go

EXEC sp_dboption ' pubs ', ' select Into/bulkcopy ', ' true '



SELECT emp_id as Emp_num,

FName as A,

Minit as Middle,

LName as Last,

IDENTITY (smallint, 1) as Job_num,

Job_lvl as Job_level,

pub_id,

Hire_date

Into employees

From employee

Go

Use pubs

EXEC sp_dboption ' pubs ', ' select Into/bulkcopy ', ' false '





Please see



CREATE TABLE



@ @IDENTITY



IDENTITY (properties)



SELECT @local_variable



Using System functions



©1988-2000 Microsoft Corporation. All rights reserved.





[Code=sql] Create and maintain databases





PRIMARY KEY Constraint

A table often has a combination of columns or columns whose values uniquely identify each row in the table. One or more of these columns is called the primary key of a table, through which you can enforce the entity integrity of a table. You can create a primary key by defining a PRIMARY key constraint when you create or change a table.



A table can have only one PRIMARY key constraint, and a column in a PRIMARY KEY constraint cannot accept null values. Because the PRIMARY KEY constraint ensures unique data, it is often used to define identity columns.



When you specify a PRIMARY KEY constraint for a table, Microsoft®sql Server? 2000 the uniqueness of mandatory data by creating a unique index from the primary key column. When you use a primary key in a query, the index can also be used to quickly access the data.



If the PRIMARY key constraint is defined on more than one column, the values in one column can be duplicated, but the values of all the columns in the PRIMARY KEY constraint definition must be unique.



As shown in the following illustration, the au_id and title_id columns in the titleauthor table make up the combined PRIMARY KEY constraints of the table to ensure that the combination of au_id and title_id is unique.







When a join is made, the PRIMARY KEY constraint connects one table to another. For example, to determine the relationship between an author and a book title, you can use a three-direction join of the authors table, the titles table, and the titleauthor table. Because titleauthor contains au_id and title_id two columns, access to the titles table can be made by the association between titleauthor and titles.





Please see



Creating and modifying PRIMARY KEY constraints



©1988-2000 Microsoft Corporation. All rights reserved.







--------------







Transact-SQL Reference





IDENTITY (function)

Used only in SELECT statements with an into table clause to insert an identity column into a new table.



Although similar, the identity function is not an identity property that is used with the CREATE table and ALTER table.



Grammar

IDENTITY (data_type [, Seed, increment]) as column_name



Parameters

Data_type



Identifies the data type of the column. Valid data types for identity columns can be data types for any integer data type category (except for bit data types) or decimal data types.



Seed



The value to assign to the first row in the table. Assigns the next identity value to each subsequent row, which equals the previous identity value plus the increment value. If seed is not specified and increment is not specified, they all default to 1.



Increment



The increment used to add to the seed value to obtain successive rows in the table.



column_name



The name of the column that will be inserted into the new table.



return type

Returns the same type as data_type.



Comments

Because the function creates a column in a table, you must specify the name of the column in the select list in one of the following ways:



--(1)

SELECT IDENTITY (int, 1,1) as Id_num

Into newtable

From oldtable



--(2)

SELECT id_num = IDENTITY (int, 1, 1)

Into newtable

From oldtable



Example

The following example inserts all rows from the employee table in the pubs database into a new table named employees. Use the identity function to start the identification number from 100 instead of 1 in the Employees table.



Use pubs

IF EXISTS (SELECT table_name from INFORMATION_SCHEMA. TABLES

WHERE table_name = ' employees ')

DROP TABLE Employees

Go

EXEC sp_dboption ' pubs ', ' select Into/bulkcopy ', ' true '



SELECT emp_id as Emp_num,

FName as A,

Minit as Middle,

LName as Last,

IDENTITY (smallint, 1) as Job_num,

Job_lvl as Job_level,

pub_id,

Hire_date

Into employees

From employee

Go

Use pubs

EXEC sp_dboption ' pubs ', ' select Into/bulkcopy ', ' false '





Please see



CREATE TABLE



@ @IDENTITY



IDENTITY (properties)



SELECT @local_variable



Using System functions