Table

1. Creating Tables

There are generally two ways to create database tables:

  1. Most DBMSs come with an administration tool that can be used to create and manage database tables interactively.

  2. Tables may also be manipulated directly with SQL statements.

To create a table using CREATE TABLE, you must specify the following information:

  • The name of the new table specified after the keywords CREATE TABLE.

  • The name and definition of the table columns separated by commas.

  • Some DBMSs require that you also specify the table location.

CREATE TABLE Products
(
    prod_id CHAR(10) NOT NULL,
    prod_price DECIMAL(8,2) NOT NULL,
    prod_desc VARCHAR(1000) NULL
);
CREATE TABLE Orders
(
    order_num INTEGER NOT NULL,
    order_date DATETIME NOT NULL,
    cust_id CHAR(10) NOT NULL
);

SQL Constraints

SQL constraints are used to specify rules for the data in a table.

Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

Constraints can be column level or table level. Column level constraints apply to a column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

  • NOT NULL

    • Ensures that a column cannot have a NULL value

  • UNIQUE

    • Ensures that all values in a column are different

  • PRIMARY KEY

    • A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table

  • FOREIGN KEY

    • Uniquely identifies a row/record in another table

  • CHECK

    • Ensures that all values in a column satisfies a specific condition

  • DEFAULT

    • Sets a default value for a column when no value is specified

  • INDEX

    • Used to create and retrieve data from the database very quickly

More about NULL: Every table column is either a NULL column or a NOT NULL column, and that state is specified in the table definition at creation time. A column that allows NULL values also allows rows to be inserted with no value at all in that column. A column that does not allow NULL values does not accept rows with no value. In some DBMS, NULL is the default setting.

# Specifying Default Values
CREATE TABLE OrderItems
(
    order_num INTEGER NOT NULL,
    order_item INTEGER NOT NULL,
    prod_id CHAR(10) NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 1,
    item_price DECIMAL(8,2) NOT NULL
);

# Setting Primary Keys
CREATE TABLE Vendors
(
    vend_id CHAR(10) NOT NULL PRIMARY KEY,
    vend_name CHAR(50) NOT NULL,
    vend_address CHAR(50) NULL,
    vend_city CHAR(50) NULL,
    vend_state CHAR(5) NULL,
    vend_zip CHAR(10) NULL
    vend_country CHAR(50) NULL
);

# Setting Foreign Keys
CREATE TABLE Orders
(
    order_num INTEGER NOT NULL PRIMARY KEY,
    order_date DATETIME NOT NULL,
    cust_id CHAR(10) NOT NULL REFERENCES Customers(cust_id)
);

2. Updating Tables

To change a table using ALTER TABLE, you must specify the following information:

  • The name of the table to be altered after the keywords ALTER TABLE. (The table must exist or an error will be generated.)

  • The list of changes to be made.

2.1 Adding Columns

ALTER TABLE Vendors
ADD vend_phone CHAR(20);

2.2 Dropping Columns

ALTER TABLE newpro
DROP COLUMN prod_name;

2.3 Modify Columns

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

2.3 Adding Primary Keys

ALTER TABLE Vendors
ADD CONSTRAINT PRIMARY KEY (vend_id);

2.4 Adding Foreign Keys

ALTER TABLE Orders
ADD CONSTRAINT
FOREIGN KEY (cust_id) REFERENCES Customers (cust_id)

2.5 Create a NOT NULL Constraint on a Column

ALTER TABLE Orders
MODIFY cust_id CHAR NOT NULL;

Complex table structure changes usually require a manual move process involving these steps:

  1. Create a new table with the new column layout.

  2. Use the INSERT SELECT statement to copy the data from the old table to the new table. Use conversion functions and calculated fields, if needed.

  3. Verify that the new table contains the desired data.

  4. Rename the old table (or delete it, if you are really brave).

  5. Rename the new table with the name previously used by the old table.

  6. Recreate any triggers, stored procedures, indexes, and foreign keys as needed.

3. Deleting Tables

Tables are deleted using the DROP TABLE statement:

DROP TABLE CustCopy;

The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.

TRUNCATE TABLE table_name;

4. Renaming Tables

We can rename tables using RENAMES TABLE name1 TO name2. to change table's name from name1 to name2.

RENAME TABLE newpro to New_pro;

5. Copying from One Table to Another

To copy the contents of a table into a brand new table (one that is created on-the-fly) you can use the SELECT INTO statement.

SELECT *
INTO newtable
FROM oldtable
WHERE condition;

SELECT column1, column2, column3, ...
INTO newtable
FROM oldtable
WHERE condition;
SELECT *
INTO CustCopy
FROM Customers;

This SELECT statement creates a new table named CustCopy and copies the entire contents of the Customers table into it. Because SELECT * was used, every column in the Customers table will be created (and populated) in the CustCopy table. To copy only a subset of the available columns, explicit column names can be specified instead of the * wildcard character.

MariaDB, MySQL, Oracle, PostegreSQL, and SQLite use a slightly different syntax:

CREATE TABLE CustCopy AS
SELECT * FROM Customers;

SELECT INTO can also be used to create a new, empty table using the schema of another. Just add a WHERE clause that causes the query to return no data:

SELECT * INTO newtable
FROM oldtable
WHERE 1 = 0;
PreviousUpdateNextDatabase

Last updated