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?
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.
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......"
rafemuhammed
Member
302 Points
97 Posts
Add primary key to existing table
Apr 06, 2012 10:51 AM|LINK
Hello..
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..
rafemuhammed
rafes.net
sandeepmitta...
Contributor
6779 Points
1058 Posts
Re: Add primary key to existing table
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.
Sandeep Mittal | My Blog - IT Developer Zone
rafemuhammed
Member
302 Points
97 Posts
Re: Add primary key to existing table
Apr 06, 2012 02:44 PM|LINK
Hi sandeepmittal11..
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?
rafes.net
TabAlleman
All-Star
15571 Points
2700 Posts
Re: Add primary key to existing table
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.
tdmca
Contributor
2396 Points
661 Posts
Re: Add primary key to existing table
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'