Page 1 of 1

ALTER TABLE - ADD UNIQUE in PHP

Posted: Fri Jul 13, 2007 12:42 pm
by knallbernd
Hi,

I want to add an UNIQUE-Constraint to my new column.

Code: Select all

$sql="ALTER TABLE table ADD UNIQUE $y varchar(150) NOT NULL ";
mysql_query($sql);
But that doesn't work. What's wrong with this?
The second question is:
How can I manage to say that empty fields (like #1062 - Duplicate entry '' for key 5 ) are excepted from UNIQUE?

Thank you!

Posted: Fri Jul 13, 2007 2:18 pm
by mentor
Make sure mysql user has the necessary priviliges to perform this action. And use mysql_error() to know the exact error.

Code: Select all

mysql_query($sql) or die(mysql_error());

Are you sure that all the values in the column are unique?

Posted: Sat Jul 14, 2007 2:08 am
by knallbernd
That's the problem, because there are empty fields in the column which are not UNIQUE, so I'd like to make an exception to exclude
those empty fields while creating a new UNIQUE column.

Posted: Sat Jul 14, 2007 7:31 am
by mentor
knallbernd wrote:That's the problem, because there are empty fields in the column which are not UNIQUE, so I'd like to make an exception to exclude
those empty fields while creating a new UNIQUE column.
It is not possible to exclude some rows from UNIQUE index. You have to delete conflicting rows.

Posted: Sat Jul 14, 2007 7:43 am
by feyd
You can also combine several fields into the key, however.

Alternately, a DISTINCT selection query may work just as well, but not require a unique key.

Posted: Sat Jul 14, 2007 12:37 pm
by knallbernd
Thanks. I will try this and let you know if it worked out.