ALTER TABLE - ADD UNIQUE in PHP

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
knallbernd
Forum Commoner
Posts: 30
Joined: Mon Apr 23, 2007 4:39 am

ALTER TABLE - ADD UNIQUE in PHP

Post 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!
mentor
Forum Contributor
Posts: 100
Joined: Sun Mar 11, 2007 11:10 am
Location: Pakistan

Post 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?
knallbernd
Forum Commoner
Posts: 30
Joined: Mon Apr 23, 2007 4:39 am

Post 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.
mentor
Forum Contributor
Posts: 100
Joined: Sun Mar 11, 2007 11:10 am
Location: Pakistan

Post 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.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
knallbernd
Forum Commoner
Posts: 30
Joined: Mon Apr 23, 2007 4:39 am

Post by knallbernd »

Thanks. I will try this and let you know if it worked out.
Post Reply