Understanding how to create table structures in SQL is essential for designing relational databases that store data effectively and accurately.
What is SQL DDL?
SQL Data Definition Language (DDL) is a category of SQL commands used to define and manage the structure of database objects, particularly tables. DDL commands are not used to manipulate the data inside the tables but rather to define the schema—the structure—of the database itself.
The most common DDL command is CREATE TABLE, which allows database designers to set up the columns, data types, and rules for the data that will be stored. Other DDL commands include ALTER TABLE and DROP TABLE, but the primary focus here is on the syntax and usage of CREATE TABLE.
Using DDL effectively means carefully choosing the right data types, constraints, and relationships between tables to ensure that the database is both efficient and accurate. A well-designed table structure prevents data duplication, maintains data integrity, and makes data easier to retrieve and update.
The CREATE TABLE Statement
Basic Syntax and Structure
The CREATE TABLE statement is used to define a new table. It includes the table’s name, a list of columns, and optional constraints that enforce rules on the data.
General syntax:
css
Practice Questions
FAQ
When an INSERT or UPDATE operation violates a constraint like UNIQUE or CHECK, the database management system (DBMS) rejects the operation and returns an error. The data is not inserted or updated, and the integrity of the table is preserved. For example, if a table has a UNIQUE constraint on an email column, trying to insert a duplicate email that already exists will result in a constraint violation error. Similarly, a CHECK constraint such as CHECK (age >= 18) ensures that only values satisfying the condition are accepted. If you attempt to insert an age of 17, the DBMS prevents it. These constraints are vital for enforcing business rules and protecting against invalid or inconsistent data. They are automatically enforced by the database at runtime, meaning that developers do not need to write additional logic in application code to handle these conditions. Violations typically halt the query execution unless handled with appropriate error-catching techniques.
Yes, you can add both primary and foreign keys to an existing table using the ALTER TABLE statement. This is useful when you initially create a table without constraints and later decide to enforce referential or uniqueness rules. To add a primary key after creation, use:
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
To add a foreign key, the syntax is:
ALTER TABLE table_name ADD FOREIGN KEY (column_name) REFERENCES other_table(primary_key_column);
However, before adding a primary key, you must ensure that the column contains only unique and non-null values; otherwise, the operation will fail. When adding a foreign key, all values in the foreign key column must already exist in the referenced table. Adding keys after table creation is common during the database development process when planning relationships or normalisation stages later on. These changes affect the table structure and are permanent unless the keys are explicitly dropped using ALTER TABLE ... DROP CONSTRAINT.
A primary key and a unique constraint both ensure uniqueness in a column, but they serve different purposes and have distinct rules. A primary key uniquely identifies each row in a table and implicitly includes a NOT NULL constraint, meaning it cannot contain null values. There can only be one primary key per table, although it may consist of multiple columns (a composite key). In contrast, a unique constraint ensures that all values in a column or a group of columns are distinct, but unlike a primary key, it does allow nulls, depending on the SQL dialect. You can have multiple unique constraints in a table, each applying to different columns. For example, a table might use student_id as its primary key and also ensure that the email column is unique. This combination ensures one identifier per student and prevents duplicate email addresses without limiting the table to a single rule of uniqueness.
The DEFAULT constraint allows you to specify a predefined value for a column when no value is provided during an INSERT operation. If an insert statement omits a column with a default, the DBMS automatically uses the specified default value. This is especially useful when most rows are expected to have the same initial value for a field. For example, if a status column is expected to be 'active' by default, the column can be defined as:
status VARCHAR(10) DEFAULT 'active'.
The DEFAULT constraint simplifies insert queries, reduces repetitive data entry, and ensures consistency in data that is often standardised. It is also useful for columns like created_at, which might default to the current date or timestamp using CURRENT_DATE or CURRENT_TIMESTAMP in supported systems. Defaults are automatically applied unless the insert explicitly includes a different value, making them an efficient way to apply business logic at the database level without additional programming.
A composite key is a primary key made up of two or more columns that together uniquely identify each row in a table. This approach is used when no single column provides uniqueness but a combination of columns does. Composite keys are particularly useful in junction tables that manage many-to-many relationships between two entities. For example, in a StudentCourses table, a combination of student_id and course_code might be used as the primary key to ensure that the same student cannot be enrolled in the same course more than once. Composite keys enforce more specific rules about uniqueness and improve data integrity across relationships. However, they can complicate queries because both columns must be used together in joins and filters. When using composite keys, it's essential to ensure both columns are present and correctly referenced in foreign keys elsewhere. Though slightly more complex, they are invaluable for representing real-world relationships accurately in relational database design.
