DATATYPES INT BOOLEAN CONSTRAINTS –
PRIMARY KEY PART 12
What are different datatypes of MYSQL ?
What are different constraints on tables ?
MANY EXAMPLES ON SELECT QUERIES ARE GIVEN WITH SAMPLE TABLES..
MySQL, a widely used relational database management system, offers a variety of data types and constraints that are fundamental to storing and managing data effectively. Here’s an overview of its key data types and constraints:
- Integer Types: MySQL supports various integer types, such as
BIGINT. These are used for storing whole numbers, both positive and negative.
- Floating-Point and Decimal Types: For decimal numbers, MySQL provides
DOUBLEare approximate types, while
DECIMALis used for exact precision arithmetic, often in financial calculations.
- Date and Time Types: MySQL includes several data types for date and time, such as
YEAR. These types allow for the storage of dates, times, or both.
- String Types: For textual data, MySQL offers types like
CHAR(fixed length) and
VARCHAR(variable length). There are also
BLOBfor storing large amounts of text or binary data, respectively.
- ENUM and SET Types: The
ENUMtype is used for creating a field that can hold one of a specified set of values, while
SETallows for the selection of multiple values from a predefined list.
- Primary Key Constraint: This constraint uniquely identifies each record in a database table. It must contain unique values, and cannot contain
- Foreign Key Constraint: Used to link two tables together. It is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
- Unique Constraint: Ensures that all values in a column are different. This provides a way to enforce the uniqueness of a data column other than the primary key.
- NOT NULL Constraint: By default, a column can hold
NOT NULLconstraint enforces a column to NOT accept
- Default Constraint: This constraint provides a default value for a column when none is specified. If no default value is set for a column, it defaults to
- Check Constraint: The
CHECKconstraint ensures that all values in a column satisfy a specific condition. It’s used to limit the type of data that can go into a table.
- Indexing: While not a data type or constraint, indexing is essential in MySQL for speeding up the retrieval of records from a database. An index in a database is similar to an index in the back of a book.
Understanding these data types and constraints is crucial for anyone working with MySQL, as they are fundamental to designing efficient and robust database schemas.