Structured Query Language (SQL)
SQL, data types and constraints
Choose data types and constraints for attributes.
SQL (Structured Query Language) is the language used to work with relational databases (RDBMS) such as MySQL, Oracle and SQL Server. It is easy to learn: it is not case-sensitive (SELECT and select mean the same), and each statement ends with a semicolon (;).
SQL is declarative: you say what data you want, not how to find it; the database works that out. SQL is used to define the structure of tables, manipulate the data, declare constraints, and query the data.
Each column needs a data type:
| Type | Holds |
|---|---|
| CHAR(n) | text of fixed length n (0-255); shorter values are padded with spaces |
| VARCHAR(n) | text of variable length, up to n (0-65535); only the actual characters are stored |
| INT | whole numbers (4 bytes) |
| FLOAT | numbers with decimals |
| DATE | dates, in the format 'YYYY-MM-DD' |
Choose the type by thinking about the data. A 12-digit Aadhaar number, or a 10-digit phone number, always has the same length and is never used in arithmetic (and may start with 0), so CHAR(12) or CHAR(10) suits it better than INT.
Constraints are rules on the values a column may hold:
- NOT NULL: the column cannot be left empty.
- UNIQUE: no two rows can have the same value.
- DEFAULT: a value used when none is given.
- PRIMARY KEY: uniquely identifies each row (it is both unique and not null).
- FOREIGN KEY: refers to the primary key of another table.
Not every column needs a constraint; they are added where the data requires them.
Practise this lesson
Questions that get harder as you improve, a Tutor that helps without giving the answer away, and progress you can see. Free for every learner.
No account, no email address. Opens the real product.