473,372 Members | 997 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

ALTER TABLE ADD UNIQUE .....


Hi ,

Looks like ADD UNIQUE( some_fuc( some_feild) ) is not supported with add constraint.
the only way is to add the constriant is using UNIQUE INDEX .

Is it a bug or intended behaviour?

Regds
Mallah.
in 7.3.4
~~~~~~~~~~~~

tradein_clients=# ALTER TABLE general.email_master ADD CONSTRAINT
email_master_uniq_lower_btrim_email UNIQUE( lower(email) );

ERROR: parser: parse error at or near "(" at character 99
tradein_clients=#

tradein_clients=#
tradein_clients=# CREATE UNIQUE INDEX email_master_uniq_lower_btrim_email on general.email_master( lower(email) );
ERROR: Cannot create unique index. Table contains non-unique values

Well the SQL has failed but it was parsed successfully.

tradein_clients=#

SAME PROBLEM IN PGSQL 7.4 RC2
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

template1=# alter table t_a add constraint "a" UNIQUE (email);
NOTICE: ALTER TABLE / ADD UNIQUE will create implicit index "a" for table "t_a"
ALTER TABLE
template1=# alter table t_a add constraint "b" UNIQUE (lower(email));
ERROR: syntax error at or near "(" at character 54
template1=#


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #1
1 9282
Rajesh Kumar Mallah <ma****@trade-india.com> writes:
Looks like ADD UNIQUE( some_fuc( some_feild) ) is not supported with add constraint.
the only way is to add the constriant is using UNIQUE INDEX .
Is it a bug or intended behaviour?


It's intended. The syntax and behavior of unique constraints is defined
by the standard, and it doesn't include anything about functional
indexes.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: me | last post by:
I would like to add an Identity to an existing column in a table using a stored procedure then add records to the table and then remove the identity after the records have been added or something...
6
by: Brian Basquille | last post by:
Just started learning SQL recently. But one thing i'm still not clear on is about altering relationships between tables after they've been created. Instead of creating a foreign key when the...
0
by: anzenews | last post by:
Hello! I have a weird problem... The application I use issues this SQL from time to time: alter table t modify id int(6) unique not null auto_increment; The problem is that this SQL adds a...
3
by: Prince Kumar | last post by:
Is there any way I can define an Unique constraint or unique index which allows more than one null values for the same column combination in DB2? ie, If my index is defined on (col3, col4) where...
10
by: BuddhaBuddy | last post by:
Platform is DB2/NT 7.2.9 The table was created like this: CREATE TABLE MYTEST ( MYTESTOID bigint not null primary key, FK_OTHEROID bigint not null references other, FK_ANOTHEROID bigint not...
3
by: Rajesh Kumar Mallah | last post by:
Hi, Looks like alter table does not tells about the indexes it dropped PG version: 7.4.3 Regds mallah.
7
by: Serge Rielau | last post by:
Hi all, Following Ian's passionate postings on problems with ALTOBJ and the alter table wizard in the control center I'll try to explain how to use ALTOBJ with this thread. I'm not going to get...
1
by: James | last post by:
To alter 1 column to a unique key, ALTER TABLE user MODIFY COLUMN id INT NOT NULL UNIQUE; But how to set combination of 2 columns as a unique key? Individual keys are not unique, but...
1
by: prosad | last post by:
hi, have a mysql query that orders entry by complaint category. this query is then used with mysql_fetch_array to generate table of records ordered by complaint and other parameters. i can alter...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.