Get Help:Ask a Question in our Forums|Report a Bug|More Help Resources
Last post Apr 06, 2012 04:19 PM by tdmca
Apr 06, 2012 10:51 AM|LINK
I have a table with two fields (dateofexam and regno) are set to primary key.. Now I wanna add new column, say academic_year and wanna include this column to existing primary key constraint.. My table has currently rows.. How to do this? Should I remove
all rows and continue?
Thanks in advance..
Apr 06, 2012 11:02 AM|LINK
No need remove data from the table
Just, first add academic_year column and fill it will data in each row then you would be able to add academic_year to the existing primary key(dateofexam and regno). But make sure data in the 3 columns should be distinct in each row.
Reason: Any column used in the primary key could not have null values.
Apr 06, 2012 02:44 PM|LINK
As you said, I did the following.. Please correct me if im not
I added new column academic_year and made 3 columns distinct.. Then i dropped the constraint and created again to include the newly added column.. But it is showing that
the primary key cannot be created for Nullable columns.. So I tried to create academic_year column as NOT NULL, then another error shows that
"ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT......"
How can I overdcome this problem?
Apr 06, 2012 04:13 PM|LINK
Add the column allowing NULLs.
Then update the table to fill the column with values.
Then ALTER the table changing the column to NOT NULL
Then add the column to the Primary Key.
Apr 06, 2012 04:19 PM|LINK
yes that error is right because when you add not null column then what value you new column contain corresponding to old rows
you have to provide default value
so use this
alter table tblname add academic_year varchar(20) not null default '2011'