How to create table with Unique Identifier field in MySQL?

Question

I am working on PHP and database MySQL. I have two tables in SQL Server 2005 and I want to move them into MySQL.

These two tables contain fields with Unique Identifier and MySQL doesn't have a Unique Identifier data type. So I am not able to convert it into MySQL.

Please help me to solve this problem.

Naissant

Answer 1

I guess you're looking how to create a primary key? There is also auto increment, which you will probably need

Here is an example of a table creation:

CREATE TABLE `affiliation` (
 `id` bigint(20) NOT NULL AUTO_INCREMENT,
 `affiliate_id` bigint(20) DEFAULT NULL,
 `email_invited` varchar(255) DEFAULT NULL,
 `email_provider` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8

Portia

No I am looking for replacement to Unique Identifier (From SQL Server) in MySQL – Naissant

Answer 2

I'm not familiar with the specifics of Unique Identifiers in MS SQL, but you may be able to get what you want with MySQL's UUID function:

https://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_uuid

I would put this in a VARCHAR or CHAR column with a UNIQUE key.

Genotype

Answer 3

When you are altering a table to add in another colum you can use Add Unique. This will add in a new column, and make sure that it's not a duplicate of any of the already existing columns. It is written as:

alter table 
your_table 
add unique (column_name)

Rhyner

Answer 4

Mysql has "Unique" property.

For more information : http://dev.mysql.com/doc/refman/5.0/en/constraint-primary-key.html

If you use a software like MySQLWorkbench you can check an attribute as primary key and unique.

Hedger

Answer 5

Mysql has unique identifiers, you can use - unique key or make the field a primary key

Rog

But problem is the Unique Identifier is combination of Number and Character.So How can I do this? – Naissant

Answer 6

MySQL knows a Primary Key. Please read the manual. http://dev.mysql.com/doc/refman/4.1/en/constraint-primary-key.html

Sauncho

Answer 7

I think you looking for the UNIQUE Constraint.

The following SQL query creates a UNIQUE constraint on the field1 column when the T table:

MySQL:

CREATE TABLE `T` (
    field1 int NOT NULL,
    field2 int,
    UNIQUE (field)
);

To create a UNIQUE constraint on the field1 column when the table is already created, you can use:

ALTER TABLE T
ADD UNIQUE (field1);

To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, you can use:

ALTER TABLE T
ADD CONSTRAINT UC_field1_field2 UNIQUE (field1, field2);

Source

Struble