Primary Key:
Primary Key enforces uniqueness of the column on which they are defined. Primary Key creates a clustered index on the column. Primary Key does not allow Nulls.
Create table with Primary Key:
CREATE TABLE Authors (
AuthorID INT NOT NULL PRIMARY KEY,
Name VARCHAR(100) NOT NULL
)
GO
Alter table with Primary Key:
ALTER TABLE Authors
ADD CONSTRAINT pk_authors PRIMARY KEY (AuthorID)
GO
Unique Key:
Unique Key enforces uniqueness of the column on which they are defined. Unique Key creates a non-clustered index on the column. Unique Key allows only one NULL Value.
Alter table to add unique constraint to column:
ALTER TABLE Authors ADD CONSTRAINT IX_Authors_Name UNIQUE(Name)
GO
Reference : Pinal Dave (https://blog.sqlauthority.com), BOL
97 Comments. Leave new
Can u please tell me, what is the maximum number of unique key constraints that can be defined in a table.
Also I have a query regarding joins in SQL-Server 2005.
In a single select query, what is a number of max joins that we can have?
Read about Maximum Capacity Specifications in SQL Server help file
Hi Pinal,
In your Unique Key definition section you wrote, “Unique Key does not allow Nulls.” But i think Unique Keys allows one null value in the column on which they are defined.
Yes u are Write Unique Constraint can Contain 1 null Value Because 1st Time Null Is Also Unique.
perfect answer of unique value in Unique constraint
primary key is unique identifiying rows in s trable Ok…
i agree with that but whats the problem with Primary Key means if you are not difine as a NOT NULL it will also taking null values as like unique key .
So you should difine Not Null while difinig key as a Primary key.
First check it out Reply me don’t forget oK……….
According to My Knowledge
Primary Key not Allow Null Values , But UNique kay may be null
to
respected sir,
i have created ta table and now i want to add a constraint
primary key in SQL SERVER 2005
but its generating error
code:
1) create table sample(sno varchar(20))
2) ALTER TABLE sample
ADD CONSTRAINT pk_sno PRIMARY KEY (sno)
error details :
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table ‘sample’.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
KINDLY SEND ME A SOLUTION
first u have to make the not null
first u run the query
Alter table sample modify sno datatype not null
After this query u can define the primary key on the column.
hi ankhenatraj
Please refer the dataschema of your database and check if your column ‘sno’ of sample table has set to ‘NULL’. If so change that to ‘NOT NULL’ as primary key cannot be defined for a nullable column.
Or else you can generate a unique constartint for that column ‘sno’ as unique constratints can be defined for the nullable columns.
Hi Deepika,
I’m also facing the same problem when adding primary key to a column of an existing table.
Error Msg:
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table ‘sample’.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
You sugested “refer the dataschema of your database and check if your column ‘sno’ of sample table has set to ‘NULL’. If so change that to ‘NOT NULL’ as primary key cannot be defined for a nullable column. ”
Please let me know how to refer to datascheme and change null to notnull.
Thanks in Advance
Swarup
You can alter the column to have not null value
alter table table_name
alter column your_col not null
Hi Swarup & ankhenatraj,
when i tried to create primary key as you said, i have also got the same error as you guys mentioned.
try the steps as deepika mentioned. here are the steps again for you.
1) right click on the table name and select “Modify” in the Object Explorer(which is right side of the query window)
2) remove the check mark for the column sno under Allow Nulls(which will then not allow the null values for that column and which is primary for creating the primary key)
3) close that window and it will ask to save that table, then save it
4) now again try to run the add constraint query, now it will execute successfully…
–Narasimha
hi
Can u please tell me, I have two questions
1.what is difference between Primary key & Unique Key Constraints?
2.Which Constraints act as a primary key like not null and unique key Constraints?
1 Primary key is unique and not null. Unique constraint can have only one NULL
2 Primary key
Hi,
I want to drop the primary key on one table but i cannot know which constraint is there. Is there a way to drop the primary key without specifying constraint.
The basic idea of doing this is :
I have one table with 4 columns e.g.
1. SrNo
2. NodeID
3. EnrollmentNo
4. FingerNo
Now the two columns already have Primary key NodeID and EnrollmentNo now i want to add another primary key FingerNo as well.
Thanks,
Prashant Hirapara
Type sp_help
your able to find the constraint name for the primary key
take the name of the constraint and drop it
using
drop constraint
@ Prashant
I am not sure if you want to use GUI to do the same. Running queries is always good… but this can be done very easily using Enterprise Manager (SQL Server 2000 ) or SSMS ( SQL Server 2005)
1. SQL Server 2000
open Enterprise manager -> expand server -> expand database -> expand tables -> on the right side-> right click table name -> design table ->
now you can see all the columns in that tables also you can see some primary key on one or more than one column, if you want to add one more column in the primary key, just do the following,
1. First remove allow null check on that column.
2. Press the control button and select all the column you want to be in your primary ( include previous primary key columns as well)
3. Now right click the select columns, select Set Primary key.
4. On the top, save the table.
Just to check again, refresh database open table – design and see if you can see the primary key includes your new column.
SQL Server 2005.
Expand Server- Database – tables – keys – right click key name and click modify, do the same process you did in SQL Server 2000, first you will uncheck allow null on that column, and then you will press control button and you will select all the columns you want to have in primary keys and then right click on those selected column, select set primary key.
at the top, click save and check once again if the changes has taken place.
One more thing, if you are using Varchar(max), text, ntext,nvarchar(max), image datatypes, then I dont think you will be able to set primary key, if you have any varchar(max) columns then try to give a fix values to those columns, like varchar(100).
Now your question remains the same, which one is primary key, what is the name of the primary key,
IN SQL Server 2005, you can easily detect what is your primary key( under database – tables- keys) , because the prange color of primary key is different from foriegn keys which is grey color, and there can be only one primary key on one table.
In SQL Server 2000 it is difficult to see as primary key and a foreign key.
Try running these script, which will give you all primary key on all tables in the database including column information.
SELECT A.TABLE_NAME, A.CONSTRAINT_NAME, B.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE CONSTRAINT_TYPE = ‘PRIMARY KEY’ AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
ORDER BY A.TABLE_NAME
Sometimes you will see the primary constraint name repeats, meaning that primary key is a composite key.
So now you have Primary Key constraint names and I told you procedure , you can do like that or what ever procedure you were trying to do earlier, I guess you were trying to drop primary key and then create a new primary key on the table. You can do this as well.
Make sure no users are connected to that database, because when you create a primary key, by default a clustered index is created, clustered actually physically stores the data, meaning the tables on which you are trying to create primary key will be un available for users.
Hope this helps.
Imran.
Hi Imran,
Thanks for your prompt reply and i am sorry i have not mentioned that i am using SQL 2000 and i want to execute my things through query.
Now your above query gives me the list of all the primary key constraint along with the column in which primary key is used. but it does not allow me to delete any of the key as it says that the information is view and cannot be update or delete.
At last i have come to this query which is not working
:-
———————————————————————–
Alter Table tblFPTemplates Drop Constraint
(
Select Name
From sysobjects
Where xtype=’PK’ And parent_obj = (Select id From sysobjects Where xtype=’U’ And Name=’tblFPTemplates’))
———————————————————————–
Can u please modify the above query to work it out for me. Again thanks a lot for your input.
Thanks and Regards,
Prashant Hirapara
Hi,
Can u please tell me,
PRIMARY KEY is equivalent to UNIQUE KEY + NOT NULL ????
Yes. You are correct
Can u please tell me how to update table with primary key on diffrent column and unique key on diffrent column? is that based on primary or unique key?
Create table with Primary Key:
CREATE TABLE Authors (
AuthorID INT NOT NULL PRIMARY KEY,
Name VARCHAR(100) NOT NULL
)
GO
Alter table with Primary Key:
ALTER TABLE Authors
ADD CONSTRAINT pk_authors PRIMARY KEY (AuthorID)
GO
Kallol
Hi,
In Which case we can use Unique Key.Please give one example so i can understand.
Regards
Prashant
Prashant – to drop a PK from a table, when You don’t know a PK’s name, You need to run something like:
Declare @pkName nvarchar(500)
Declare @sql nvarchar(500)
select @pkName=name
from sysobjects
where xtype = ‘PK’ and parent_obj = object_id(‘YourTableName’)
IF NOT @pkName IS NULL
BEGIN
select @sql = ‘ALTER TABLE YourTableName DROP CONSTRAINT ‘ + @pkName
execute sp_executesql @sql
END
Thank U Very much pinal dave
Dear Pinal,
I like your site very much. Few concern about pk and unique key
1) how many columns could be included in unique key(i mean whether composite unique key is possible or not)
2) Could we edit values of the unique key/pk (keeping them unique)
3) how to use composite pk or fk as foreign key
4) which one is better choice composite pk or composite unique key
5) What if wanna modify composite pk column value
I could try in db to get my ans but wanna confirm frm you.
@spider
1) Yes, composite UNIQUE keys are allowed.
2) Yes, because you don’t edit the key, you edit the values. Then the UNIQUE CONSTRAINT will make sure the data is still unique. If it is, it will allow the edit.
3) Like any other FK. FOREIGN KEY(col1, col2) REFERENCES other_tab
If the parent is a UNIQUE key instead of the PRIMARY KEY
FOREIGN KEY(col1, col2) REFERENCES other_tab(col1, col2)
4) That depends on the situation. Assuming i understand the question, PK would be better.
5) See question 2.
We can create composite like
CREATE TABLE Authors (
AuthorID INT NOT NULL,
Name VARCHAR(100) NOT NULL,
PRIMARY KEY(AuthorID,Name)
)
GO
am I right??
Thanks 4 the quick reply :-)) you saved my effort.Thats the way to get more hits ,love and respect from the users :-))
Keep it up…