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.

Solution 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

Solution 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.

Solution 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)

Solution 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.

Solution 5

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

View more solutions

Comments

[Sachin D]

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

[Sachin D]

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