SQL DROP can be used to delete table,indexes and databases

Below given are the SQL DROP dtatements

SQL DROP INDEX – Delete indexes

SQL DROP TABLE – Delete tables

SQL DROP DATABASE – Delete databases

SQL CREATE INDEX  is used to create indexes in tables.

It let the database application to find data fast  .

Example

mysql> CREATE INDEX user_index ON users (uid);
Query OK, 5 rows affected (0.12 sec)
Records: 5  Duplicates: 0  Warnings: 0

SQL DEFAULT is used to insert a default value into a column.

The default value will be added to all new records, if no other value is given.

Example

CREATE TABLE users(uid int NOT NULL,name varchar(255) NOT NULL DEFAULT ‘phpcode’)

SQL CHECK  is used to limit the value range that can be placed in a column.

If you define a CHECK constraint on a single column it allows only certain values for this column.

If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

Example

CREATE TABLE users(uid int NOT NULL,name varchar(255) NOT NULL,CHECK (uid>0))

SQL FOREIGN KEYin one table points to the primary key of another table .

Table – users

+—-+——+
| uid | name |
+—-+——+
|  1 | AAAA |
|  2 | BBBB |
|  3 | CCCC |
|  4 | DDDD |
|  5 | EEEE |
+—-+——+

Table  – orders

+—–+—–+——–+
| oid | uid | items  |
+—–+—–+——–+
|   1 |   1 | Pen    |
|   2 |   2 | Watch  |
|   3 |   3 | shoe   |
|   4 |   4 | mobile |
+—–+—–+——–+

Here the field uid in the table orders points to the column uid in users .

That is  uid in the orders is the foreign key and that in users is primary key

FOREIGN KEY constraint prevent any action that may destroy the relation between the tables.

It also prevent invalid data and make sure that the data to be inserted should be one of the primary key value in another

Example

CREATE TABLE IF NOT EXISTS `orders` (
`oid` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL,
`items` varchar(255) NOT NULL,
PRIMARY KEY (`oid`),
FOREIGN KEY (uid) REFERENCES users(uid)
)