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.