SQL SERVER – 2008 – Creating Primary Key, Foreign Key and Default Constraint

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

SQL SERVER - 2008 - Creating Primary Key, Foreign Key and Default Constraint 5_AllQuery

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

SQL SERVER - 2008 - Creating Primary Key, Foreign Key and Default Constraint 10_ALLFKquery

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

SQL SERVER - 2008 - Creating Primary Key, Foreign Key and Default Constraint 14_DFAllQuery

Reference : Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – Explanation about Usage of Unique Index and Unique Constraint
Next Post
SQL Server – Error : Fix : SharePoint Stop Working After Changing Server (Computer) Name

Related Posts

131 Comments. Leave new

  • How to find the constraint name…

    Reply
  • Hello,
    If i run sequence i am getting error “Unknown object type ‘SEQUENCE’ used in a CREATE, DROP, or ALTER statement.” how to solve this problem

    Reply
    • Note that Sequence is available from version 2012 onwards only

      Reply
      • Hello,
        How to store URL in table
        I want to store URL in my tables in 2008, I will be extracting the same data for report in excel, if i click on the URL it should take me to particular site, I hope to get solution for this.. Thank You in advance…..

  • Hi,

    I am Ramesh from Chennai, how many tables allowed in sql server 2008.

    Reply
    • It depends on the harddisk volume. Read about Maximum capacity specifications in SQL Server help file.

      Reply
  • Venkat Kilari
    June 13, 2012 3:40 pm

    Database objects include objects such as tables, views, stored procedures, user-defined functions, triggers, rules, defaults, and constraints. The sum of the number of all objects in a database cannot exceed 2,147,483,647.

    Reply
  • Awesome help as always. Why I don’t come here first every time, I have no idea.

    Reply
  • pinal sir you r the genius hats off to your site really!

    Reply
  • actually i had problem with default with alter you just solved it thanks sir again i say yu the best in the world for sql

    Reply
  • Hi Sir.

    How to add the default value for exsiting column

    Reply
  • hi sir,
    I need to copy the value of a primary key into another column of
    another table which is a foreign key how will i do it in sql server 2008 r2

    Reply
  • awsm

    Reply
  • Satyabrata Panda
    August 24, 2012 11:21 am

    Hi sir ,
    There are two table like User and comment and columns like
    (Userid,Conversation,click abuse ,commentid and category) for user table and (comment id,Comments,userid).when i insert some data through asp.net website i want to update these data of two tables in database.plz what is the code for sql database

    Reply
  • can we give reference of foreign key to primary key of table which is present in other schema table

    Reply
  • Hey

    can any one guide me on !!

    How to Drop bunch of tables from database ??

    Reply
  • Sushil Chhimpa
    October 6, 2012 9:10 am

    drop table tablename

    Reply
  • Sushil Chhimpa
    October 6, 2012 9:55 am

    How to use triggers

    Reply
  • HI I have a table test with primary and foreign key.and i created view with that table as vwtest.
    i can insert into the vwtest as it contain single table.
    how can i get primary,foreign keys into the views vwtest from the main table test

    Reply
  • I have what appears to be a painstakingly manual task. I need to find all the foreign keys in a DB however they are all logical. There are no constraints in the DB. Can you reference a set of thechniques to facilitate this?
    Thank you

    Reply
  • hello guys, i really need your help, i’m using sql server 2008 and in creating tables when i put foreign key the insert not accept but when there no foreign key the insert accept directly. what can i do to create that foreign key??there is another way to join tables in report without using foreign key??? please need your help i’m a beginner..thx

    Reply
  • manish srivastava
    February 14, 2013 4:06 pm

    hey friends…tell me how to use a foreign key between 2 tables in a single database when one table have primary key..??

    Reply
  • VIVEK KUMAR THAKUR
    February 26, 2013 7:37 pm

    Hi
    I am making a billling software and i can’t understand that how can i make data base for billing my structure is like this
    INVOICE NO,
    SLNO PRODUCTS PRICE QUANTITY TOTAL
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    GRANDTOTAL
    Problem is that if i sell one products then it working but if i sell 10item at a time
    i can,t understand how to create table for it please help me

    VIVEK KUMAR

    Reply

Leave a Reply

Menu