Unique Constraint Issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Unique Constraint Issue

Author

Topic

krugg
Starting Member

12 Posts

Posted - 2005-06-05 : 19:45:23

Hi,
I have added a computed column to a table as follows:

ALTER TABLE [tblClientAddresses]
ADD ComputedHelper AS CASE isDefault WHEN 0 THEN -AddressID ELSE isDefault END
go

Yet when I try to add a constraint on it with another column as follows:

ALTER TABLE [tblClientAddresses]
ADD CONSTRAINT AK_Only_One_Default_Address
UNIQUE (ClientID, ComputedHelper)
go

I get the following error:

Column name 'ComputedHelper' does not exist in the target table.

N.B ClientID is an int.

Does anyone know why it doesn't recognise this new field and thus allow me to the constraint? It's also worth noting that this worked at home... but not on the work db server.

nr
SQLTeam MVY

12543 Posts

Posted - 2005-06-05 : 19:54:58

Check the tabe to see if the add worked.
Check spellings and case and owners.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

krugg
Starting Member

12 Posts

Posted - 2005-06-05 : 20:57:44

mmmm... checked that column is initially adding... spelling is ok and I am part of the db_owner role.

Any other suggestions?

Thanks.

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-06-05 : 21:05:17

Are the versions the same? Constraints on computed columns require various SET commands to be in place...Check BOL

DavidM

A front-end is something that tries to violate a back-end.

krugg
Starting Member

12 Posts

Posted - 2005-06-05 : 21:20:58

Nice one... it seems as if this is version 7 as opposed to 2K..

krugg
Starting Member

12 Posts

Posted - 2005-06-05 : 21:35:59

Do you mean version 7 requires various SET commands to be in place? Because this is working on SQL 2K and not version 7...

Thanks again

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2005-06-05 : 23:01:35

IIRC, 7 does not support computed column constraints..

DavidM

A front-end is something that tries to violate a back-end.