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

  • WHAT IS THE DIFFERENCE OF GIVING PRIMARY KEY AND FOREIGN KEY CONSTRAINT AT COLUMN LEVEL AND TABLE LEVEL….WE CAN PROVIDE DIRECTLY AT A TABLE LEVEL ITSELF RIGHT

    Reply
  • helo sir,
    how to insert values in a table that contains a foreign key

    Reply
  • HI
    CAN YOU DEFINE SCHEMAS AND DEFAULT CONSTRAINT

    Reply
  • can constraint check the validity of ddl operation??

    Reply
  • Fantastic Post.

    Reply
  • Hi

    can anybody help me to know the diffrence between database developer, database programmer and data analyst. which things i have to prepare for these profiles. because i am confused in it. please reply ASAP.

    Reply
  • i wanna to know with simple example how to apply foreign key in tables

    Reply
    • create table tb
      (
      id int constraint pk primary key,
      name varchar(20)
      )
      select * from tb
      insert into tb values(1,’pari’)
      insert into tb values(2,’mansi’)
      insert into tb values(3,’mansi’)

      create table mb
      (
      id int constraint fk foreign key references tb(id),
      address varchar(20)
      )

      select * from mb
      insert into mb values(1,’sonipat’)
      insert into mb values(2,’panipat’)

      Reply
  • hi i am using vb6 with sql server 2008. i have table named address given the query below:
    Create Table address
    (
    [apartment_name][varchar](50) NULL,
    [door_number][varchar](50) NULL,
    [street_name_1][varchar](50) NULL,
    [street_name_2][varchar](50) NULL,
    [street_name_3][varchar](50) NULL,
    [village][varchar](50) NULL,
    [city][varchar](50) NULL,
    [state][varchar](50) NULL,
    [country][varchar](50) NULL,
    [row_upd_date][date/time] NULL,
    [apartment_number][varchar](50) NULL,
    [agn][int]IDENTITY(1,1) NOT NULL,
    [status][bit] NULL,
    [pincode][varchar](50) NULL,
    [user_id][int] NULL,
    )
    now that i have to create a field name supplier_id of type integer which links to tables named supplier_contact and Supplier_id and Supplier_name. How make supplier_id as foreign in table named address? Reply please

    Reply
  • Hey Pinal,

    Can I add two master tables (A & B) primary key as foreign key into Child table(C) and both master primary key refer child single or same column ?

    Like :
    A has primary key A_Id
    B has primary key B_Id

    Can i do this ?
    C has column C_Ref_Id and refer both A_Id and B_Id

    Please suggest, If we can’t do this then why can we not do it.

    Thanks in advance. :)

    Regards,
    Manoj

    Reply
    • Yes.

      Create database MyDB
      go
      USE [MyDB]
      GO
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE TABLE [dbo].[t1](
      [c1] [nchar](10) NOT NULL,
      [t1] [nchar](10) NULL,
      CONSTRAINT [PK_t1] PRIMARY KEY CLUSTERED
      (
      [c1] ASC
      ))

      GO

      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE TABLE [dbo].[t2](
      [c1] [nchar](10) NOT NULL,
      [t2] [nchar](10) NULL,
      CONSTRAINT [PK_t2] PRIMARY KEY CLUSTERED
      (
      [c1] ASC
      ))

      GO
      SET ANSI_NULLS ON
      GO
      SET QUOTED_IDENTIFIER ON
      GO
      CREATE TABLE [dbo].[t3](
      [c1] [nchar](10) NULL,
      [t3] [nchar](10) NULL
      ) ON [PRIMARY]

      GO
      ALTER TABLE [dbo].[t3] WITH CHECK ADD CONSTRAINT [FK_t3_t1] FOREIGN KEY([c1])
      REFERENCES [dbo].[t1] ([c1])
      GO
      ALTER TABLE [dbo].[t3] CHECK CONSTRAINT [FK_t3_t1]
      GO
      ALTER TABLE [dbo].[t3] WITH CHECK ADD CONSTRAINT [FK_t3_t2] FOREIGN KEY([c1])
      REFERENCES [dbo].[t2] ([c1])
      GO
      ALTER TABLE [dbo].[t3] CHECK CONSTRAINT [FK_t3_t2]
      GO

      Reply
  • can any one reply how many columns can include for single primary key combination like
    (column a, column b, column c…….??? )

    Reply
  • what is the difference of table level constraint and column level constraint and its use?????

    Reply
  • | Criteria: (7) Add column to EMPLOYEES (0 out of 16 points).
    -missing DDL SQL to alter table to add field to an existing table. (hint: ALTER table).

    Write a script to add the following column to the EMPLOYEES table:

    Column Name Datatype Primary Key (Y/N) Nullable (Y/N)
    DepartmentID Integer N N

    Reply
  • hell sir,
    i am new to sql and i am trying to create a table using the syntax below: and i found your site to be helpful

    CREATE TABLE Bk_Location
    (
    Location_ID Int(20),
    Country_ID Int (20)
    Street_Address Varchar (255),
    Postal_Code Varchar (255),
    City Varchar (255),
    State_Province Varchar (255),
    );

    i want Location id to be the primary key and country id to be the foreign key. i have tried some codes but it is giving me errors.

    i agent need

    Reply
  • INSERT INTO new_table (Foo, Bar, Fizz, Buzz)
    SELECT Foo, Bar, Fizz, Buzz
    FROM initial_table
    — optionally WHERE …

    Reply

Leave a Reply

Menu