Create table: alter table to drop index : Index « Table Index « SQL / MySQL






  1. SQL / MySQL
  2. Table Index
  3. Index

Create table: alter table to drop index

  
/*
mysql> Drop table Product;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE Product
    -> (
    ->    ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
    ->    ModelID SMALLINT UNSIGNED NOT NULL,
    ->    UNIQUE unique_1 (ID, ModelID)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> Describe Product;
+---------+----------------------+------+-----+---------+-------+
| Field   | Type                 | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+-------+
| ID      | smallint(5) unsigned |      | PRI | 0       |       |
| ModelID | smallint(5) unsigned |      |     | 0       |       |
+---------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE Orders
    -> DROP INDEX unique_1;
ERROR 1091 (42000): Can't DROP 'unique_1'; check that column/key exists
mysql> Describe Product;
+---------+----------------------+------+-----+---------+-------+
| Field   | Type                 | Null | Key | Default | Extra |
+---------+----------------------+------+-----+---------+-------+
| ID      | smallint(5) unsigned |      | PRI | 0       |       |
| ModelID | smallint(5) unsigned |      |     | 0       |       |
+---------+----------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)


*/
Drop table Product;

CREATE TABLE Product
(
   ID SMALLINT UNSIGNED NOT NULL PRIMARY KEY,
   ModelID SMALLINT UNSIGNED NOT NULL,
   UNIQUE unique_1 (ID, ModelID)
);

Describe Product;

ALTER TABLE Orders
DROP INDEX unique_1;

Describe Product;








Related examples in the same category

1.Creating a Table with an Index
2.Create index on a column
3.Create table: Make index
4.Create index on table column with order
5.Create index on one column
6.Create index on two columns
7.Create unique index on table columns
8.Create index using btree
9.Handling Duplicate Index Values
10.Eliminating Duplicates by Adding an Index
11.How to include a unique index in the table definition.
12.How to include a regular index in a table definition:
13.Get the descriptive data of the indexes defined on the PENALTIES table.
14.Creating Indexes (CREATE INDEX)
15.Create an index on the AMOUNT column of the PENALTIES table.
16.Get the names of the indexes defined on the PENALTIES table.
17.Defining Indexes at Table-Creation Time
18.The syntax for CREATE INDEX is as follows
19.Removing Duplicates by Adding an Index
20.Removing Indexes
21.Create index of decimal type column
22.Create view for indexes
23.Drop an index by name
24.Add index to int type column
25.To create a unique-valued index, use the UNIQUE keyword instead of INDEX.
26.PRIMARY KEY INDEX_PRIM
27.Create an index and check it
28.Adding or Dropping Indexes
29.There are four types of statements for adding indexes to a table:
30.Hash index
31.Show index for a table
32.Using index in order by clause to indecate the result from subquery
33.Create a table with a nonunique index on the date-valued column Inauguration
34.To include multiple columns in an index
35.Provide index definitions in addition to the column definitions.
36.Modifying Indexes of Existing Tables
37.A table can have multiple indexes.
38.Create a single-column index on c, and the second creates a multiple-column index that includes both c and i