Keys and Auto-Incrementing Values
PRIMARY keys (id)
Used to uniquely define each row in a table. It can’t be duplicated, can’t be null.
UNIQUE keys (email_address, ssn)
Is similar to Primary keys but enforces uniqueness, can’t be duplicated but can be null.
FOREIGN keys (genre_id)
Special keys that describe the relationship between data in two in two tables. Also known as reference keys because they reference data from another table. These can be duplicated and can be null.
AUTO-INCREMENT increments the integer by 1 every time you insert a new row.
Let’s create a genres table with a primary key as id and a unique key for name.
PRIMARY KEY is always not null. But you can specify it for clarity.
Mentioning ‘KEY’ is optional. You can use
REFERNCE instead of PRIMARY KEY, UNIQUE KEY, and REFERENCE KEY.
UNIQUE KEY, and
UNIQUE INDEX all mean the same thing.
The order of things in the statement is as follows:
Let’s add values to the genres tables
Since the id will be auto-incremented, we don’t need to specify it in our name. And because we set our genres name column to be unique, we won’t be able to add ‘Sci Fi’ again.
Linking data between tables
In the statement below, we want our genre_id to be a foreign key, meaning it references a key from another table.
CONSTRAINT means that only particular values can be entered in this column. You can add a constraint as a