Entry
Database: Relational: Key: Primary: Unique: How to create a unique primary key? [auto_increment]
Mar 21st, 2005 11:52
Knud van Eeden,
----------------------------------------------------------------------
--- Knud van Eeden --- 18 March 2005 - 02:28 pm ----------------------
Database: Relational: Key: Primary: Unique: Create: How to: How to
create a unique primary key?
---
Method: Create a table with column with an integer value, auto
increment and 'PRIMARY KEY'
Then if you insert values in that table,
you do not insert values in this column with integer.
Then the database will automatically generate the unique keys for you.
---
e.g.
--- cut here: begin --------------------------------------------------
-----------------------------------------------
CREATE DATABASE
databaseMyTest;
-----------------------------------------------
USE
databaseMyTest;
-----------------------------------------------
DROP TABLE
tableTest1;
-----------------------------------------------
CREATE TABLE
tableTest1
(
columnPrimaryKey1 INT AUTO_INCREMENT PRIMARY KEY,
nameS VARCHAR( 50 )
)
;
-----------------------------------------------
INSERT INTO
tableTest1
(
nameS
)
VALUES
(
"John"
)
;
-----------------------------------------------
INSERT INTO
tableTest1
(
nameS
)
VALUES
(
"Vanessa"
)
;
-----------------------------------------------
INSERT INTO
tableTest1
(
nameS
)
VALUES
(
"Knud"
)
;
-----------------------------------------------
SELECT
*
FROM
tableTest1
;
-----------------------------------------------
--- cut here: end ----------------------------------------------------
When you run this, this shows output similar to the following:
--- cut here: begin --------------------------------------------------
mysql> source c:\temp\ddd.sql
ERROR 1007 (HY000): Can't create database 'databasemytest'; database
exists
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.11 sec)
Query OK, 0 rows affected (0.15 sec)
Query OK, 1 row affected (0.05 sec)
Query OK, 1 row affected (0.06 sec)
Query OK, 1 row affected (0.06 sec)
+-------------------+---------+
| columnPrimaryKey1 | nameS |
+-------------------+---------+
| 1 | John |
| 2 | Vanessa |
| 3 | Knud |
+-------------------+---------+
3 rows in set (0.00 sec)
--- cut here: end ----------------------------------------------------
---
---
You can further combine (concatenate) 2 or more columns in order
to create a unique primary key
e.g.
--- cut here: begin --------------------------------------------------
SELECT
nameS,
columnPrimaryKey1
FROM
tableTest1
;
--- cut here: end ----------------------------------------------------
---
---
You can then concatenate this via middleware (in your PHP, Perl, ...
program, by using string concatenation, or by using SQL (this should be
the preferred method, as you have your data centrally and independent
of your middleware stored (so using the principle 'store your data
once, access it anywhere'). Create e.g. an extra table with some
constants which you can concatenate to an integer unique primary key,
via e.g. a SQL join query)
---
---
Giving e.g. as output, by concatenating the 2 fields after each other:
John1
Vanessa2
Knud3
---
---
Or if you have international customers, in different countries, you
might concatenate an abbreviation for the country of the customer, in
order to get a unique and visually easy to recognize unique id:
concatenate 'US' and an integer '234233', giving
US234233 for the USA
concatenate 'DE' and an integer '234234', giving
DE234234 for Germany
concatenate 'GB' and an integer '234235', giving
GB234235 for Great Britain
---
e.g.
-----------------------------------------------
CREATE DATABASE
databaseMyTest;
-----------------------------------------------
USE
databaseMyTest;
-----------------------------------------------
DROP TABLE
tableTest1;
-----------------------------------------------
CREATE TABLE
tableTest1
(
columnPrimaryKey1 INT( 4 ) AUTO_INCREMENT PRIMARY KEY ,
nameS VARCHAR( 50 )
)
;
-----------------------------------------------
-- this gives a default value to the primary key
INSERT INTO
tableTest1
(
columnPrimaryKey1,
nameS
)
VALUES
(
234233,
"US"
)
;
-----------------------------------------------
INSERT INTO
tableTest1
(
nameS
)
VALUES
(
"DE"
)
;
-----------------------------------------------
INSERT INTO
tableTest1
(
nameS
)
VALUES
(
"GB"
)
;
-----------------------------------------------
SELECT
nameS,
columnPrimaryKey1
FROM
tableTest1
;
-----------------------------------------------
When you run this, this shows output similar to the following:
--- cut here: begin --------------------------------------------------
mysql> source c:\temp\ddd.sql
ERROR 1007 (HY000): Can't create database 'databasemytest'; database
exists
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.21 sec)
Query OK, 0 rows affected (0.25 sec)
Query OK, 1 row affected (0.07 sec)
Query OK, 1 row affected (0.06 sec)
Query OK, 1 row affected (0.05 sec)
+-------+-------------------+
| nameS | columnPrimaryKey1 |
+-------+-------------------+
| US | 234233 |
| DE | 234234 |
| GB | 234235 |
+-------+-------------------+
3 rows in set (0.00 sec)
--- cut here: end ----------------------------------------------------
---
---
Internet: see also:
---
Database: Language: SQL: Overview: Can you give an overview of links
about SQL?
http://www.faqts.com/knowledge_base/view.phtml/aid/32811/fid/54
----------------------------------------------------------------------
