Constraint with Calculation - 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

General SQL Server Forums

New to SQL Server Programming

Constraint with Calculation

Author

Topic

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-22 : 11:45:38

Hello there

I want to create a column with numeric data type that = another column within the table then times it by 1.25

for example.

alter table [table_name] add [New_Column] numeric (28, 4) NULL check ( [New_Column] = [Old_column]) * 1.25

but i receive a message that you cannot reference another column within the table.

Can someone tell me where i am going wrong please.

Kind Regards

Rob

MCTS / MCITP certified

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-23 : 03:38:41

does anyone have any ideas on this please

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-23 : 03:41:45

use this instead:

Create a function to implement the logic and use that instead

ALTER TABLE table_name
ADD CONSTRAINT chkValueEquals CHECK (new_col = 1.25 * dbo.GetOld_column_Value());

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-23 : 03:45:51

sorry i dont understand, could you give me an example please

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-23 : 03:53:38

Create a table constraint :

ex-

CREATE TABLE Customers_2
(
CustomerID int,
Phone varchar(24),
Fax varchar(24),
CONSTRAINT CK_PhoneOrFax
CHECK(Fax IS NOT NULL OR PHONE IS NOT NULL)
new_col = old_col * 1.25 -- your requirement
)

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-23 : 03:56:49

would you say creating a function is the more efficient approach

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-23 : 03:57:14

for existing table

CREATE TABLE table_name
(
old_col_1 int,
old_col_2 datetime
)

ALTER TABLE [table_name] add [New_Column] numeric (28, 4) NULL
ALTER TABLE table_name
ADD CONSTRAINT CK_old_new_col_match CHECK(New_Column = old_col_1 * 1.25)

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-23 : 03:59:27

quote:
Originally posted by masterdineen

would you say creating a function is the more efficient approach

Surely not more effecient.Since, you can't reference an existing column in check constraint, it is the way around.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-23 : 04:03:43

quote:
Originally posted by masterdineen

would you say creating a function is the more efficient approach

Surely not more effecient.Since, you can't reference an existing column in check constraint, it is the way around.

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-23 : 04:04:59

another example:
ALTER TABLE [dbo].[aa] WITH CHECK ADD CONSTRAINT [CK_aa] CHECK ((([column_1]+[column_2])=(10)))
GO

ALTER TABLE [dbo].[aa] CHECK CONSTRAINT [CK_aa]

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-23 : 04:22:37

Thank you for you help so early in the morning rohitvishwakarma.

I am very greatful

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-23 : 04:23:53

i have now created the constraint below

ADD CONSTRAINT CK_Euro_Conversion check (EURO_GPRICE = GPRICE * 1.25 )

rohitvishwakarma
Posting Yak Master

232 Posts

Posted - 2010-09-23 : 04:37:07

quote:
Originally posted by masterdineen

Thank you for you help so early in the morning rohitvishwakarma.

I am very greatful

Always Welcome

. I am in India and its a bright and sunny afternoon here

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2010-09-23 : 06:12:10

The constraint i am using ADD CONSTRAINT CK_Euro_Conversion check (EURO_GPRICE = GPRICE * 1.25 )

is not working. the new column Euro_Gprice is not updating when a new entry is going in, Euro_Gprice is null.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources