|
|
|
| Home | Downloads | Activate | Contact | Forum | Faq | Links |
Posted by Dave on May 21 2007 20:51:37:
In Reply to: Skip identity fields? posted by Katwork on May 21 2007 14:07:35:
Katwork,
I will use MS SQL most of the time in this explanation but the principle applies to other languages as well.
Identity fields are like auto increment fields, it means that the value is auto generated when each row is created (on script execution or row insertion).
SQL Script Builder will not add the specification that a column is identity or auto increment, you will have to add it yourself.
First you have to decide if you want to keep your existing values or want new ones generated. Most of the time this decision depends on having other tables that are linked to these values.
There's an exeption if you want the identity value generated when executing the script... because the value of the field must be an empty string, example :
in MS SQL :
CREATE TABLE items
(
item_id int IDENTITY(10,2),
item_name varchar(50)
)
INSERT INTO items
(item_name)
VALUES
('printer')
or in MySql :
CREATE TABLE `items` (
`item_id` INT( 11 ) AUTO_INCREMENT ,
`item_name` VARCHAR( 50 ) ,
INDEX ( `item_id` )
);
INSERT INTO `items` VALUES('','printer');
would create a row where item_id having an auto generated value.
In this case one of the solutions would be to modify your source table (create an empty text or integer column) before creating the script to be executed.
Then use this column as the identity (auto inc.) when executing the script so the values will be generated.
Otherwise, if you don't want problems inserting an explicit existing value that you want to keep you can also simply execute the script to create the table with a integer column (that contains the values) then after you change the type of the column to be identify (auto increment) or by changing it after with your DBMS, but this is more delicate.
With MySql it would look like this :
ALTER TABLE items MODIFY item_id INT auto_increment, ADD INDEX (item_id);
The add index have been added because with MySql auto_increment have to be a key field.
With MS SQL simply insert the folowing statement before running the script :
SET IDENTITY_INSERT items ON
Where 'items' is the name of the table.
you will not have errors messages.
For example :
Creating the table :
CREATE TABLE items
(
item_id int IDENTITY(10,2),
item_name varchar(50)
)
By default, values cannot be inserted into an identity field. The statement:
INSERT INTO items
(item_id, item_name)
VALUES
(21, 'printer')
Will fail with error:
Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in
table 'items' when IDENTITY_INSERT is set to OFF.
To force the value, the IDENTITY_INSERT needs to be set ON:
SET IDENTITY_INSERT items ON
INSERT INTO items
(item_id, item_name)
VALUES
(21, 'printer')
Remember you can remove the column before or after the table is created with the following statement:
ALTER TABLE items
DROP COLUMN item_id
And to add it:
ALTER TABLE items
ADD item_id int identity(10,2)
You see, there's many avenues for dealing with identity (auto inc.) fields, make tests with one or two records before filling your database, I hope this info will helps,
Dave.
> Would it be possible to add an option to skip identity fields when creating the scripts?