mysql in General :: Databases

Databases
InterBase (29)
MS-SQL (5)
mysql (37)
Oracle (1)

General information about mysql - how to get around the differences between version 3.33 an 4, how to do stuff that you think you need a nested query for etc.

Articles:

This list is sorted by recent document popularity (not total page views).
New documents will first appear at the bottom.

Featured Article

Create UNIQUE Index yields a not unique index in mysql - why?

Question:

I created an index on a mysql table named 'referrers' using CREATE UNIQUE INDEX and I get no error message but when I view the table definition afterwards with DESC referrers then the key on is described as MUL (multiple, not unique).
What is going on?

Answer:

You probably forgot to specify the field in question as NOT NULL.

See the example below. First attempt to add a unique index on field ref_url yields a 'MUL' key field.

Then the field gets dropped, redefined as NOT NULL and then the index definition is successful.

mysql> create unique index ref_idx1 on referrers (ref_url);
 Query OK, 0 rows affected (0.00 sec)
 Records: 0  Duplicates: 0  Warnings: 0
 
 mysql> desc referrers;
 +---------+--------------+------+-----+---------+----------------+
 | Field   | Type         | Null | Key | Default | Extra          |
 +---------+--------------+------+-----+---------+----------------+
 | ref_id  | int(11)      |      | PRI | NULL    | auto_increment |
 | ref_url | varchar(250) | YES  | MUL |         |                |
 +---------+--------------+------+-----+---------+----------------+
 2 rows in set (0.00 sec)
 
 mysql> drop index ref_idx1 on referrers;
 Query OK, 0 rows affected (0.00 sec)
 Records: 0  Duplicates: 0  Warnings: 0
 
 mysql> alter table referrers drop ref_url;
 Query OK, 0 rows affected (0.00 sec)
 Records: 0  Duplicates: 0  Warnings: 0
 
 mysql> alter table referrers add ref_url varchar(250) not null default '';
 Query OK, 0 rows affected (0.00 sec)
 Records: 0  Duplicates: 0  Warnings: 0
 
 mysql> create unique index ref_idx1 on referrers (ref_url);
 Query OK, 0 rows affected (0.00 sec)
 Records: 0  Duplicates: 0  Warnings: 0
 
 mysql> desc referrers;
 +---------+--------------+------+-----+---------+----------------+
 | Field   | Type         | Null | Key | Default | Extra          |
 +---------+--------------+------+-----+---------+----------------+
 | ref_id  | int(11)      |      | PRI | NULL    | auto_increment |
 | ref_url | varchar(250) |      | UNI |         |                |
 +---------+--------------+------+-----+---------+----------------+
 2 rows in set (0.00 sec)
 
 mysql>

Generated 20:00:27 on Aug 15, 2018

Most recent comments

2018-08-15 15:30:41:
by Keat Cheng
in Finances
on Well-respected bank offering Payday loans:

We are Ireland based major/Direct providers of Fresh Cut BG, SBLC, POF, MTN, Bonds and CDs and this financial instruments are specifically for lease and sale.We are one of the leading Financial inst ... read more

2018-08-15 02:26:15:
by anonymous
in Male Dating Scammers
on Dating scammer Damian Hughes Moretti :
Whoever that wrote this up and posted a pic is impersonating me and you better take this photo down off this page before i take legal actions.

2018-08-14 23:52:16:
by anonymous
in Male Dating Scammers
on Dating scammer William E. Fredrick:
this personharry Beeston scam me said he was stuck in Belgium was from miamia florida and had a daughter.His wife died 8 years ago.

2018-08-14 17:46:21:
by buzzshadow333
in about DelphiFAQ
on DelphiFAQ Site wide search function:
How do I search all scammers on this site?

2018-08-14 03:43:33:
by [hidden]
in Dating Scams
on Dating scammer Elena Zlygosteva:
Долги нужнo отдaвать

2018-08-13 21:03:40:
by [hidden]
in Dating Scams
on Dating scammer Sandra Da_Rocha from Accra/Ghana:
At least for today the girl with a hundred names. Profile von realbaby000 - I am young sexy lady, faithful and respectful slave and baby , I have lost my master and now homeless and on the street , I ... read more

2018-08-13 16:34:47:
by anonymous
in Web publishing
on Getting started with CSS:

Dear Sir/Ma,

We are direct provider for BG/SBLC specifically for lease, at leasing price of (3.0 + 0.5 + X)% of face value, Issuance by HSBC London/Hong Kong or any other AA rated Bank in Eur ... read more

2018-08-12 23:00:31:
by anonymous
in Male Dating Scammers
on Dating scammer Sergeant Dylan Daniel:
(HE IS ALSO AS TASKER BLISS: this is his msg)

Hello Dear,

I am happy to hear from you!

I want to tell you more about myself. My name is Tasker Bliss, I was born on 1975, am ... read more

2018-08-12 12:48:06:
by [hidden]
in Dating Scams
on Is this Russian girl scamming me? How to recognize scammers:
Unfortunately, the U.S. Embassy cannot verify the identity of this individual, because Russia has strict laws protecting the distribution of information about Russian citizens. The embassy has informa ... read more

2018-08-12 07:25:19:
by DOC
in Dating Scams
on Dating scammer Nancy Milman from Accra, Ghana:

Natalie Sparks

http://www.delphifa..&x=10&y=7


	Search: