SQL Data Definition Language (DDL) and Constraints in SQL

Introduction to SQL

  • SQL Definition: Structured Query Language; a key reason for the success of relational databases.
  • Historical Background: Originates from relational predicate calculus, originally termed as SQUARE. The term SQL is an abbreviation of SEQUEL ("SEQUEL TO SQUARE").

SQL Standards

  • SQL has many standards:
    • SQL-86 (SQL 1)
    • SQL-92 (SQL-2)
    • SQL-1999 introduced core specifications and specialized extensions.
    • SQL-2006 included XML features.
    • SQL-3 (current) began with SQL-1999 but lacks full implementation in any RDBMS.

SQL Command Categories

  • Data Definition Language (DDL): Defines database structure/schemas.
  • Data Manipulation Language (DML): Manages data in schema objects.
  • Data Control Language (DCL): Manages access privileges.
  • Transaction Control Language (TCL): Groups DML statements into transactions.

SQL Data Types

  • Numeric Types:
    • Exact: bit, int, numeric, money
    • Approximate: float, real
  • String Types:
    • Fixed (char), variable (varchar), large (text), Unicode (nchar, nvarchar)
  • Date/Time Types: date, datetime2, time
  • Binary Types: binary, varbinary
  • Other Types: cursor, xml, table, geography, etc.

Key SQL DDL Commands

  • CREATE: Create database objects.
  • ALTER: Change structure of database objects.
  • DROP: Delete database objects.
  • TRUNCATE: Remove all records from a table.
  • RENAME: Rename an object (with limitations in T-SQL).

CREATE Command

  • Syntax:
    ```sql
    CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints]
    ); 
- Constraints can include Primary Key, Not Null, Foreign Key.  

## Specifying Constraints  
- **Key Constraint**: Primary key values must be unique and not null.  
- **Entity Integrity**: A primary key value cannot be null.  
- **Referential Integrity**: Foreign key must reference an existing primary key or be null.  
- **DEFAULT/NOT NULL/ CHECK**: Other restrictions can be applied.  

## Examples  
- **Creating a Table**:

sql
CREATE TABLE DEPARTMENT (
DNAME VARCHAR(10) NOT NULL UNIQUE,
DNUMBER INTEGER PRIMARY KEY NOT NULL,
MGRSSN CHAR(9) REFERENCES EMP,
MGRSTARTDATE CHAR(9)
);
```

Foreign Key Constraints

  • OPTIONS: ON DELETE (NO ACTION, SET NULL, CASCADE), ON UPDATE.
  • Examples: FOREIGN KEY clauses with constraints for data integrity.

Problems with Constraints

  • Must adhere to the defined relationships during data insertion. Constraints can enforce integrity post table definition using ALTER command.

Summary

  • Covered key SQL concepts: Data types, DDL, and how to specify constraints effectively.