Table

Introduction

The basic building blocks of a database are tables, which have data. There are rows (records) and columns (fields) in every table. You must specify the columns in a table as well as the data types for each one before you can build it. 

Create table

Syntax

CREATE TABLE table_name (
   column1_name data_type constraints,
   column2_name data_type constraints,
   ...
);

Example

CREATE TABLE Customers (
   customer_id INT PRIMARY KEY,
   first_name VARCHAR(50) NOT NULL,
   last_name VARCHAR(50) NOT NULL,
   customer_name VARCHAR(50) NOT NULL,
   email VARCHAR(100) UNIQUE NOT NULL,
   phone VARCHAR(15),
   address VARCHAR(255),
   city VARCHAR(50),
   province VARCHAR(50),
   zip_code VARCHAR(10),
   country VARCHAR(50),
   customer_status VARCHAR(10),
   date_created DATE
);

Contraints:

• Primary Key: Ensures that the column will have unique values and that no row will have a NULL value in this column. A Primary Key is a unique identifier for a record in a table. It ensures that no duplicate values are present in that column and that the value is not NULL. A table cannot have more than one primary key, but a primary key can be a composite key (i.e., made up of more than one column).

• UNIQUE: Ensures that all values in this column are distinct from each other.

• Not Null: Ensures that no row will have a NULL value in this column.

• Foreign Key: A Foreign Key is a field (or a set of fields) in one table that uniquely identifies a row in another table. It creates a link between two tables by referencing the primary key of another table. Foreign keys are used to enforce referential integrity between related tables. For example, a customer_id in an Orders table might be a foreign key referencing the customer_id in the Customers table.

Note : learn more about SQL datatypes in resources: SQL Data Types

Drop table

The DROP TABLE command is used to delete an entire table from the database. This action is irreversible, that means once a table is dropped, all the data it contained and its structure are permanently removed.

Syntax

DROP TABLE table_name;                              

Example

DROP TABLE Customers;                              

Truncate table

The TRUNCATE TABLE command is used to delete all rows from a table without removing the table itself.

Syntax

TRUNCATE TABLE table_name;                              

Example

TRUNCATE TABLE Customers;                              

Delete Table

If a criteria is met, the DELETE TABLE command can be used to remove particular rows from a table. In contrast to TRUNCATE, you can use the WHERE clause to determine which rows to delete; it does not erase all rows by default. DELETE will remove every row from the table while keeping the table structure if the WHERE clause is left out.

Syntax

DELETE FROM table_name WHERE condition;                             

Example

DELETE FROM Customers WHERE first_name = 'Alex';                             

Differences Between DROP, TRUNCATE and DELETE

Conclusion

Use the CREATE table command to make a table; use the DROP or TRUNCATE/DELETE command to remove a table or its contents. The DROP command deletes the table and its structure permanently, whereas the TRUNCATE/DELETE command just removes the data and keeps the structure as is.

Asked in Interview

• What is a Primary Key? Can a table have more than one primary key?
• What is a Foreign Key? Explain its role in database relationships.
• What are primary and foreign keys? How do they affect table relationships?
• How DROP, TRUNCATE and DELETE are different?