SQLite Core-Operations Data Types and Constraints

SQLite supports multiple data types for storing values in various formats. Common data types include:

Integer types: INTEGER (integers), REAL (floating-point numbers)
String types: TEXT (text), VARCHAR (variable-length strings)
Boolean type: BOOLEAN (logical values)
Date and time types: DATE, TIME, DATETIME
Special type: BLOB (binary data)

These data types determine the range and format of values stored in columns, impacting database performance and data integrity.

Setting Constraints

Constraints are used to ensure the integrity of database data. Common constraint types include:

Primary Key Constraint (PRIMARY KEY)
Ensures that column values are unique and not null, specified using the PRIMARY KEY keyword.
```
CREATE TABLE users (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL
);
```
Foreign Key Constraint (FOREIGN KEY)
Used to establish relationships between tables, ensuring that foreign key values exist in the referenced table.
```
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  user_id INTEGER FOREIGN KEY REFERENCES users(id)
);
```
Unique Constraint (UNIQUE)
Ensures uniqueness of column values and can be applied to single columns or composite columns.
```
CREATE TABLE products (
  product_id INTEGER UNIQUE,
  name TEXT UNIQUE
);
```
Check Constraint (CHECK)
Used to validate that column values meet specific conditions, such as age being greater than or equal to 18.
```
CREATE TABLE employees (
  id INTEGER PRIMARY KEY,
  age INTEGER CHECK (age > 18)
);
```
Default Constraint (DEFAULT)
Sets a default value for columns when no explicit value is provided during insertion.
```
CREATE TABLE contacts (
  id INTEGER PRIMARY KEY,
  name TEXT DEFAULT 'Unknown'
);
```

Example Demonstration

The following example demonstrates how to create tables with constraints in SQLite:

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  age INTEGER CHECK (age >= 18),
  enrollment_date DATE DEFAULT CURRENT_DATE
);

By properly setting data types and constraints, you can ensure a clear database structure along with data consistency and integrity.