Create Table In MySQL
In relational database management systems, a database table is a set of data elements
where in it resembles the structure of a spreadsheet. A specific cell can be found at
the intersection of a specific vertical row and specific vertical column. Each column
is identifiable by name. A table will have specific number of columns. However, the data
that are stored in that table are represented in each row and this can grow endlessly.
The CREATE TABLE statement follows the syntax of the Structured Query Language with a few
difference from it. Lets use use the
database we created in the previous lesson.
Let us now create 2 related tables.
The above statement will create a table named as 'ACCOUNT'. It has 3 columns named as 'ACCOUNT_ID',
'EMAIL', and 'CREATE_DATE'. The 'ACCOUNT' is an 'INT' data type. This means, only integers are allowed.
It is defined as 'PRIMARY KEY' which means, this column should be unique in this table. All rows should
have a unique value for the 'ACCOUNT' column. The 'AUTO_INCREMENT' means, every time a row is inserted into
the table, the 'ACCOUNT' column will be populated with the next integer value that the table has used
previously. The 'NOT NULL' keyword indicates that the column should always have a value. The next column is
'EMAIL' which is a 'VARCHAR' type. It can accomodate 65 characters as defined in the CREATE TABLE statement.
The 'CREATE_DATE' is a 'DATE' type.
CREATE TABLE ACCOUNT (ACCOUNT_ID INT PRIMARY KEY
AUTO_INCREMENT NOT NULL, EMAIL VARCHAR(65) NOT NULL,
The above statement will create a table with 5 columns. The new keywords used here are 'DECIMAL', 'FOREIGN KEY' and 'REFERENCES'.
The 'DECIMAL' is a data type for numbers that contains decimal places. The parameter '10' means it can contain digits. The parameter '2'
means it can contain 2 decimal places. The 'FOREIGN KEY' means that the column defined in this table is a primary from another table.
The 'REFERENCES' keyword is followed by the name of the table where the foreign key is declared and the parameter is the name of the
primary key column.
CREATE TABLE ORDERED_ITEM (ORDERED_ITEM_ID INT NOT NULL
AUTO_INCREMENT PRIMARY KEY, DESCRIPTION VARCHAR(100)
NOT NULL, PRICE DECIMAL(10,2) NOT NULL, ORDER_DATE DATE,
ACCOUNT_ID INT NOT NULL, FOREIGN KEY(ACCOUNT_ID)
Drop Table In MySQL
The DROP TABLE statement is for deleting or removing the table completely from the database. When we drop tables, we need to make sure
we drop the table with the most dependencies are. In our example, 'ORDERED_ITEM' should be dropped first before 'ACCOUNT'. The reason is,
if we drop 'ACCOUNT' first, the foreign key in 'ORDERED_ITEM' will no longer be available because the table it reference to is no
longer existing. Here are the drop statements.
DROP TABLE ORDERED_ITEM;
DROP TABLE ACCOUNT;