SQL INSERT statement
Automatically generated values using SQL
With many database engines, you have the ability to create a column that will be automatically created whenever information is saved to the column.
For example, with Microsoft SQL Server (as well as Sybase ), you can create a column that is referred to as an identity column.
When information is stored in this type of column, the database engine automatically assigns the next available value to the column.
One example of where this is commonly used is the automatic creation of an ID such as the customer ID that you created in the sample table.
By creating the table column as an identity type, you can let the server number the records for you.
You will need to know what columns in your tables are established this way because, when you do an insert, you must not provide values for those columns.
Because the system generates them automatically, you will receive an error message if you insert such values explicitly in the database table.
Simply omit them in your INSERT statement and you will be all set.
How does MySQL treats indexes?
MySQL treats indexes as optional components of a table, which is why you must use the alter table command to add or remove an index. Other database servers, including SQL Server and Oracle Database, treat indexes as independent schema objects. For both SQL Server and Oracle, therefore, you would generate an index using the create index command, as in:
CREATE INDEX dept_name_idx
ON department (name);
As of MySQL version 5.0, a create index command is available, although it is mapped to the alter table command.
mysql> SHOW INDEX FROM department \G *************************** 1. row
*************************** 1. row ***************************
Table: department
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: dept_id
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_Comment:
*************************** 2. row ***************************
Table: department
Non_unique: 1
Key_name: dept_name_idx
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 3
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_Comment:
2 rows in set (0.01 sec)
The output shows that there are two indexes on the department table: one on the dept_id column called PRIMARY ,
and the other on the name column called dept_name_idx . Since I have created only one index so far (dept_name_idx ),
you might be wondering where the other came from; when the department table was created, the create table statement included a constraint naming the dept_id column as the primary key for the table. Here's the statement used to create the table:
CREATE TABLE department
(dept_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
CONSTRAINT pk_department PRIMARY KEY (dept_id) );
When the table was created, the MySQL server automatically generated an index on the primary key column, which, in this case, is dept_id , and gave the index the name PRIMARY . If, after creating an index, you decide that the index is not proving useful, you can remove it by means of the following:
mysql> ALTER TABLE department
-> DROP INDEX dept_name_idx;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
NOTE
SQL Server and Oracle Database users must use the drop index command to remove an index, as in:
DROP INDEX dept_name_idx; (Oracle)
DROP INDEX dept_name_idx ON department (SQL Server)
MySQL now also supports a drop index command.
