SQL Table Structure

Tables are an organized collection of data is stored and represented by a form of columns and rows. Fields are what represents columns in your table. When writing your CREATE sql statement how many columns and what the data type are are specified on the business needs of your application

Shown below a sql statement on creating a table.

CREATE tbl_name (
    id INT NOT NULL AUTO INCREMENT,
    column_Name VARCHAR(50)
    ...
)

Columns

When you create a table, you have the flexibility on how your table is structured. This will be come a representation on how your data is organized.

A column type is a data type that is how it its going to be stored in each data cell. You can have Numeric, Date time and String data types for your columns

CONSTRAINTS

Constraints are used for ensuring the logical, and physical data is stored consistent in your tables. You can specify what type of rules of how records are entered, and updated. This helps with data integrity of your Database.

NOT NULL

This constraint is used to specify a particular column on your table cannot allow NULL values. This rule is used to enforce your application that a particular column needs/require a certain value.
This example shown below "column_name" has a data type of INTEGER, and it cannot be null when you insert or update a record in your table

column_name INT NOT NULL

DEFAULT

This is used to set a default value on a column if there is no data specified when persisting data to a particular column.

This example shown below the column "column_name" has a data type of VARCHAR, with a default value of "Test default text" when you enter a null value when inserting a new record in a table

column_name VARCHAR "Test default text"

INDEX

Indexes are used to retrieve data from a database more quickly. Its mostly used to speed up process when making a query off of a particular index column

CREATE_INDEX idx_name 
ON tbl_name (column1, column2, ...)

PRIMARY KEY

This constraint is used for specify the main indicator of a table for the record, it has a unique value, and most of the time it is usually an integer value and it auto increments when entering new data in a particular table.

Check and make sure what database are you using. Each database has its own syntax on how a primary key is created when generating your tables.

CREATE tbl_name (
    id INT NOT NULL AUTO INCREMENT,
    column_Name VARCHAR(50)
    ... ,
    PRIMARY KEY (id)
)

FOREIGN KEY

A foreign key is used to reference a primary key from a different table. It is usually associated with defining relationships between other tables in your database. It is also used to bring a constraint to prevent from disrupting relationships between different entities in your database

CREATE tbl_name (
    id INT NOT NULL AUTO INCREMENT,
    column_Name VARCHAR(50)
    tble_name_2_id: INT
    ... ,
    PRIMARY KEY (id),
    FOREIGN KEY (tble_name_2_id) REFERENCES tble_name_2(id)
)

UNIQUE

This is to specify a column is only allowed to have unique values from one row to another. This will prevent any duplicated content if you try to enter the same data from a particular column

CREATE tbl_name (
    id INT NOT NULL AUTO INCREMENT,
    column_Name VARCHAR(50) UNIQUE
    ... ,
)

CHECK

This is used to put a condition or limits value ranges on a column you specify when creating your table.

CREATE tbl_name (
    id INT NOT NULL AUTO INCREMENT,
    column_name INT
    ... ,
    CHECK (column_name > 18)
)