Clever Geek Handbook

How to create a table with a unique id in MySQL

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

These two tables contain fields with Unique ID , and MySQL does not have a unique ID data type. Therefore, I cannot convert it to MySQL.

Please help me to solve this problem.

+5


source to share


7 replies


I think you are looking at how to create a primary key? There is also an auto increment that you probably need

Here's an example of creating a table:



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
+4


source


I'm not familiar with the specifics of unique identifiers in MS SQL, but you can get what you want with the MySQL function UUID

:

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



I would put this in a column VARCHAR

or CHAR

with a key UNIQUE

.

+3


source


When you modify the table to be added to another column, you can use Add Unique. This will add a new column and make sure it doesn't duplicate any of the already existing columns. It is written as:

alter table 
your_table 
add unique (column_name)
+1


source


Mysql has a "Unique" property.

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

If you are using software like MySQLWorkbench, you can check the attribute as primary key and unique.

+1


source


Mysql

has unique ids, you can use - unique key

or create field aprimary key

+1


source


MySQL knows the primary key. Read the manual. http://dev.mysql.com/doc/refman/4.1/en/constraint-primary-key.html

+1


source


I think you are looking for Constraint . UNIQUE

The following SQL - query creates UNIQUE

constraints on field1

columns for a T

table:

MySQL:

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

To create a UNIQUE

constraint on a field1

column if the table has already been created, you can use:

ALTER TABLE T
ADD UNIQUE (field1);

To name a constraint UNIQUE

and define a constraint UNIQUE

on multiple columns, you can use:

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

Source

0


source






More articles:


All Articles