check contraint based on another column
in [Server Configuration]
|
Prev: I: ODBC error
Next: performance
From: OceanDeep via SQLMonster.com on 13 Apr 2010 17:53 In SQL 2008 std, I want to default some value in a column based on another column value within the same table For example, if column A has a value of 0, then column B can only have a value 1. If column A has a value of 1, then column B can only have a value of 2. If column A has other values, column B has 3. Can I use check contraint to do that? If not, what other ways? OC -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201004/1
From: Kalen Delaney on 13 Apr 2010 21:47 Hi OC It is possible to have a column CHECK constraint that references another column in the table. However, your description sounds like you want column B to be a computed column, since its value is so deterministic. CREATE TABLE t1 (a int, b as case a when 0 then 1 when 1 then 2 else 3 end); GO INSERT INTO t1 SELECT 0; INSERT INTO t1 SELECT 1; INSERT INTO t1 SELECT 2; INSERT INTO t1 SELECT 3; INSERT INTO t1 SELECT 4; GO SELECT * FROM t1; GO -- HTH Kalen ---------------------------------------- Kalen Delaney SQL Server MVP www.SQLServerInternals.com "OceanDeep via SQLMonster.com" <u46587(a)uwe> wrote in message news:a67cdf403bfbc(a)uwe... > In SQL 2008 std, I want to default some value in a column based on another > column value within the same table For example, if column A has a value > of > 0, then column B can only have a value 1. If column A has a value of 1, > then > column B can only have a value of 2. If column A has other values, column > B > has 3. Can I use check contraint to do that? If not, what other ways? > > OC > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201004/1 >
From: OceanDeep via SQLMonster.com on 14 Apr 2010 13:09 Kalen, Thank for the reply. Actually my description is for an existing table and existing columns. We are trying to set up a contraint to match a new business rule. Plaman's solution addresses my question. Your solution actually could improve the solution one step further if the check constraint can compute the correct value into column B automatically. So, can your solution be combined with a check contraint to populate a value in an existing column? oc Kalen Delaney wrote: >Hi OC > >It is possible to have a column CHECK constraint that references another >column in the table. However, your description sounds like you want column B >to be a computed column, since its value is so deterministic. > >CREATE TABLE t1 >(a int, b as case a when 0 then 1 when 1 then 2 else 3 end); >GO >INSERT INTO t1 SELECT 0; >INSERT INTO t1 SELECT 1; >INSERT INTO t1 SELECT 2; >INSERT INTO t1 SELECT 3; >INSERT INTO t1 SELECT 4; >GO >SELECT * FROM t1; >GO > >> In SQL 2008 std, I want to default some value in a column based on another >> column value within the same table For example, if column A has a value >[quoted text clipped - 6 lines] >> >> OC -- Message posted via http://www.sqlmonster.com
From: Gert-Jan Strik on 14 Apr 2010 14:17 OC, You can do whatever you want, although it is not quite clear to me what you want. If you just want the value, and it really is a fixed formula, then you can add a computed column. Kalen showed you how to create a table that includes it. If you want to add it later, it would be something like this: ALTER TABLE t1 ADD columnb AS CASE columna WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END If you want an additional column, then you can add a check constraint as Plamen demonstrated. If you want to update the column first, then before you add the constraint, run something like this: UPDATE Foo SET columnb = CASE columna WHEN 0 THEN 1 WHEN 1 THEN 2 ELSE 3 END -- Gert-Jan "OceanDeep via SQLMonster.com" wrote: > > Kalen, > > Thank for the reply. Actually my description is for an existing table and > existing columns. We are trying to set up a contraint to match a new > business rule. Plaman's solution addresses my question. Your solution > actually could improve the solution one step further if the check constraint > can compute the correct value into column B automatically. So, can your > solution be combined with a check contraint to populate a value in an > existing column? > > oc > > Kalen Delaney wrote: > >Hi OC > > > >It is possible to have a column CHECK constraint that references another > >column in the table. However, your description sounds like you want column B > >to be a computed column, since its value is so deterministic. > > > >CREATE TABLE t1 > >(a int, b as case a when 0 then 1 when 1 then 2 else 3 end); > >GO > >INSERT INTO t1 SELECT 0; > >INSERT INTO t1 SELECT 1; > >INSERT INTO t1 SELECT 2; > >INSERT INTO t1 SELECT 3; > >INSERT INTO t1 SELECT 4; > >GO > >SELECT * FROM t1; > >GO > > > >> In SQL 2008 std, I want to default some value in a column based on another > >> column value within the same table For example, if column A has a value > >[quoted text clipped - 6 lines] > >> > >> OC > > -- > Message posted via http://www.sqlmonster.com
From: Mahmoud Amin on 15 Apr 2010 04:49
ALTER Table <table_name> WITH NOCHECK ADD CONSTRAINT CK_ColumnB CHECK (ColumnB = ColumnA+1) This is will check user input and prevent wrong values but it depends on that column does not have Null or default values. "OceanDeep via SQLMonster.com" <u46587(a)uwe> wrote in message news:a67cdf403bfbc(a)uwe... > In SQL 2008 std, I want to default some value in a column based on another > column value within the same table For example, if column A has a value > of > 0, then column B can only have a value 1. If column A has a value of 1, > then > column B can only have a value of 2. If column A has other values, column > B > has 3. Can I use check contraint to do that? If not, what other ways? > > OC > > -- > Message posted via SQLMonster.com > http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/201004/1 > |