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
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
SELECT A.* FROM A, B WHERE A.CITY = B.CITY AND (A.STATE B.STATE OR A.PINCODE B.PINCODE OR A.COUNTRY B.COUNTRY)
I am making project (desktop application) using C#.NET and SQL server 2008.I have used p.k and f.k in three tables….and when i update or delete or insert i get violation error and its natural but is there any solution that neither constraints get affected nor my prject.
I want to update a table in which almost 20 records for a single name.
I want to update dates in a table which is assoicated with a single name for 20 records. I want to keep the name unchanged n want to update the dates as 3,4,5, and so on.
You need to post some sample data with expected result to help you
hello friends,help me on this
Create table person(p_id varchar(10) unique,
name_s varchar(20))
I have created a table person and set p_id as unique.
Now i want to delete the Unique for the P_id..
how can i do that in sql server… reply me asap
Find the constraint name and use
alter table person drop constraint constraint_name
Really nice site…
Hi,
I have one question: I have one table Logs table lots of thread make entry in this table. The Primary key of this table in not identity column. I am getting primary key conflict error..what should i do now?
Hello Mr. Dave,
I read your articles so many time which are very fruitful for me,
From their I will get all most all the solutions of my query very easily , But I have some ambiguities
In my table. Lets give me solution for that .
My query is , may table is tab1(colname, tabname) here both are column name and multivalued
My table data is given as follows :
Opdate compupdtdtl
Cldate compupdtdtl
Totalamt loanstatus
[long list truncated]
My expected output is :
colname tab1 tab3 tab4
cldate cr_cp compupdtdlt rpl_emp_leave
loanid loantrans compupdtdtl loanstatus
Could you gime me solution of this problem ,
Anyone ?
i have two tables unit and item primary key(uid) of unit table is foreign key of item table
now i want to add the data in item table but due to foreign key problem my data is not inserted into item table in SQL server 2005
what is an alternate way to make insert query on database
can you pls give real time examples of a just pk and a unique key which is not PK
hai i need the command of without using constraint to drop the primary key
I have checked
Primay Key does not allow NULL value
how i update primary key value which have already foreign key value and i and i want to also update the record of foreign tables. tell me i am using ms sql 2005 server
Hi Pinal,
I tried creating table with unique key as below and getting error when trying to insert one record with NULL value.
create table TEST (
id int not null,
name varchar(100)
)
ALTER TABLE TEST
ADD CONSTRAINT UNQ UNIQUE(ID)
INSERT INTO TEST VALUES(1,’A’)
INSERT INTO TEST VALUES(NULL,’B’)
Error Msg:
Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column ‘id’, table ‘CR6_DW.dbo.TEST’; column does not allow nulls. INSERT fails.
The statement has been terminated.
But unique key should allow one null value right?
Version used:
Microsoft SQL Server 2008 (RTM) – 10.0.1600.22 (X64) Jul 9 2008 14:17:44 Copyright (c) 1988-2008 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (VM)
Hi This is Srikanth,
Is Possible to give Primary Key and Unique Key in same table .
yes it possible
Yes. Have you tried it?
yes
Hi Pinal,
I want to learn about SQL Transactions.Please Help me.
Thanks
With Regards
Amit Kumar
I want to throw exception while entering the values in registration form n ve to store it in database… wen i am giving email id n second time also i am using same id means the error should come as mail id is already exists in Database…i need code in asp.net(c#).
I want to throw exception while entering the values in registration form n ve to store it in database… wen i am giving email id n second time also i am using same id means the error should come as mail id is already exists in Database…i need code in asp.net(c#).
I learnt lot from this conversation…………
Dear Pinal Sir,
Please explain me how to disable Primary key constraint?
–we can disable primary key index like this
alter index ix_empId on employee disable;