Informatics Practices · Ch 7 — Introduction to Structured Query Language (SQL)
Data Types and Constraints in MySQL
Data Types and Constraints in MySQL
This section sets up the two ideas that every table definition rests on: data types and constraints. Both follow directly from how a relational database is organised.
A database consists of one or more relations, and each relation — that is, each table — is made up of attributes, which appear as its columns. Now, a column cannot sensibly hold "anything at all": a roll-number column should hold numbers, a name column should hold text, a date-of-birth column should hold dates. This is captured by giving each attribute a data type — a declaration of what kind of values that column is allowed to store.
Beyond the kind of value, we often want to impose rules on the values themselves. For that, we can also specify constraints for each attribute of a relation — restrictions such as "this column may never be empty" or "no two rows may repeat this value." Constraints are optional extras layered on top of the data type.
The division of labour between the two is worth keeping clear:
- the data type answers "what sort of value can this attribute hold?"
- a constraint answers "what additional rules must those values obey?" …
Figure 8.1 is a screenshot of the MySQL shell — specifically, a window titled "MySQL 5.7 Command Line Client - Unicode". It captures what you actually see on screen the moment MySQL is started and becomes ready for SQL statements, which is why the chapter points to it as the visual confirmation that installation and start-up have succeeded.
The window records the complete login sequence, top to bottom. It begins with an Enter password: line, where the typed password appears masked as asterisks. Once the password is accepted, the MySQL monitor prints its welcome banner, which includes the practical reminder that commands end with ; or \g. The banner also reports session details: the connection id is 13, and the server version is 5.7.23-log MySQL Community Server (GPL). Below this appears Oracle's copyright and trademark notice for the software.
Next comes a help hint telling the user how to get assistance from inside the shell: type help; or \h for help, and type \c to clear the current input statement. Finally, on the last line of the window, sits the prompt itself:
mysql>
``` …