MariaDB Crash Course - Ben Forta [6]
For example, a customers table might store one customer per row. The number of rows in the table is the number of records in it.
* * *
New Term: Row
A record in a table.
* * *
* * *
Note: Records or Rows?
You might hear users refer to database records when referring to rows. For the most part, the two terms are used interchangeably, but row is technically the correct term.
* * *
NULL
Data is stored in rows and columns, and the exact data that may be stored is based on the defined datatype. Columns may also be defined to accept no value, meaning no data at all. In SQL, the term NULL is used to mean no value. If a column is defined to allow NULL, then data can be omitted from that column when a row is inserted or updated. You will be seeing lots more of NULL as you work through the lessons in this book.
Primary Keys
Every row in a table should have some column (or set of columns) that uniquely identifies it. A table containing customers might use a customer number column for this purpose, whereas a table containing orders might use the order ID. An employee list table might use an employee ID or the employee Social Security number column.
* * *
New Term: Primary key
A column (or set of columns) whose values uniquely identify every row in a table.
* * *
This column (or set of columns) that uniquely identifies each row in a table is called a primary key. The primary key is used to refer to a specific row. Without a primary key, updating or deleting specific rows in a table becomes difficult because there is no guaranteed safe way to refer to just the rows to be affected.
* * *
Tip: Always Define Primary Keys
Although primary keys are not actually required, most database designers ensure that every table they create has a primary key so future data manipulation is possible and manageable. In fact, if you omit the primary key, some database engines create one automatically for you, and the odds of it being what you’d have wanted are pretty slim. Bottom line, always define primary keys!
* * *
Any column in a table can be established as the primary key, as long as it meets the following conditions:
• No two rows can have the same primary key value.
• Every row must have a primary key value (primary key columns may not contain NULL values).
* * *
Tip: Primary Key Rules
The rules listed here are enforced by MariaDB itself.
* * *
Primary keys are usually defined on a single column within a table. But this is not required, and multiple columns may be used together as a primary key. When multiple columns are used, the rules previously listed must apply to all columns that make up the primary key, and the values of all columns together must be unique (individual columns need not have unique values).
* * *
Tip: Primary Key Best Practices
In addition to the rules that MariaDB enforces, several universally accepted best practices should also be adhered to
• Don’t update values in primary key columns.
• Don’t reuse values in primary key columns.
• Don’t use values that might change in primary key columns. (For example, when you use a name as a primary key to identify a supplier, you would have to change the primary key when the supplier merges and changes its name.)
* * *
There is another important type of key called a foreign key, but we discuss that later on in Chapter 15, “Joining Tables.”
What Is SQL?
SQL (pronounced as the letters S-Q-L or as sequel) is an abbreviation for Structured Query Language. SQL is a language designed specifically for communicating with databases.
Unlike other languages (spoken languages such as English, or programming languages such as Java or Visual Basic), SQL is made up of very few words. This is deliberate. SQL is designed to do one thing and do it well—provide a simple and efficient way to read and write data from a database.
What are the advantages of SQL?
• SQL is not a proprietary