Constraints are rules that you can apply to columns in a SQL Server table to enforce data integrity and validity.
Notes
1. Creating Tables
Data Definition Language (DDL) statements in SQL Server are used for defining and managing database objects, including tables. Here's how you create tables:
Example :
Example of creating a "Students" table
CREATE TABLE
Students (
StudentID INTPRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Birthdate DATE,
GPA DECIMAL(10, 2),
IsActive BIT);
In this example, a "Students" table is created with columns for student ID, first name, last name, birthdate, and GPA. The CREATE TABLE statement defines the table structure and data types for each column.
2. Modifying Tables
You can use DDL statements to modify existing tables. Common modifications include adding columns, changing column data types, and adding constraints. Here's how you can modify a table:
a) Adding Columns:
Example :
Example of adding a new column to the "Students" table
ALTER TABLE
Students
ADD
PhoneNumber
VARCHAR(20);
In this example, a new column named "PhoneNumber" is added to the "Students" table.
b) Changing Column Data Types:
You can modify the data type or other properties of an existing column:
Example :
Example of changing the data type of the "GPA" column
ALTER TABLE
Students
ALTER COLUMN
GPA
DECIMAL(4, 2);
c) Adding Constraints:
To remove a column from a table, use the ALTER TABLE statement with the DROP COLUMN clause:
Example :
Example of adding a unique constraint to the "PhoneNumber" column
ALTER TABLE
Students
ADD
CONSTRAINT UK_PhoneNumber UNIQUE (PhoneNumber);
In this example, a unique constraint is added to ensure that phone numbers in the "Students" table are unique.
3. Dropping Tables
Dropping a table removes it from the database. Use the DROP TABLE statement to do this:
Example :
Example of dropping the "Students" table
DROP TABLE
Students;
Caution: Be careful when using DROP TABLE, as it permanently deletes all data and the table structure.
4. Real-world DDL Usage
E-commerce System:
- Creating Tables: Create tables for customers, products, orders, and order details to store e-commerce data.
- Modifying Tables: Add a new column for tracking order statuses. Change the data type of the "Price" column to handle international currencies.
- Dropping Tables: Remove temporary tables used for reporting purposes once they're no longer needed.
Healthcare Management System:
- Creating Tables: Define tables for patients, appointments, physicians, and medical records.
- Modifying Tables: Add a constraint to ensure that appointment dates cannot be in the past. Add a new column to track prescription details.
- Dropping Tables: Remove tables for archived patient data that are no longer required.
5. Key Takeaways
- DDL statements in SQL Server are used for creating, modifying, and dropping database objects like tables.
- CREATE TABLE is used to define the structure of a new table.
- ALTER TABLE allows you to modify an existing table, such as adding or changing columns and constraints.
- DROP TABLE permanently removes a table and its data from the database.
6. Exercise Questions
1. Create a table named "Employees" with columns for employee ID (INT), first name (VARCHAR), last name (VARCHAR), and date of hire (DATE). Define appropriate constraints.
2. Add a new column to the "Products" table called "StockQuantity" to keep track of product stock levels. Set a default value of 0 for this column.
3. Modify the "Customers" table to add a constraint that ensures email addresses are unique.
4. Create a table named "Orders" with columns for order ID (INT), customer ID (INT), order date (DATE), and total amount (DECIMAL). Include primary key and foreign key constraints.
5. In a library database, create a table named "Books" with columns for book ID (INT), title (VARCHAR), author (VARCHAR), and publication date (DATE). Add a constraint to ensure that the title and author together are unique.
Data Manipulation Language (DML) statements in SQL Server are used to interact with data within tables. Here's how you can insert data into a table: