Database Reference
In-Depth Information
Considering common business situations, Id and similar columns are usually added as the first
column in most tables, and if you have a similar desire, then you need to follow a different approach, as
explained next.
Drop and Re-create the Table
As stated earlier, if you want to have the Id column as the first column in your table, then you have to
define the table from scratch. To do so, you have to drop the table first and then re-create it.
In the query pane, enter the following statement, select it, and press Execute.
DROP TABLE MySqlTable
This will delete or remove the table permanently; if you select the Tables node in Object Explorer,
right-click, and choose Refresh option, you will see that there is no table listed because you just dropped
it.
Now, you will re-create the MySqlTable with an IDENTITY column added to the table definition at
the time of creation itself, as shown in the following statement:
CREATE TABLE MySqlTable
(
Id int IDENTITY (1,1), --Identity makes the column Not Null internally
Name varchar(10) Not Null, --Name value is a must and can't be Null
Age int,
SSN varchar(11),
Date datetime,
Gender char(6)
)
Select this
create table
statement and press Execute; then select the MySqlDb database in Object
Explorer, right-click, and choose Refresh. You will see a fresh MySqlTable created and in it is the first
column, unlike it was added when you used the
alter table
statement.
You can also see the column list by expanding the Tables node and then the Columns node, and you
will be able to see the list of columns, as shown in Figure 3-10.
