How to Create a Table in SQL with Constraints and Best Practices

Create a Table

Write a SQL query to create a table with specific columns and constraints.

Solution:

-- Create a table named "Employees" with columns for ID, Name, Age, and Salary.
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY, -- Define a primary key column for unique identification.
    Name VARCHAR(100) NOT NULL, -- Name cannot be null.
    Age INT CHECK (Age > 0),    -- Age must be greater than 0.
    Salary DECIMAL(10, 2)       -- Salary is stored as a decimal with 2 decimal places.
);

Explanation:

1. Purpose of the Query :

1. The goal is to define a new table called Employees with specific columns and constraints.
2. This demonstrates how to structure a table with proper data types and rules to ensure data integrity.

2. Key Components :

1. CREATE TABLE Employees : Specifies the creation of a new table named Employees.

2. EmployeeID INT PRIMARY KEY : Defines the EmployeeID column as an integer and sets it as the primary key to ensure each record is uniquely identifiable.
3. Name VARCHAR(100) NOT NULL : Defines the Name column as a variable-length string with a maximum of 100 characters and ensures it cannot contain null values.
4. Age INT CHECK (Age > 0) : Defines the Age column as an integer and adds a constraint to ensure the value is greater than 0.
5. Salary DECIMAL(10, 2) : Defines the Salary column as a decimal number with up to 10 digits, 2 of which are after the decimal point.

3. Why use Constraints? :

1. Constraints like PRIMARY KEY, NOT NULL, and CHECK help maintain data integrity by enforcing rules on the data being inserted or updated.
2. For example, the CHECK (Age > 0) ensures that invalid ages (e.g., negative numbers) cannot be entered into the database.

4. Real-World Application :

1. In a company database, this table could store employee information, ensuring that each employee has a unique ID, a valid name, a positive age, and a precise salary value.

Additional Notes:

• Defining primary keys and constraints helps in organizing data efficiently and avoiding redundancy.
• Scenarios where these constraints are critical, such as:
• Ensuring unique identifiers for employees (PRIMARY KEY).
• Preventing incomplete data entry (NOT NULL).
• Validating data ranges (CHECK).

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: SQL DDL Exercises Home
Next SQL Exercise: How to add a new Column to an Existing SQL Table.