SQL Examples
SQL Create Table
Creating a SQL Table
Creating a table with PRIMARY KEY constraints defines structured storage.
Introduction to SQL Create Table
The SQL CREATE TABLE statement is used to create a new table in a database. Tables are essential components of a database as they hold the data in a structured format. A table consists of columns and rows, where each column represents a specific data field, and each row represents a data entry.
In this tutorial, we will focus on creating tables with PRIMARY KEY constraints that ensure the uniqueness and integrity of the data.
Basic Syntax of CREATE TABLE
The basic syntax for creating a table in SQL is as follows:
Here, table_name is the name of the table you want to create, and each column has a specified datatype. The PRIMARY KEY constraint is used to uniquely identify each row in the table. It ensures that no duplicate values are entered in the column(s) defined as the primary key.
Example: Creating a Simple Table
Let's create a simple table named Students to store information about students. The table will have the following columns:
- student_id: An integer that uniquely identifies each student and serves as the primary key.
- first_name: A string to store the student's first name.
- last_name: A string to store the student's last name.
- birth_date: A date to store the student's date of birth.
In this example, the student_id column is the primary key, which means it must contain unique values and cannot be NULL. The other columns, first_name, last_name, and birth_date, are used to store additional information about each student.
Adding Constraints to Ensure Data Integrity
Constraints are rules applied to columns to enforce data integrity. In addition to PRIMARY KEY, other common constraints include:
- NOT NULL: Ensures that a column cannot have a NULL value.
- UNIQUE: Ensures all values in a column are different.
- CHECK: Ensures all values in a column satisfy a specific condition.
- FOREIGN KEY: Ensures referential integrity between tables.
These constraints can be added to a table during its creation to maintain the accuracy and reliability of the data.
Conclusion
Creating tables with proper constraints is crucial for maintaining a well-structured and reliable database. The CREATE TABLE statement allows you to define the structure of your data, ensuring that each entry is unique and valid. Understanding and utilizing primary keys and other constraints will help you design efficient databases that meet your application's needs.
SQL Examples
- Simple Select
- Filtered Query
- Sorted Query
- Joined Query
- Aggregate Report
- Subquery Example
- Correlated Subquery
- Insert Data
- Update Data
- Delete Data
- Create Table
- View Creation
- String Manipulation
- Date Calculations
- JSON Query
- Window Function
- CTE Example
- Pivot Report
- Union Query
- Paginated Query
- Hierarchy Query
- Sales Report
- User Management
- Inventory Query
- Search Query
- Dynamic Filter
- Error Handling
- Data Export
- Data Import
- Table Backup
- Query Logging
- Conditional Aggregation
- Cross Tabulation
- Previous
- Delete Data
- Next
- View Creation
