The goal of database design is to produce a database which is straightforward and efficient to search. This is done by splitting data into a set of tables, with lookups between those tables used to build the desired output results.
Efficient database design is normally discussed with reference to "normal forms", the goal being to reach the highest order normal form. In practice, pragmatism is applied which means it may be sensible to hold back a little on this.
The Primary Key may be a single column of 'synthetic' numbers (i.e. they don't have any other purpose), or it may be a pre-existing column in the table, or it may be a combination of columns which case it is called a Composite Key. Primary and Composite Keys are indicated using the PRIMARY KEY keyword :
PRIMARY KEY (sid)
For a composite key, this form is used:
PRIMARY KEY (column_1,column_2,column_3)
In a normalised relation a non-key field must provide a fact about the key, the whole key and nothing but the key.Relationships between tables in a database are indicated like this:
PRIMARY KEY (order_id),
FOREIGN KEY (customer_sid) REFERENCES customer(sid)
(Example borrowed from here). PRIMARY KEY and FOREIGN KEY are examples of 'constraints', primary keys must be unique and a foreign key value cannot be used in a table if it does not exist as a primary key in the referenced table. The CONSTRAINT keyword is used to give a name to a constraint (a constraint being one of NOT NULL, UNIQUE, CHECK, Primary Key, Foreign Key). CHECK is not supported in MySQL.
Keywords: PRIMARY KEY, CONSTRAINT, FOREIGN KEY, REFERENCES, CONSTRAINT