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)
pinaldave
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