MorkaLork Development

Interesting stuff I've picked up over the years...

Mysql 14: Primary key

2009-04-16 21:22:35 | 276 views | sql mysql table primary key field unique

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.


Article comments

Feel free to comment this article using a facebook profile.

I'm using facebook accounts for identification since even akismet couldn't handle all the spam I receive every day.