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 GO
3)Â Â Â Â Â Alter Table to Drop Default Constraint
ALTER TABLE Customer DROP CONSTRAINT df_customer_Add GO
Reference : Pinal Dave (https://blog.sqlauthority.com)
131 Comments. Leave new
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.
hi…..,
can u notify d difference between primaryn foreign key constraints cn anyone xplain me in detail tanx fr ur kind response…….
hi satish,
suppose we have two tables like employee and department emptable have few columns like eno,ename,sal,deptno and same like dept table is also having same columns like deptno,dname so we want establish the relation between emp table and dept table there is a need to create primary and foreign key relationship on these two tables.make sure that these two tables same the same columns. in my example I have taken one deptno column in emp table and same deptno in dept table also then only this relation is established.
only one primary key is allowed per table and if we created any primary on any particular that that column accepts only unique values and it should not accept null values and it automatically creates clustered index on that column .
these constraints are very valuable in any database without these there is a chance to mislead the data
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
yes , it is….but not in all cases…
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
Thank for ur Wonderful Support…
i need to create three different tables with foreign key and primary key with auto increment can u send me the code,
Thanks&Regards
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..!
hi chaturi,
you didn’t mention your doubt clearly but you created a view using both of those table I will tell you something about view.
a view is nothing but a query or output of a query suppose we have to retrieve a data using query we can get the data successfully but again you want that data so you have to write the same query again n again so to over come these type of problems a views coming into picture I mean you can create view within that view you write your required query view are stored in database. so we want to retrieve the data again no need to write a query simply call a view with select statement,some important about view is view cant store any data just its stores the query.
we want to retrieve the data again you can run view in back ground the query is going to the original table and retrives our required data .view with in a view is also possible .
Thanks,
Avinash Reddy Munnangi