Data Types in SQL

Numeric Data Types

INT: Whole Numbers

FLOAT (M,D): Decimal numbers (approximate)

• example: 1.78 = FLOAT (3,2)
• 1.625 will round to a 1.63

DECIMAL (M,D): Decimal numbers (precise)

• best for things like $
• DECIMAL (4,2) = $10.32

Non-Numeric Data Types

CHAR(N): Fixed length character

• CHAR(3) = any value in that column has to be this length
• example: USD

VARCHAR(N): Varying length character

• Any string up to the N

ENUM('M','F'): Value from a defined list

• This is male or female
• good for gender or yes or no

BOOLEAN: True or False values

• examples In stock - set to true or false

Date and time types

DATE: Date (YYYY-MM-DD)

DATETIME: Date and tiem (YYYY-MM-DD HH-MM-SS)

TIME: Time (HHH-MM-SS)

• Not just used for times of day
• Can be used for the time between two events

YEAR: Year (YYYY)

Primary Key

• Set of columns which uniquely identifies a record within a table
• must be unique
• cannot be null
• table can only have one primary key

Foreign Key

• Used to link two tables together
• column whose values match the values of another tables primary key column
• table with the primary key is called the reference, or parent, table and the table with the foreign key is called the child table
• A table can have multiple foreign keys