| Author |
Topic |
|
sowmyav
Starting Member
34 Posts |
 Posted - 2009-01-30 : 01:49:22
|
| Hi, I would like to modify a table in sql server 2005.I can do the same by adding column and the needed information like name,datatype, null.But I have a doubt, I want to add a field like "Anal_flag" in the already existing table.And the option for the "Anal_flag" should be either "yes" or "no".which can be mentioned in the the constraint definition (for example, "price >= 0 and price<=50") in the Expression textbox, after selecting from the Table Designer menu.but I am not able to add it in the exp text box...So Could you please let me know how to do the same. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-30 : 01:55:23
|
| are you asking for creation of check constarint or is your attempt to make column computed? |
 |
|
|
sowmyav
Starting Member
34 Posts |
Posted - 2009-01-30 : 02:06:31
|
| It is for the creation of check const after adding the columns in the table. |
|
|
|
sowmyav
Starting Member
34 Posts |
Posted - 2009-01-30 : 02:13:54
|
| In the check constraint Expression field ,I want to add two column known as PRDCTN and ANAL_FLG.And then,we need to give the check const condition asPRDCTN_STATUS = ‘IN’ or PRDCTN_STATUS = ‘OUT' ANAL_FLG = ‘Y’ or ANAL_FLG = ‘N'but I am getting error.So could you please let me know where is the error? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-30 : 02:15:01
|
| then just use ALTER TABLE ADD CONSTRAINT CHK_Anal_flag CHECK(Anal_flag IN ('Yes','No')) |
|
|
|
bklr
Master Smack Fu Yak Hacker
1693 Posts |
Posted - 2009-01-30 : 02:16:25
|
| try like this as computed columnalter table tablename add Anal_flag as case when price >= 0 and price<=50 then 'y' else 'n' end |
|
|
|
sagitariusmzi
Posting Yak Master
113 Posts |
Posted - 2009-01-30 : 02:28:16
|
| You can add the check constraint asALTER TABLE tablename WITH CHECK ADD CHECK (([PRDCTN_STATUS]='IN' AND [PRDCTN_STATUS]='OUT')) |
|
|
|
sowmyav
Starting Member
34 Posts |
Posted - 2009-01-30 : 02:31:59
|
| Thanks.But how it can be added in the Expression textbox, as it is sql server management studio |
|
|
|
sowmyav
Starting Member
34 Posts |
Posted - 2009-01-30 : 02:34:02
|
| it is adding only the null values, and incase if i uncheck the box, it doesn't get saved and gives the error msg.So this might due to the privilege of this DB is not given to me....could any one please let me know |
|
|
|
raky
Aged Yak Warrior
767 Posts |
Posted - 2009-01-30 : 02:35:29
|
| what error msg it is giving?? |
|
|
|
sowmyav
Starting Member
34 Posts |
Posted - 2009-01-30 : 02:42:23
|
| It gives me the following err msg:'SYS_200812' table- Unable to modify table. Cannot insert the value NULL into column 'PROD_STATUS', table 'AS.dbo.Tmp_SYS_200812'; column does not allow nulls. INSERT fails.The statement has been terminated. |
|
|
|
sowmyav
Starting Member
34 Posts |
Posted - 2009-01-30 : 02:44:53
|
| And also the foll msg saying that:whether the below mes is due to the privelge or any restriction for me add the col in the DB???'SYS_200812' table- Unable to modify table. ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'PROD_STATUS' cannot be added to non-empty table 'SYS_200812' because it does not satisfy these conditions. |
|
|
|
sowmyav
Starting Member
34 Posts |
Posted - 2009-01-30 : 04:43:40
|
| The below constraint is working fine,([price]>=(0) AND [price]<=(50))for the field price and datatype (numeric ).The condition is price should vary between 0 and 50 only.similarly I want to cheque the constraint for the below field whether the prod_status should be either IN or OUT.([prod_statuts]=("IN") OR [prod_status]=("OUT")).IS there any syntax prob with the above one.please let me know where i need to change as i m getting the error as error validatiing const.thanks,Sowmya |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-30 : 09:35:21
|
quote:
Originally posted by sowmyav
And also the foll msg saying that:whether the below mes is due to the privelge or any restriction for me add the col in the DB???'SYS_200812' table- Unable to modify table. ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column 'PROD_STATUS' cannot be added to non-empty table 'SYS_200812' because it does not satisfy these conditions.
you need to give column a default value using DEFAULT clause if you're making it NOT NULL through ALTER statement |
|
|
|
|
Creating/modifying a table in sql server 2005