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
What is reason for having only one NULL IN unique key constraint?
Hi Pinal.
I am looking for the sql statement, by which executing, i can come to know which column(s) is having unique constraint and which is/are not having.
Hi Pinal,
Thanx for the reply. It solved my problem…..
It is really very useful me…
Thanx again….
Pinal, I am fond of learning SQL deeply. And I have heard lots of about your knowledger and you. So I would love to attend your seminars. So can you plz tell me how can I get the information about your upcoming seminars?
Regards,
Nilesh Soni
Hellow Pinal,
You solved my last problem. But I’ve come again with another problem.
I need to change the data type of column ‘Science’ in table ‘tblMarks’.
I want to change the data type from int to varchar(100) and want to set the default value ‘abc’.
I’ve tried too many times with different sql statements and have checked lots of websites. But didn’t succeeded.
Here I have written a sql statement which is producing error.
ALTER TABLE [dbo].[tblMarks] Alter Column Science varchar(100) Not Null DEFAULT ‘abc’
Before changing the data type the column ‘Science’ does not have any default constraint created on it.
Plz help me.
Regards,
Nilesh Soni
@Nilesh
The easiest way to do this is to DROP the existing COLUMN and ADD a new one.
@Brian Tkatch
Thanx for reply..
But what about the data inserted in to table if I’ll drop the column….
@Nilesh
1) ADD a new COLUMN of the correct type.
ALTER TABLE [dbo].[tblMarks] ADD Science2 varchar(100) Not Null DEFAULT ‘abc’;
2) UPDATE [dbo].[tblMarks] SET Science2 = Science;
3) ALTER TABLE [dbo].[tblMarks] DROP Column Science;
4) 3 options.
4a) sp_rename
4b) Open tblMarks in Design (right-click) and change the name.
4c) ADD Science as the new COLUMN, UPDATE it to equal Science2, and DROP Science2.
Hello Nilesh,
What is the error produced by ALTER TABLE statement?
Try to perform alter column length using SSMS interface when table is not being used.
Regards,
Pinal Dave
Hi Pinal,
I’m getting below error while executing the alter statement “ALTER TABLE [dbo].[tblMarks] Alter Column Science varchar(100) Not Null DEFAULT ‘abc'”.
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword ‘DEFAULT’.
and i’m unaware about SSMS interface.
Regards,
Nilesh Soni
Hello Nilesh,
Break the statement in two as below:
ALTER TABLE [dbo].[tblMarks] Alter Column Science varchar(100) Not Null
ALTER TABLE [dbo].[tblMarks] ADD CONSTRAINT Sc_Default DEFAULT ‘abc’ FOR Science
Regards,
Pinal Dave
Hi Brian Tkatch.
Thanx for reply.
But Brian my purpose is not for just a single time execution. I’m developing a tool which used to create the xml file at admin side or our side. the file will contains the information about changes in the database and the same tool will read the xml at the client side and will make the changes by creating and executing the sql statements. so the way you suggested is little bit long to altering the table. and i’m sure there is a way to alter the table with default value and not null attribute.
Thanx once again Brian…
Regards,
Nilesh Soni
Oh, i see. I didn’t realize this was for a script. I was trying to provide a simple solution for your scenario.
Pinal’s answer looks best. I think i learned something too.
hi
i created a table with columns such as p_id(unique),name(unique),address,city.
now i want to drop the constraint for name column.
whats the query to done the same
Alter tabke table_name
drop constraint constraint_name
HAI….
I HAVE ANOTHER PROBLEM….
I CREATED A TABLE AS
CREATE TABLE PERSONS(PID INT, LASTNAME VARCHAR(8),FIRSTNAME VARCHAR(8))
NOW I WANT TO ADD PRIMARY KEY FOR PID COLUMN…
FOR THIS I WROTE A QUERY AS
ALTER TABLE PERSON
ADD PRIMARY KEY (PID)
I AM WAITING FOR UR ANSWER
WHILE I EXECUTE THIS ,I FACE SOME ERROR. I MENTION IT BELOW
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table ‘primaryalter’.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
PLS SOLVE MY PROBLEM..
Hi Ramya,
it seems the column PID is accepting null values..that’s why the PK constraint is not defined. go to the table design and remove the Allow Nulls check mark for the PID column and then try again.
hi,
I want Add Unique key constraint on my existing table which already have data.
I run this query..
ALTER TABLE tbl_Standard ADD CONSTRAINT Unique_Standard_Name UNIQUE(Standard_Name)
I think error is due to this names
Ex
in first row ‘ASTM C 295 – 98’
in 2nd row ‘ASTM C 40 – 98’
these both values are different but I am receiving error.. plz help me..
Error
“CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 5. Most significant primary key is ‘ASTM C’ “
Hello Rizwan,
its a bug identified in SQL Server 2000. For more details please check the below page:
https://support.microsoft.com/en-us/help/308811
Regards,
Pinal Dave
Isn’t the other *BIG* difference between PRIMARY KEY & UNIQUE INDEX the fact hat UNIQUE INDEX allows you to add columns using the INCLUDE keyword, and PRIMARY KEY will not.
@Antek
To do that, you would have to compare PRIMARY KEY to UNIQUE KEY. Being they both CREATE their own INDEXes, they both have the same restriction.
Nope.
Primary key is basically unique index. By default it is also clustered index, which defines how data is ordered by default, but it can be non-clustered also and then it behaves as normal unique index.
Include can be used with non-clustered indexes and index doesn’t even have to be unique.
hi
iam using select query in my sp to reterieve the values from the temp table. In my sp iamgetting the values from the temp table by calling another sp by passing the parameter and then i am getting the values through select query using 2 right joins and 3 left outer join with where condition and with group by.
but this select statement is taking more than 15 minutes to execute. i have already created index for the temp table. canu plz give me any solution
Hello Aswathaman,
This is not sufficient to identify the issue and recommond a solution. You can get the correct solution by checking the Execution Plan of query. In execution plan, Idetify the most resource consuming tasks and apply a fix.
Regards,
Pinal Dave
1. Is it good to use foreign key reference to a unique key. Please advice.
2. How to hide primary key from view of user in sql server.
Hi,
Can you please tell me why Unique Constraint can allow only one Null and no more?
You know that Null is not a value so what is the reason of this?
@Majid
SQL Server (and DB2, IIRC) store NULLs on the INDEX. Even NULL can only take up one space in a UNIQUE INDEX. Oracle does not put NULLs on the INDEX, so it can have as many as there are. Both have pros and cons.
Regardless, SQL Server 2008 can support more than one NULL with a filtered INDEX, WHERE col IS NOT NULL;
what is the difference between
unique(a,b) and
unique(a), or
unique(b), or
unique(a) unique(b)?
unique(a,b) means combination of a and b is unique
Others indicate individual columns should be unique
@ahmed
unique(a): COLUMN a must be UNIQUE by itself.
unique(b): COLUMN b must be UNIQUE by itself.
unique(a) unique(b): Both COLUMN a and b must be UNIQUE separately.
unique(a,b): COLUMNs a and B must be UNIQUE in combination.
a b
– –
1 2
1 3
That is unique(a,b), but is not unique(a) unique(b), because b is not UNIQUE on its own.
hi,
i need a sql 2000 script to find the unique constraint in a spacific table
thnx
Start with
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
Hi,
I have to alter some column’s datatype in many tables. Problem is, the columns are composite keys or foreign keys. When I am altering the data type its giving error
Server: Msg 5074, Level 16, State 8, Line 1
Server: Msg 4922, Level 16, State 1, Line 1
I do not want to drop the constraint as there are lots of data in the tables.
Please help
thanks
Harsha
You need to disable the constraint before alter and enable it after alter
Hi
I have a two tables A1 & A2 with columns
ADD1, city, state, postal_code & country
& B_ADD1,B_state,B_postal_code & B_country.
The second table has the correct combination of city,state,postal_code and Country &
the first table can have errors.
I need to write a query or create a view which will fetch me all the combinations of A which does not have a perfect match in B
for example
A has
hyd,ap,500056,india
bombay,ap,500056,india
B has
hyd,ap,500056,india
bombay,mah,2993849,india
my query must return the error record from A
bombay,ap,500056,india
after checking all combinations in B
Thanks in Advance
Prashanth