34,333
edits
Changes
→Understanding NULL and NOT NULL Values
== Understanding NULL and NOT NULL Values ==
When a column is specified to be NULL then a row can be added to a database when there is no value assigned to that column. Conversely, if a column is defined as NOT NULL then it must have a value assigned to it before the row can be added to table.
== Primary Keys ==
As covered in [[Database Basics]] a primary key is a column used to identify individual records in a table. The value of a primary key column must be unique within the context of the table in which it exists, or if multiple columns are combined to constitute a primary key, the combination of key values must be unique to each row.
The primary key is defined using the ''PRIMARY KEY'' statement during table creation. If multiple columns are being used they are comma separated:
<pre>
PRIMARY KEY (column_name, column_name ... )
</pre>
In the following example, a table is created using two columns as the primary key:
<pre>
CREATE TABLE product
(
prod_code INT NOT NULL AUTO_INCREMENT,
prod_name char(30) NOT NULL,
prod_desc char(60) NULL,
PRIMARY KEY (prod_code, prod_name) ) ENGINE=InnoDB;
</pre>
== AUTO_INCREMENT ==
AUTO_INCREMENT is one of the simplest, yet most useful column definitions in the SQL language. Essentially, when a column is defined using AUTO_INCREMENT the value of the column is increased automatically each time a new row is added to a table. This is especially useful when using a column as a primary key. By using AUTO_INCREMENT it is not necessary to write SQL statements to calculate a new unique id for each row. This is all handled by the MySQL server when the row is added.
There are two rules that must be obeyed when using AUTO_INCREMENT. Firstly, only one column per table may be assigned AUTO_INCREMENT status. Secondly, the AUTO_INCREMENT column must be indexed (e.g. by declaring it as the primary key).
It is possible to override the AUTO_INCREMENT value of a column simply by specifying a value when executing an INSERT statement. As long as the specified value to unique the value provided will be used in the new row and subsequent increments will start at the newly inserted value.
