Mysql 14: Primary key
Written by:
, 2009-04-16 21:22:35
Sometimes you want a field in your table that contains unique numbers. This might be useful for ID rows in a customer account table since you only want one of each customer. Primary key offers the solution to this. By setting a field(column) as primary key you make sure that whatever value a record has, the primary key field has to be unique in that table, e.g. an error will be thrown if the same number is entered again.
Syntax:
CREATE TABLE table_name
(
column_1 datatype,
column_2 datatype,
PRIMARY KEY (column_1)
)
Example:
CREATE TABLE members
(
m_ID int NOT NULL,
username varchar(255) NOT NULL,
password varchar(255) NOT NULL,
email varchar(255),
PRIMARY KEY (m_ID)
)
This will create a table suited for member registration on a server. The ID has to be unique for every member. The problem however with this table is that you have to manually check so that each entered record has it's own unique number. Thank the devil there is a quick solution to this:
AUTO_INCREMENT(MySQL),
IDENTITY(SQL Server),
AUTOINCREMENT(Access) or
The-Incredibly-complicated-Oracle-way(which I won't cover).
Since I work with MySql I will use AUTO_INCREMENT, but if you work with SQL Server, just replace it with IDENTITY and it will work just fine.
CREATE TABLE members
(
m_ID int NOT NULL auto_increment,
username varchar(255) NOT NULL,
password varchar(255) NOT NULL,
email varchar(255),
PRIMARY KEY (m_ID)
)
Now you don't have to enter anything in the m_ID column, it will auto increment a value for every record.
WARNING: Do not attempt to change the order of the incremented values in a database. If you throw records away, the incrementation will(most likely) continue from the last entered record, not the last number in the current table. Altering this can cause problems.