In this article, we will explore the process of creating and altering tables in SQL Server, including an explanation of data types, practical examples, and best practices.
In a relational database, data is organized into tables. Each table comprises rows and columns, where each row represents a unique record, and each column defines a specific attribute of that record. To create a table in SQL, you need to specify the table’s structure, including the column names, data types and constraints such as primary keys.
Keywords:
sql create table, add a column in sql, add table to database sql, alter table create column, create database table in sql, sql for creating a table, sql add column, add column in sql query, add column sql query, alter add column, alter table add column, alter table create column
Table of Contents
Learn SQL online
Best Practices
When creating tables in SQL, consider the following best practices:
- Use descriptive and meaningful column names: Choose column names that are self-explanatory and reflect the data they store. This makes it easier for you and others to work with the database.
- Select appropriate data types: Choose data types that match the nature of the data in each column. Using the right data types ensures data accuracy and storage efficiency.
- Define primary keys: Every table should have a primary key to uniquely identify each record. This ensures data integrity and helps in efficient data retrieval.
- Consider indexing: Depending on your usage patterns, consider adding indexes to columns that are frequently used in search or filtering operations. Indexes can significantly improve query performance.
- Plan for future changes: Anticipate potential changes in your data structure. While you can alter tables later, planning for future needs can save you time and effort.
Data types
When you create or alter a table the following data types are available however this can vary depending on the database you are working with. you will need to specify the datatype for each column when you create it.
INT: The
INTdata type is used for whole numbers. It typically represents a 32-bit signed integer, allowing values from -2,147,483,648 to 2,147,483,647. Smaller versions likeTINYINT(8-bit) andSMALLINT(16-bit) are also available for more specific use cases.BIGINT: This data type represents a larger whole number and is typically a 64-bit signed integer. It allows for significantly larger values compared to
INT.NUMERIC/DECIMAL: These data types are used for storing fixed or floating-point numbers. You can specify the precision (total number of digits) and scale (number of decimal places) for these types, allowing precise control over numeric data.
FLOAT/REAL: Floating-point data types are used to store approximate numerical values. They are suitable for scientific and engineering calculations where precision is less critical.
CHAR/VARCHAR: Character data types are used for storing text or strings.
CHARcolumns store fixed-length strings, whileVARCHARcolumns store variable-length strings. For example,VARCHAR(255)can store up to 255 characters.TEXT: The
TEXTdata type is used for storing large text or character data. It’s suitable for columns that might contain extensive textual content, such as long descriptions.DATE/DATETIME/TIMESTAMP: These data types are used for date and time values.
DATEstores a date,DATETIMEstores a date and time, andTIMESTAMPstores a timestamp with both date and time information.BOOLEAN/BOOL: The
BOOLEANorBOOLdata type is used for storing true/false or binary (0/1) values. It’s commonly used for representing logical conditions or binary choices.BINARY/VARBINARY: Binary data types are used for storing binary data, such as images or files.
BINARYcolumns store fixed-length binary data, whileVARBINARYcolumns store variable-length binary data.ENUM: This data type is specific to some SQL dialects, like MySQL. It allows you to define a list of permissible values that a column can hold.
GEOMETRY/GEOGRAPHY: These data types are used for storing spatial data, including points, lines, polygons, and geographical information.
JSON: The
JSONdata type is used for storing JSON (JavaScript Object Notation) data. It’s helpful when dealing with semi-structured or unstructured data.XML: The
XMLdata type is used for storing XML documents, making it suitable for databases that work with XML data.ARRAY: Some SQL dialects, like PostgreSQL, support array data types for storing lists or arrays of values of the same data type. For instance, an
INT[]represents an array of integers.INTERVAL: The
INTERVALdata type is used for storing time intervals or durations, which can be handy for date and time calculations.CLOB/BLOB: These data types are used for storing large text (CLOB) and binary (BLOB) objects, respectively. They are suitable for handling large documents, images, or multimedia content.
Primary and Foreign keys
One of the things you will normally need to define when creating a table is foreign and primary keys.
A primary key is a column or a set of columns in a relational database table that uniquely identifies each record (row) in that table. It enforces data integrity by ensuring that every record is distinct and identifiable. This means that no two rows can have the same values in the primary key column(s).
A foreign key is a column or a set of columns in one table that is used to establish a link between the data in two tables. It enforces referential integrity by ensuring that the data in the foreign key column(s) matches the data in the primary key of another table. In essence, it creates a relationship between tables, allowing data to be shared and linked.
Constraints
SQL database constraints are rules and conditions applied to tables and columns to ensure data integrity and reliability. They serve as safeguards, guaranteeing that the data stored in a database conforms to specific criteria. Below are the different type of constraints that can be applied:
1. Primary Key Constraint
A primary key constraint ensures the uniqueness of values in a specific column or combination of columns. It identifies a unique record in a table, preventing duplicate or null values. This constraint is crucial for maintaining data integrity and facilitating efficient data retrieval through table joins.
2. Unique Constraint
A unique constraint guarantees the uniqueness of values within a column. Unlike a primary key, a unique constraint allows for a single null value. It enforces the uniqueness of data without requiring a specific identifier for each record.
3. Check Constraint
A check constraint defines a condition that values in a column must satisfy. For example, you can use a check constraint to ensure that a numeric column only contains positive values. It helps maintain data quality by limiting the types of data that can be inserted.
4. Foreign Key Constraint
A foreign key constraint establishes a relationship between two tables. It enforces referential integrity by ensuring that values in a column correspond to values in another table’s primary key. Foreign keys facilitate the creation of complex data models and maintain consistency in related data.
5. Default Constraint
A default constraint specifies a default value for a column if no value is explicitly provided during insertion. It simplifies data entry and guarantees that every record has a valid value in that column.
6. Not Null Constraint
A not null constraint ensures that a column cannot contain null values. It enforces data integrity by requiring every record to have a valid entry in that column.
Implementing Database Constraints
To implement constraints, SQL provides specific clauses within the CREATE TABLE statement as follows:
- PRIMARY KEY: Used to define a primary key constraint.
- UNIQUE: Used to define a unique constraint.
- CHECK: Used to define a check constraint.
- FOREIGN KEY: Used to create a foreign key constraint.
- DEFAULT: Used to specify a default value.
- NOT NULL: Used to enforce the not null constraint.
Creating a Table in SQL
To create a table in SQL, you use the CREATE TABLE statement. The basic syntax for creating a table is as follows:
CREATE TABLE table_name (
column1 data_type constraint1,
column2 data_type constraint2,
...
columnN data_type constraintN
);
In this syntax:
table_nameis the name you want to give to your table.column1,column2, and so on represent the column names.data_typespecifies the data type for each column.constraint1,constraint2, and so on are constraints applied to the columns.
Now, let’s dive into different types of constraints and how to apply them.
1. Primary Key Constraint
A primary key constraint ensures that each row in a table is unique and is used to identify a specific record. Here’s how you can create a table with a primary key constraint:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
DepartmentID INT
);
In this example, the EmployeeID column is defined as the primary key, which means it must contain a unique value for each employee.
2. Foreign Key Constraint
A foreign key constraint establishes a relationship between two tables, ensuring referential integrity. Here’s how you can create a table with a foreign key constraint:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
OrderDate DATE,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
In this example, the CustomerID column in the Orders table is defined as a foreign key, referencing the CustomerID column in the Customers table.
3. Unique Constraint
A unique constraint ensures that the values in a column are unique but does not require them to be the primary key. Here’s an example of creating a table with a unique constraint:
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50) UNIQUE,
Age INT
);
In this case, the StudentName column has a unique constraint, ensuring that no two students have the same name.
4. Check Constraint
A check constraint defines a condition that values in a column must meet. Here’s how you can create a table with a check constraint:
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
ProductName VARCHAR(50),
Price DECIMAL(10, 2) CHECK (Price > 0)
);
In this example, the CHECK constraint ensures that the Price column contains values greater than zero.
Altering a table in SQL
ALTER TABLE table_name
[alter_command];
The following are the different types of common table alterations that you might want to apply:
1. ADD COLUMN:
This command allows you to add a new column to an existing table. Use the ADD keyword followed by the datatype and any constraints you want to apply to the column such as making it a primary key.
ALTER TABLE table_name
ADD column_name data_type [constraints];
2. ALTER COLUMN:
This command is used to modify the data type of an existing column. Use the ALTER COLUMN keyword followed by the column you want to alter followed by the data type that you want to change it to.
ALTER TABLE table_name
ALTER COLUMN column_name new_data_type;
3. DROP COLUMN:
To remove a column from a table, you can use the DROP COLUMN keyword and then specify the column you want to DROP:
ALTER TABLE table_name
DROP COLUMN column_name;
Constraints, such as primary keys, foreign keys, and unique constraints, can be added using the ADD CONSTRAINT statement. The syntax depends on the type of constraint.
When you make alterations to existing tables with data in you may find that you are stopped from executing a statement if it violates existing constraints. If this is the case you will either need to remove the constraint first or delete the data in the table that is causing the constraint to be triggered.
Conclusion
In this article, we’ve covered creating and altering tables, choosing data types, examined constraints, and talked about primary and foreign keys in SQL.
With a solid understanding of table design and data types, you can effectively manage your data in SQL Server, ensuring that your databases are efficient, scalable, and maintainable.
Free online SQL course sections
Please see the other SQL learning articles below as part of this learning SQL online course.
If you would like an instructor lead SQL course that goes into more detail with worked examples then we also provide a video course to learn SQL online hosted on UDEMY.
