Keys and Data Types - SQL

Categories

Primary Key

In SQL, a primary key is a special relational database table column (or a combination of columns) designated to uniquely identify each record in the table. Here's what you need to know about primary keys:

  • Uniqueness: Every value in a primary key column must be unique within the table. This ensures that no two rows have the same primary key value.

  • Non-null: A primary key column cannot contain NULL values. Each row must have a value in the primary key column(s).

  • Single or Composite: A primary key can consist of a single column or multiple columns. When it consists of multiple columns, it's called a composite primary key.

  • Automatically Indexed: Typically, when you define a primary key on a column, the database management system (DBMS) automatically creates an index on that column(s) for faster data retrieval.

  • Referential Integrity: Primary keys are often used as references in foreign keys of other tables, establishing relationships between tables and enforcing referential integrity.

Foreign Key

In SQL, a foreign key is a column or combination of columns in one table that uniquely identifies a row of another table. It establishes a relationship between two tables based on the values of those columns. Here's what you should know about foreign keys:

  • Referential Integrity: Foreign keys enforce referential integrity by ensuring that every value in the foreign key column(s) matches a value in the primary key column(s) of the referenced table, or is NULL if allowed.

  • Parent-Child Relationship: The table containing the primary key is called the parent table, and the table containing the foreign key is called the child table. The foreign key in the child table references the primary key in the parent table.

  • CASCADE Options: When defining foreign key constraints, you can specify actions to be taken upon certain events, such as deleting or updating records in the parent table. Common options include CASCADE, SET NULL, RESTRICT, and NO ACTION.

  • Indexed Automatically: Like primary keys, foreign keys are usually indexed automatically by the database management system (DBMS) for efficient querying.

Data Types

In SQL, data types are used to specify the type of data that can be stored in each column of a table. Different database management systems (DBMS) support various data types, but there are some common ones found across most SQL implementations. Here's an overview of the most common data types:

Numeric Types:

  • INT: Integer type for whole numbers.

  • DECIMAL(p, s), NUMERIC(p, s): Fixed-point numbers with precision p and scale s.

  • FLOAT(p), REAL, DOUBLE PRECISION: Floating-point numbers with precision p.

  • BIT, BOOLEAN: Boolean values (true or false).

Character String Types:

  • CHAR(n): Fixed-length character string with length n.

  • VARCHAR(n): Variable-length character string with maximum length n.

  • TEXT: Variable-length character string with no specified maximum length.

Date and Time Types:

  • DATE: Date value (year, month, day).

  • TIME: Time value (hour, minute, second).

  • DATETIME, TIMESTAMP: Date and time value.

Binary Data Types:

  • BINARY(n): Fixed-length binary string with length n.

  • VARBINARY(n): Variable-length binary string with maximum length n.

  • BLOB: Binary large object for storing large binary data.

Other Data Types:

  • ENUM: Enumeration data type, allowing a predefined set of values.

  • SET: Set data type, similar to ENUM but allowing multiple values to be selected.

  • JSON: Stores JSON (JavaScript Object Notation) data.

  • GEOMETRY, GEOGRAPHY: Used for storing spatial data.