Primary key, Foreign Key and Default constraint are the 3 main constraints that need to be considered while creating tables or even after that. It seems very easy to apply these constraints but still we have some confusions and problems while implementing it. So I tried to write about these constraints that can be created or added at different levels and in different ways or methods.
Primary Key Constraint: Primary Keys constraints prevents duplicate values for columns and provides unique identifier to each column, as well it creates clustered index on the columns.
1) Create Table Statement to create Primary Key
a. Column Level
USE AdventureWorks2008
GO
CREATE TABLE Products
(
ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY,
ProductName VARCHAR(25)
);
GO
b. Table Level
CREATE TABLE Products
(
ProductID INT,
ProductName VARCHAR(25)
CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)
);
GO
2) Alter Table Statement to create Primary Key
ALTER TABLE Products
ADD CONSTRAINT pk_products_pid PRIMARY KEY(ProductID)
GO
3) Alter Statement to Drop Primary key
ALTER TABLE Products
DROP CONSTRAINT pk_products_pid;
GO
Foreign Key Constraint: When a FOREIGN KEY constraint is added to an existing column or columns in the table SQL Server, by default checks the existing data in the columns to ensure that all values, except NULL, exist in the column(s) of the referenced PRIMARY KEY or UNIQUE constraint.
1) Create Table Statement to create Foreign Key
a. Column Level
USE AdventureWorks2008
GO
CREATE TABLE ProductSales
(
SalesID INT CONSTRAINT pk_productSales_sid PRIMARY KEY,
ProductID INT CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES Products(ProductID),
SalesPerson VARCHAR(25)
);
GO
b. Table Level
CREATE TABLE ProductSales
(
SalesID INT,
ProductID INT,
SalesPerson VARCHAR(25)
CONSTRAINT pk_productSales_sid PRIMARY KEY(SalesID),
CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID)
);
GO
1) Alter Table Statement to create Foreign Key
ALTER TABLE ProductSales
ADD CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID)
GO
2) Alter Table Statement to Drop Foreign Key
ALTER TABLE ProductSales
DROP CONSTRAINT fk_productSales_pid;
GO
Default Constraint: Default constraint when created on some column will have the default data which is given in the constraint when no records or data is inserted in that column.
1) Create Table Statement to create Default Constraint
a. Column Level
USE AdventureWorks2008
GO
CREATE TABLE Customer
(
CustomerID INT CONSTRAINT pk_customer_cid PRIMARY KEY,
CustomerName VARCHAR(30),
CustomerAddress VARCHAR(50) CONSTRAINT df_customer_Add DEFAULT 'UNKNOWN'
);
GO
b. Table Level : Not applicable for Default Constraint
2) Alter Table Statement to Add Default Constraint
ALTER TABLE Customer
ADD CONSTRAINT df_customer_Add DEFAULT 'UNKNOWN' FOR CustomerAddress
AGO
3) Alter Table to Drop Default Constraint
ALTER TABLE Customer
DROP CONSTRAINT df_customer_Add
GO
Reference : Pinal Dave (http://blog.SQLAuthority.com)
i see the different between column level & table level in terms of creating them, but what’s the significance by having a foreign key defined at column level ?
Hi! Pinal ,
Could you please tell me, what is difference between defining the constraint on column level or Table level?
Hi!
How can we add Unique constraint through Design mode? I do not want Code or query.
[...] Default constraint must be defined at the column level. All other constraints must be defined at the table level. (Read More Here) [...]
Sir,
I am newbiee…not clear on CREATE TABLE.
CREATE TABLE dbo.Project (
ProjectID VARCHAR(10) NOT NULL,
ProjectName VARCHAR (50) NOT NULL,
ProjectDesc VARCHAR (50) SPARSE NULL
CONSTRAINT pk_project_pid PRIMARY KEY (ProjectID);
GO
ALTER TABLE dbo.Project
DROP CONSTRAINT pk_project_pid;
GO
ALTER TABLE dbo.Project
ADD CONSTRAINT pk_project_pid PRIMARY KEY (ProjectID);
go
Questions:
1. It’s necessary to DROP CONTRAINT pk_project_pid?
2. It’s a good practice to create constraint in Table level?
Many thanks for your kind help.
Edwin
hi…..,
can u notify d difference between primaryn foreign key constraints cn anyone xplain me in detail tanx fr ur kind response…….
My primary key table, PrimaryLiterals stores two different types of literals – race and ethnic. In another table, I have two different columns that refer back to the key, PrimaryLiteralID, of primary key table – RaceLiteralID and EthnicLiteralID. Is it possible to create two foreign key constraints both referencing the PrimaryLiteralID or do two separate tables need to be created?
Hello Diane,
A primary key can be referenced by multiple foreign keys.
Kind Regards,
Pinal Dave
hi,
i m using sql server 2008 and for that i have one table named tabdealermaster in that i m using one primary key as DealerId .and in same table i m using that field as foreign key as dealer_ParentId .is it possible? m i right?
plz tell me. its urgent
just want to thanks you for your priceless work
how can i use default primary key such as (0 ,1,2,3,4,5,6 ……)
for the data
how to add foreign key when we have more than two tables
Hello Ashwini,
Your question is not clear to me. I think you want to refer a primary key in multiple tables. You can do that as a primary key can be referenced from multiple tables.
Regards,
Pinal Dave
This is the good question….i will give u answr latter….
Hello sir,
i’m creating a new Foreign key in Factresellersales table
using PK in Dimtime table and timekey(PK)
the below statement gives an error I cannot understand the reason why? please guide me the correct procedure…I’m new to SQL server
alter table dbo.FactResellerSales
ADD constraint fk_Timekey
foreign key (TimeKey) References DimTime(TimeKey)
Go
the error I get is ,
Foreign key ‘fk_Timekey’ references invalid column ‘TimeKey’ in referencing table ‘FactResellerSales’.
@Prathyusha
Make sure DimTime.TimeKey is a valid COLUMN, has a UNIQUE INDEX, and FactResellerSales.TimeKey is the same data type.
Hello Prathyusha,
Even the syntax of your statment is correct but following statement should also complete:
alter table dbo.FactResellerSales
ADD FOREIGN KEY (TimeKey) References DimTime(TimeKey)
Go
Let us know if you still get the error.
Regards,
Pinal Dave
Ur posts r really helpful..
thanx..
Great Article thanks !!
Sir,
Sir i am firstly going to make table using normal forms so please you have help me to create table with discretion.
{custid,firstname,middlename,Lastname,birthofdate,accountcreationdate,Address,type(creadit or cash),mobilenumber(multivalue),emailid(multivalue).
Sir,
I want how to learn code in c# .net,please specify if there is any book,site……………………
Sir,
What is the difference between table level and column level constraints?
Actually, when you create a column level constraint and generate the script of the same table, it shows as table level.
Thanks in Advance
ok i have understand
Sir,
is it possable to create a foreign key on unique key colum?
i have table which has a unique key (organization_code,product_barcode) and i want to add a foreign key from another table which has these two column s in this table please help me
Preeti
CREATE TABLE Software_Manuals (
item_no varchar(40)PRIMARY KEY,
title varchar(100),
descript varchar(100),
vers varchar(30),
manufacturer varchar(50)
);
SELECT *
From Software_Manuals
INSERT INTO Software_Manuals
VALUES (100,’User guide’,'Adout Hardware’,2009,’Samsung’);
INSERT INTO Software_Manuals
VALUES (101,’Software testing’,'About Software’,2008,’Samsung’);
INSERT INTO Software_Manuals
VALUES (102,’Protected mode’,'Security’,2010,’Kaspaskey’);
INSERT INTO Software_Manuals
VALUES (103,’Macintosh’,'MAC address’,2007,’LG’);
INSERT INTO Software_Manuals
VALUES (104,’Educational software’,'About Education’,2011,’Joeant’);
CREATE TABLE Copy_SW_Manual (
access_no integer PRIMARY KEY,
item_no varchar(40)REFERENCES Software_Manuals
);
SELECT *
FROM Copy_SW_Manual
INSERT INTO Copy_SW_Manual
VALUES(100,0001);
INSERT INTO Copy_SW_Manual
VALUES(101,0002);
INSERT INTO Copy_SW_Manual
VALUES(102,0003);
INSERT INTO Copy_SW_Manual
VALUES(103,0004);
INSERT INTO Copy_SW_Manual
VALUES(104,0005);
CREATE VIEW VW_SW_MANUAL (title,manufacturer,vers,access_no)
AS
SELECT SM.title,SM.manufacturer,SM.vers,CSM.access_no
FROM Software_Manuals SM, Copy_SW_Manual CSM
WHERE SM.item_no=CSM.item_no
Sir,
this is argent. i can’t understand these questions.
1) if the inserted row contains an access that exists in the table, then update the version, title & manufacture columns of Software_manuals table with the inserted data
2) Else,if the title,manufacture and version exists in the Software-Manuals table,insert a new row to Copy_SW_Manual table with its item information referncing the existing row of the Software_Manuals table
3)Else, insert rows to Software_Manuals and Copy_SW_Manual tables to reflect the new information
Please send me the Answers of these questions pls………..sir
Thankyou..!
I’ve a problem in sql server just could you tel me ?
there are four tables in my database and there are two primary keys in two different table I need to filter data s by using those primary keys when I prepare reports, but in the beginning I’ve given only 1 primary key as foreign key to other tables
My question is can I insert in this stage the other particular primary key as foreign key to other tables?
its very argent ! I know you can help me
Hello Mr. Stephen,
You can insert the other primary key as foreign key to other tables provided you have the data in the tables that do not conflict with the combination keys (existing primary key and the new foreign key). In that case, you will have to make the corrections in the data already existing in the table and then insert the keys.
Also, after making changes in the design of the table, do not forget to refresh the data in reports.
Kind Regards,
Pinal Dave
sir
please tell me how can i create table in sql server.what process of start sql and then reached the table sheet.and also create contrant primary key foreign key .
Read about “CREATE TABLE” in SQL Server help file
first you have to create one table then –.> go to stored procedure –>
and write code like a
create procedure [procedure_name]
(
declare variables
[while u declare a variable start with ' @ ' and end with ,]
example
@name nvarchar(50)
Hello Mr.Pinal Dave,
I read your post regarding Constraints in Sql.Its very useful & Easy to understand.
As well,Short and Sweet.
Thanks a lot..
Regards
Anbu with Love
I am a newbee to SQL and I am working on a metrics table. I have the SQL 2008 Express on my computer.
The name of the database is PlantMetrics and the name of the table is dbo.PerformanceIndexes.
I have three columns containing:
PlantName
Month
Year
The combination of values in these three columns is unique. When I post the metrics for each plant for each month, the values of PlantName, Month and Year will repeat from record to record.
Can I denote these three columns as primary keys? If so how?
Or do I leave the table without primary key constraint?
I want to add other tables to the database with details of other characteristics for the plants and relate them to this table.
The other tables will have unique values for the PlantName etc.
Thanx for your Example Posts.They are really really helpful
hi sir,
i am vinoth we are using sql server 2008 i want to display the content table for daily updates….
hi sir,
i am vinoth. we are using for sql server 2008. i want display
recorded data… for example when i entered in office using
my id card that time..the product software recorded…
already recorded and new record data i want to display
screen with using sql server 2008.
which command we have to use…. please give me solution..
Thanks & Regards,
vinoth.A
If you have datetime column with default getdate(), you can find it
can you please tell me about logins and users n sql server?
Hi Pinal Sir,
Thanks for your tutorial about adding primary key and foreign key using alter statement at column level and table level.
Anshu
Hi Pinal Sir,
In the scenario of foreign key and primary key both are providing the same feature but what is the actual difference between column level and table level ?
hi i want create a login form in master page and relavented tables in database
CREATE table Customers
(CustomersID char(5) not null,
Address varchar (60) null,
City char(15) null,
Phone char (24)null,
Fax char (24) null,
constraint pk_Customers primary key (CustomerID)
)
CREATE table Orders
(OrdersID int,
CustomerID char not null,
Orderdate datetime null,
Shippeddate datetime null,
Quantity int null,
constraint pk_Orders primary key (OrdersID, CustomerID)
)
Hello
Pinal
Your posts are really very helpful
thank you
Hi Pinal
I create salespeople table
create table salespeople(snum int CONSTRAINT pk_snum PRIMARY KEY,sname varchar(50),city varchar(50),comm decimal(12))
i inserted data like this
insert into salespeople values(1002,’bama’,'bangalore’,.18)
insert into salespeople values(1003,’kama’,'Bobbili’,.15)
insert into salespeople values(1004,’anjuri’,'vizag’,.14)
———————-
then i create customer table
create table customer(cnum int INT CONSTRAINT pk_productSales_sid PRIMARY KEY,
Cname varchar(50),
city varchar(50),rating int ,
snum int CONSTRAINT fk_snum_pid FOREIGN KEY REFERENCES salespeople(snum))
inser values
insert into customer values(200,’jayanth’,'bangalore’,100,1004)
insert into customer values(2005,’bhagyasri’,'guntur’,200,1006)
—————-
i create order table
create table orders(onum int CONSTRAINT pk_productSales_sid PRIMARY KEY,
amount decimal(10),Orderdate datetime,
snum int CONSTRAINT fk_snum2_pid FOREIGN KEY REFERENCES salespeople(snum),
cnum int CONSTRAINT fk_cnum_pid FOREIGN KEY REFERENCES customer(cnum))
i got the error
Msg 2714, Level 16, State 4, Line 1
There is already an object named ‘fk_snum_pid’ in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
How to solve those errors
plz send details urgent pinla
Hi Pinal Sir,
I hava a problem in creating foreign key in my sql table.im using sql 2008.
Can i set two foreign keys in two child tables with references to one unique key in main table .
pleas help me.
try to chage your main table unique key bcoz unique key access from the null values so try to use primary key then u il set the foreign key ur table
i am now satisfy with ur answer
thanks
dear sir,
i have a table called student_ id with 3 column id(pkey),name and course.
i wants to access id (start with 3 and end with 8,means 5 values)
hw can this is possible with asp.net application
can anyone help me!!!
when ever i create identity primary key tat error showing all the times
“Must declare the scalar variable “@”ID”
HAI,
I want learn SQL more.Is any way to understand SQL easely.
REGARDS
Dinesh
Can’t we create a default constraint at table level ???
Hi Mr. Pinal,
One table having one cluster Index in the case of one primary key. If more than one primary keys are there in a table, then how many clustered Indexes are created for table?
Please reply me……
Thanks & Regards
Madhu….
Note that there cannot be more than one primary key in a table
Hi madhivanan,
we can create more than one primary key we can call it as composite key..
It is a single primary key combined on many columns. You cannot create primary key on each column seperately.
Hi, greeting from Indonesia. I’ve been looking for script of creating a foreign key related to the primary key from the column level and your explanation was great. Thank you very much! God bless
Greetings Pinal – Always find your answers helpful and spot on. At the moment I’m stuck in SQL/Server hell and can’t find a solution. I have a table that I can’t drop, can’t truncate, can’t do anything with because of this message “it is being used for foreign key constraint enforcement.” I have dropped ALL of the constraints on the table, and still can’t get around this error. There is a unique index that is being referenced that can’t be dropped either because of the same error response. I’m stuck in a circular hell. How do I get out of hell with this table?
Your posts are really usefull
Thanks for your post.
Thank you, always seem to forget the exact syntax on many of those.
how to insert a manual int value in primary key , sql server 2008
You can do it in INSERT and UPDATE statements
Hi sir,
My question is-
In sql server authentication mode which is more secure authentication? (Windows or Sql server)
window authentication mode used when you use your application with your system and sql server authentication is used when multiple application used a centrally database .
[...] Default Constraint Over Table Column Create Primary Key with Specific Name when Creating Table Creating Primary Key, Foreign Key and Default Constraint How to ALTER CONSTRAINT Disable CHECK Constraint – Enable CHECK Constraint Query to Display [...]
How do I fetch MAC address in SQL server to implement in triggers.