April 4, 2012 at 1:50 pm
Hello Guys,
I have one table with no contraints and populated with records, and now i want to define a primary key on it. I used
Alter table employee
Add contraint pk_employee
primary key (ID)
but it gives an error
"Can not define primary key constraint on nullable column in table 'emploee'. could not create constraint"
My table dont have any null values and all the records are unique.
Can any one help me, please?
April 4, 2012 at 1:59 pm
Pleasse read the error message again: Can not define primary key constraint on nullable column in table 'emploee'.
The column itself allows null values. This is what is keeping you from creating a primary key on this column.
April 4, 2012 at 2:19 pm
So it is not possible to add primary key on any populated table? or i need to another coulumn which will not allow null vaules ?
April 4, 2012 at 2:21 pm
jitendra.padhiyar (4/4/2012)
So it is not possible to add primary key on any populated table? or i need to another coulumn which will not allow null vaules ?
Not what is being said. To create a primary key, none of the columns (if making a composite primary key) can allow null values. If you are 100% certain that the column you want tomake the primary key has no null values, you can alter the column to not null and then create the primary key.
April 6, 2012 at 9:54 am
jitendra.padhiyar (4/4/2012)
So it is not possible to add primary key on any populated table? or i need to another coulumn which will not allow null vaules ?
A Primary Key is a Referential Integrity constraint that intends to uniquely indentfy each single row in the target table - that's the reason why (as pointed out before for other poster) Primary Key column/s do not allow for Null values.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 6, 2012 at 10:05 am
Thanks a lot Pettis and Paul !
Now i m cleared about primary key concept. I also tried to insert a column using Alter command with not null, but it did not allow me to do so.
Becos alter table command only allows columns to be added that can contains nulls after table populated.
Thanks again. !!
April 6, 2012 at 10:45 am
jitendra.padhiyar (4/6/2012)
Thanks a lot Pettis and Paul !Now i m cleared about primary key concept. I also tried to insert a column using Alter command with not null, but it did not allow me to do so.
Becos alter table command only allows columns to be added that can contains nulls after table populated.
Thanks again. !!
Unless you provide a default value for the column by including a default constraint in the definition of the new column.
April 9, 2012 at 1:18 am
If you think that your table does not contain nulls and only contains unique data, then you can alter the table to add a primary key as follows :
1. Open Design View in SSMS.
2. Select the column you want to place the Primary Key Constraint on.
3. In column Properties change allow nulls From 'NO' to 'YES'.(This would be possible only if your table does not contain nulls.)
4. Right click on the column and add Primary Key Constraint.
5. Save the changes you made in the design View.
I hope this is a solution.
Vinu Vijayan
April 9, 2012 at 7:42 am
Thanks Vinu for suggestion, i tried it but it did not work when I save the changes.. its give me an error..
"Saving changes is not permitted. The changes you have made require the following table to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be recreated."
Can anyone tell me how to recreate table ? Will there be any data loss in recreation of table ?
April 9, 2012 at 10:48 am
jitendra.padhiyar (4/9/2012)
Thanks Vinu for suggestion, i tried it but it did not work when I save the changes.. its give me an error.."Saving changes is not permitted. The changes you have made require the following table to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be recreated."
Can anyone tell me how to recreate table ? Will there be any data loss in recreation of table ?
SSMS for some reason likes to move the data to a temp table and drop and recreate the table then move the data back. (at least i think i remember seeing that here)
what you can do is run the following SELECT * FROM table WHERE [column for pk] IS NULL
if that returns any rows you have an issue of NULL data in the column you will have to fix. if not the next thing to run is SELECT COUNT(*), [column for pk] FROM table GROUP BY [column for pk] HAVING COUNT(*) > 1
this will tell you if you have duplicates another reason you cant create a primary key. if both those queries return 0 rows we can go from there.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 9, 2012 at 11:18 am
If you select Tools in the menu, then Options, Designers, Table and Database Designers you will find an option that will allow you to modify a table that needs to be dropped and recreated using the GUI. As it is currently configured, you need to use scripts to make your changes.
April 9, 2012 at 10:13 pm
capn.hector (4/9/2012)
jitendra.padhiyar (4/9/2012)
Thanks Vinu for suggestion, i tried it but it did not work when I save the changes.. its give me an error.."Saving changes is not permitted. The changes you have made require the following table to be dropped and re-created. You have either made changes to a table that can't be re-created or enabled the option Prevent saving changes that require the table to be recreated."
Can anyone tell me how to recreate table ? Will there be any data loss in recreation of table ?
SSMS for some reason likes to move the data to a temp table and drop and recreate the table then move the data back. (at least i think i remember seeing that here)
what you can do is run the following
SELECT * FROM table WHERE [column for pk] IS NULLif that returns any rows you have an issue of NULL data in the column you will have to fix. if not the next thing to run is
SELECT COUNT(*), [column for pk] FROM table GROUP BY [column for pk] HAVING COUNT(*) > 1this will tell you if you have duplicates another reason you cant create a primary key. if both those queries return 0 rows we can go from there.
Try opening the designer and Select "No" for the Allow Nulls option in the properties window of the proposed primary key(ID). Save what you have done.
Does the Designer still give you the same result??.....If it does then I think your table does contain NULL values and those columns will be affected by you making the changes. That is why the designer is not saving your changes. Read this if you get the error on changing the option to Allow Nulls:
If you still think that your field(ID) contains all the properties required for it to become a Primary Key, then:
Hope this was helpful.
Vinu Vijayan
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply