loading..
Русский    English
00:30

Adding rows to a table with identity column page 1

Many commercial products allow using auto incrementable columns, i.e. columns that form their values automatically when adding new rows. Such columns are widely used as primary keys in tables by virtue of uniqueness of these values. A typical example of that column is sequential counter, which generates a value greater by 1 than previous value (the value obtained when adding previous row).

Below, there is example of creating a table with IDENTITY column (code) in MS  SQL(Structured Query Language) is a database computer language designed for the retrieval and management of data in relational database management systems (RDBMS), database schema creation and modification, and database object access control management.SQL Server.

  1. CREATE TABLE Printer_Inc
  2. (
  3. code int IDENTITY(1,1) PRIMARY KEY ,
  4. model varchar (4) NOT NULL ,
  5. color char (1) NOT NULL ,
  6. type varchar (6) NOT NULL ,
  7. price float NOT NULL
  8. );

Autoincrementable column is defined trough IDENTITY (1, 1) function where the first parameter (seed value) is the value to be assigned to the first row in the table, and the second is the increment to add to the seed value for successive rows in the table. So, in that example, the first inserted row obtains in the code column the value of 1, the second row - 2 and so on.

Since the value in the code column is formed automatically, the statement

  1. INSERT INTO Printer_Inc
  2. VALUES (15, 3111, 'y', 'laser', 599);
raises error, even though the table has not a row with the value of 15 in the code column. Thus we will not include that column when adding the row to the table just as we do when using default value, i.e. 

  1. INSERT INTO Printer_Inc (model, color, type, price)
  2. VALUES (3111, 'y', 'laser', 599);

As a result, the information about model 3111 for color laser printer priced $2599 will be added to the Printer_Inc table. The value in the column code is 15 only accidentally. In the most cases, the specific value in an identity column is of no concern for this value has no sense as a rule; the first moment is uniqueness of the value.

Nevertheless, there are cases where a specific value needs to be inserted into autoincrementable columns. This takes place, for example, when existing data must be transferred into a new structure. In so doing, these data are in a "one-to-many" relationship from the "one " side. Thus, arbitrary values are not allowed here. On the other hand, we want to use autoincrementable field later.

In the absence of autoincrementable columns in the SQL Standard, a single approach does not exist. Here, the realization in MS SQL Server. The statement

  1. SET IDENTITY_INSERT < TABLE name > { ON | OFF };
turn off (ON value) or on (OFF value) auto increment use. Thus, to add a row with the value of 15 in the code column, we may write

  1. SET IDENTITY_INSERT Printer_Inc ON;
  2. INSERT INTO Printer_Inc(code, model, color, type, price)
  3. VALUES (15, 3111, 'y', 'laser', 599);

Note that column list is necessary in this case, i.e. we can neither write:

  1. SET IDENTITY_INSERT Printer_Inc ON;
  2. INSERT INTO Printer_Inc
  3. VALUES (15, 3111, 'y', 'laser', 599);
nor

  1. SET IDENTITY_INSERT Printer_Inc ON;
  2. INSERT INTO Printer_Inc(model, color, type, price)
  3. VALUES (3111, 'y', 'laser', 599);

In the last case, missing value in the code column cannot be inserted automatically because auto increment is turned off.

It should be noted that numbering would be continued from the value of 16 if the value of 15 were the maximum in the code column. Clearly if the auto increment will be turned on: 

  1. SET IDENTITY_INSERT Printer_Inc OFF;

At last, let us consider an example addition the data from the Product table to the Product_Inc table while conserving the values in the code column:

  1. SET IDENTITY_INSERT Printer_Inc ON;
  2. INSERT INTO Printer_Inc(code, model,color,type,price)
  3. SELECT * FROM Printer;

The following should be said of autoincrementable columns. Let the last value in the code column is 16. Then the row with that value is deleted. What is the value then in this column after adding a new row? Correctly, 17, i.e. the last value of counter is conserved despite of deletion of the row with the value of 16. So, the numbering will not be sequential after deletion and addition of rows. This is another reason for inserting the row with a given (missed) value in the autoincrementable column.

Bookmark and Share
Pages 1 2 3 4
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates EXCEPT exercise (-2) exercise 19 exercise 23 exercise 32 More tags
The book was updated
several days ago
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100