Bright Java Tutorial

is brought to you by DoITBright LLC

MySQL

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.
  USE MY_FIRST_DB;
                            
Let us now create 2 related tables.
  CREATE TABLE ACCOUNT (ACCOUNT_ID INT PRIMARY KEY 
      AUTO_INCREMENT NOT NULL, EMAIL VARCHAR(65) NOT NULL, 
      CREATE_DATE DATE);
                            
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 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) 
      REFERENCES ACCOUNT(ACCOUNT_ID));
                            
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.

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;
                            



Back    Next