---[REF]---"style":"heading"---[REF]--- ---[SIDEBAR]--- "type":"objectives", "title":"Learning Objectives", "text":"After completing this lesson, the successful student will be able to use SQL statements to:

1. Create database tables.
2. Make changes to database tables.
3. Remove tables from a database.

" ---[SIDEBAR]--- You can use structured query language to do more than retrieve and manipulate data stored in database tables. It can also be used to perform all database tasks. These include creating, editing, and deleting database tables. Most DMBS have interactive tools for completing these tasks, but it is helpful to have some basic understanding of the SQL statements that performs these tasks, because the syntax highlights some important database concepts. This lesson will highlight how to create, delete, and edit database tables. ## Creating Tables As with all of the SQL statements covered in this resources, creating database tables involves using SQL keywords. The statement for creating a database table starts with the keywords CREATE and TABLE. The keyword CREATE is used when you wish to add a new object to the database. A table is a database object. This is why the keyword TABLE is used after CREATE. Using the keyword CREATE tells the DBMS that a new object will be added to the database, The keyword TABLE specifies that the object will be a table. ---[SIDEBAR]--- "type":"term", "title":"CREATE", "text":"The SQL keyword used to add a new object to the database." ---[SIDEBAR]--- ---[SIDEBAR]--- "type":"term", "title":"TABLE", "text":"The SQL keyword that, when used with CREATE, specifies that a new table object will be added to the database." ---[SIDEBAR]--- When you add a table to the database, you provide the DBMS of the details of the table. These details include: 1. The name of the table 2. The list of columns that will be stored on the table, including about the column. 3. The columns that will be used at the primary and foreign keys of the table. The generic syntax for adding a table to the database is: CREATE TABLE *tablename* (      *column1 name with column details*,      *column2 name with column details*,      *optional table details* ) The tablename is specified after the keywords CREATE and TABLE. The individual columns of the table are detailed as a comma separated list in parentheses after the table name. Primary and foreign keys are defined either as part of the column details or in the optional table-level details after the columns are defined. ---[SIDEBAR]--- "type":"tip", "title":"DBMS Table Creation Differences", "text":"There are some differences in how the difference DBMS handle creating tables. Check the specifics of the DBMS for details on how to create tables in the DBMS you are using." ---[SIDEBAR]--- ### Table Names The table names you use in a database must be unique within the database (two tables in the same database can't have the same name). Additionally, a table name cannot be a reserved word (you can't name a table SELECT). While these are the only rules for naming database tables, there are a few suggestions to keep in mind: 1. Table names should be descriptive. A table name should be descriptive enough that it conveys what is stored in the table to users. If the table names used in a database are too ambiguous, users will find the database difficult to use. 2. Table names should be brief. You have no doubt felt that writing SQL statements can be tedious. If table names are too verbose, the task of writing SQL statements becomes more difficult. 3. Table names may include any character typically allowed in a filename (no '/', '\', or '.'), though it is best to not include spaces in table names. 4. Use a naming convention for all table names in the database. Table names should all look alike. For example, if one table name is in singular form (*customer* instead of *customers*), all should be. Naming conventions make using a database easier, because the different elements of the database are predictable. For example, if you have some tables with a singular name and others are plural, users will need to keep consulting the database diagram to know the table names needed to write queries of the data in those tables. *The next next suggestion is somewhat debated by database experts, but is practical advice for avoiding common problems users face when using a database.* 5. Use "snake case" for table names. Snake case is the common name for replacing all spaces in a names with the underscore character. For example, a table that stores museum hours should be named, "museum_hours" (without the quotes). Snake case is generally easier to read than other name conventions (such as CamelCase - capitalizing the first letter of new words in a name without spaces), and often reduces the potential for ambiguity in table names. An example of the syntax you would use to add a table named vendor to the database is: CREATE TABLE vendor (...) ### Defining Table Columns The columns are defined using a comma separated list. When defining columns, you specify a column name, the type of data that will be stored in the column, and any constraints you wish to define for how data will be stored in the column. While each column must be defined with a name and data type, any additional constraints are optional. #### Column Names Column names must be unique for each table. The suggestions for naming columns are the same as those for naming tables. Additionally, it is a good idea begin each primary key column with the name of the table. For example, the primary key for museum table should be named museum_id rather than just id. #### Data Types You should also define the type of data that will be stored in each column. While each DBMS has a different set of data types, they all allow you to configure a column to store text, numbers (both decimal and integer numbers), and files. If you have a question about the types of data that can be stored using your DBMS, consult the documentation.

Data types used in SQLite

| Data Type | Description | |:---------:|--------------------------------------------------------------------------------| | Integer | Whole numbers | | Real | Floating point (decimal numbers)values | | Text | String of text | | Blob | Blob of data (stored exactly as it was input - for example a file) | | NULL | NULL values |

An example of the syntax used to define the name column on the vendor table with a data type of text is: CREATE TABLE vendor (     name TEXT ) #### Column Constraints You can also add additional constraints on the values can be added to a column. These constaints are enforced by the DBMS. When a user tries to add a new row to the database table that violates one of these constraints, the DBMS prohibits the row from being saved to the table. The first constraint that can be added to a column is the unique constraint. The unique constraint is applied using the keyword UNIQUE. The unique constraint ensures that each value for that column is different than all of the other values in that column. If a user attempts to add a new row to the table with a duplicate value for that column, the DBMS will prohibit the new record from being added to the table. ---[SIDEBAR]--- "type":"term", "title":"UNIQUE", "text":"Constraint added to a column that ensures that all each value in the table for that column is different than all other values for that column." ---[SIDEBAR]--- You can also add a constraint that ensures that a column has a value when a new row is added to the table. This constraint is added using the keywords NOT NULL. This constraint essential makes values for this column required for each row that is added to the table. ---[SIDEBAR]--- "type":"term", "title":"NOT NULL", "text":"Constraint added to a column that ensures that a column has a a value when a new row is added to the table." ---[SIDEBAR]--- A column can also be designed as the primary key using a column constraint. This is done using the keywords PRIMARY KEY. Recall that when a column is a primary key, values for that column must be both unique and not null. Most DBMS will also add features to primary key columns. These features are not discussed in this resource. ---[SIDEBAR]--- "type":"term", "title":"PRIMARY KEY constraint", "text":"Constrains the values for a column to be both unique and not null. Additional features will likely be added to primary key columns by the DBMS." ---[SIDEBAR]--- An example of the syntax used to define the vendor table with a vendor_id as interger data and the primary key and the name column with a data type of text with the constraint that the name be NOT NULL is: CREATE TABLE vendor (     vendor_id INTEGER PRIMARY KEY,     name TEXT NOT NULL ) You can also add additional custom constraints to the columns of a database table. These are added using the keyword CHECK. CHECK constraints create a logical test that the values for a column must satisfy before a new row can be added to a database table. For example, you could create a check constraint to ensure that the values for a new employee's salary are within an appropriate range. ---[SIDEBAR]--- "type":"term", "title":"CHECK", "text":"Custom constraint that ensures the values for a column satisfy a logical test before a new row can be added to a table." ---[SIDEBAR]--- ### Defining Table Options After the columns have been defined in the table creation statement, table-level constraints can be added. The two table-level constraints that are primary and foreign keys. Generally, a primary key is designated when the column is defined. However, if multiple columns are used together as a composite primary key, the primary key must be added as a table-level constraint. This is done using the keywords PRIMARY KEY followed by a comma separated list of the fields that make up the composite primary key in parentheses. An example of the syntax used to define the composite primary key with the product_id, sale_id, and product_size columms is: PRIMARY KEY (product_id, sale_id, product_size) Foreign keys are also added as table-level constraints. There are three elements to a foreign key constraint. First, the column to be used as the foreign key is identified. This column must exist on the table. Second, the table that the foreign key references is defined. Finally, constaint must also designate what to do if the referenced values on the related table are deleted from the database. The following syntax demonstrates how the museum_id would be designed as a foreign key on the vendor table referencing the museum table: CREATE TABLE vendor (     vendor_id INTEGER PRIMARY KEY,     ...     museum_id INTEGER,     FOREIGN KEY (museum_id) REFERENCES museum ON DELETE SET NULL ) Notice the set of keywords in the foreign key constraint. The keywords FOREIGN KEY are used to designed the foreign key. The keyword REFERENCES specifies the table that the foreign key relates. ON DELETE SET NULL ensures that if an museum is deleted from the museum table, the values for the corresponding museum_id on the vendor table will be set to NULL. This way the vendor table will not reference an museum that does not exist. Putting all of these pieces together, the table creation statement is generated. A table creation statement to create the vendor table in the museum database would be: CREATE TABLE vendor(     vendor_id INTEGER PRIMARY KEY,     name TEXT NOT NULL,     country TEXT NOT NULL,     museum_id INTEGER,     FOREIGN KEY(museum_id) REFERENCES museum ON DELETE SET NULL ); **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:create-vendor-table ---[SQL]--- CREATE TABLE vendor( vendor_id INTEGER PRIMARY KEY, name TEXT NOT NULL, country TEXT NOT NULL, museum_id INTEGER, FOREIGN KEY(museum_id) REFERENCES museum ON DELETE SET NULL ); ---[SQL]--- The result of executing the query would be a response from the DMBS that the statement executed without errors. When this happens, the table will be added to the database. Of course at this point there would not be any data in the table. The SQL statements used to add data to the blank table will be discussed in ---[REF]---"style":"link","pageId":"lesson-insert"---[REF]---. If there are errors that occur during the execution of the statement the DBMS will respond with the error statements. ## Deleting Tables A common error that occurs when attempting to add a table to a database happens when you try to add a table that already exists in the database. If you wish to replace the existing table, you must remove it from the database first. To remove a table from the database, you use the keywords DROP TABLE. The keyword DROP is used to remove objects from the database. The keyword table is used to designate that a table is being removed. ---[SIDEBAR]--- "type":"term", "title":"DROP", "text":"The keyword used in a SQL statement to remove an object from the database." ---[SIDEBAR]--- When a table is dropped from the database, the table is completed removed including all of the data stored on the table. The syntax used to remove the vendor table you just created from the database would be: DROP TABLE vendor; **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:drop-vendor-table ---[SQL]--- DROP TABLE vendor; ---[SQL]--- ---[SIDEBAR]--- "type":"caution", "title":"Removing Database Tables", "text":"The SQL statement used to remove a table from a database is simple and there is not a command to undo removing a table. Care should be taken when removing tables to prevent accidental deletion and data loss." ---[SIDEBAR]--- ## Editing Tables From time to time, you will need to make changes to an existing database table. The keywords used to change a table are ALTER TABLE. The keyword ALTER is used to change a database object. The syntax for changing a database table is: ---[SIDEBAR]--- "type":"term", "title":"ALTER", "text":"The keyword used in a SQL statement to change an object in the database." ---[SIDEBAR]--- ALTER TABLE tablename *statements that define the changes to be made* For example, you could add or delete a column from an existing table. The syntax to add a column vendor_phone to an existing table named vendor would be: ALTER TABLE vendor ADD vendor_phone TEXT; **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:alter-vendor-add-vendor-phone ---[SQL]--- CREATE TABLE vendor( vendor_id INTEGER PRIMARY KEY, name TEXT NOT NULL, country TEXT NOT NULL, museum_id INTEGER, FOREIGN KEY(museum_id) REFERENCES museum ON DELETE SET NULL ); ALTER TABLE vendor ADD vendor_phone TEXT; ---[SQL]--- Note that if you tried the DROP statement in the last section, your copy of the vendor table will have been deleted from the database. This is why this example creates a vendor table first. If you did not delete the table, you will receive an error when trying to create the vendor table in this example. Similarly, the syntax used to change the table and remove the vendor_phone column would be: ALTER TABLE vendor DROP vendor_phone; **Try the SQL statement.** ---[SQL]--- connection:sql_book_connection schema:canvas diagram:canvas owner:atlas-query result:alter-vendor-remove-vendor-phone ---[SQL]--- ALTER TABLE vendor DROP vendor_phone; ---[SQL]--- Tables can also be altered to define primary and foreign keys after a table is created. ---[SIDEBAR]--- "type":"tip", "title":"Limitations to Altering Tables", "text":"Some DBMS, notably SQLite limit what can be done to change a table. Check with the documentation of the DBMS you are using to know what these limitations might be." ---[SIDEBAR]--- ## Summary This lesson described how to write SQL statements to manipulate database tables. Specifically, it described how to add database tables using the CREATE TABLE keywords. When a table is created, the table columns and table-level constraints are also defined. The lesson also described how to remove and edit existing tables in a database.