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

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)

,
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

  • rama chandra sahu
    August 29, 2011 3:38 pm

    Can’t we create a default constraint at table level ???

    Reply
  • 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….

    Reply
  • Hi madhivanan,

    we can create more than one primary key we can call it as composite key..

    Reply
    • It is a single primary key combined on many columns. You cannot create primary key on each column seperately.

      Reply
  • yosepkurniawijaya
    September 9, 2011 9:07 pm

    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

    Reply
  • 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?

    Reply
  • Your posts are really usefull

    Reply
  • Thanks for your post.

    Reply
  • Thank you, always seem to forget the exact syntax on many of those.

    Reply
  • how to insert a manual int value in primary key , sql server 2008

    Reply
  • Hi sir,
    My question is-
    In sql server authentication mode which is more secure authentication? (Windows or Sql server)

    Reply
    • 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 .

      Reply
  • Sharan Chakradhar
    February 3, 2012 7:37 pm

    How do I fetch MAC address in SQL server to implement in triggers.

    Reply
  • Dave, thanks for you feedback, because this examples are very good and help me so much.

    Thanks,

    JCSL.

    Reply
  • Great sir, thanks…

    Reply
  • How to generate a default Primary Key
    Background: There is a “Column Properties–Identity Specification” option with “Identy Increment” and “Identity Seed” in sqlsrv.

    Q: Is it possible to assign a default value for new records like “getYear() + get_SQLSRV_Global_Sequence_Number()” feature/trigger available?

    1) Example:

    a) Syntax
    (SELECT CONVERT( NUMERIC(4), (SELECT CONVERT(VARCHAR(4), GETDATE(), 112) AS [YYYY]))) + <SOME_GLOBAL_UNIQUE_SEED_FUNCTION()

    2) SQL example
    SELECT (SELECT CONVERT( VARCHAR(4), (SELECT CONVERT(VARCHAR(4), GETDATE(), 112) AS [YYYY]))) PK_PART1_YEAR, 1000000 as PK_PART2_SQLSRV_SEQUENCE

    3) Expected result

    Assigned primary key value: YEAR+CONCAT

    1) First value this year: 20121000001
    2) Second value this year: 20121000002
    3) N-value for year 2050: 20501000002

    Please refer or provide an sqlsrv example if possible…we would like to have a standard approach(dba’s, C# developers) regarding primary key assignment…

    -kurt

    Reply
  • Forgot to mention that
    a) external systems (PRODUCERS) post typically + + in a Varchar(50) column
    …and that
    b) i’m using an additional “uniqueidentifier” and “(newid())” in sqlsrv db for internal integrity.

    Reply
  • Hello sir,
    How to create recursive query in SQL Server?

    Reply
  • ur post was very nice and useful for me

    Reply
  • Hello sir?
    now i save the data in one table , that values also create a new columns into another table , if any possible to do automatically create a new columns

    Reply
  • sir,
    Am Jyothi. Is it posible to create a foreign with out primary and it is a unique key.

    Reply
  • Hello Sir,
    Can we drop foreign key which is not a constraint.. How to drop foreign key …

    Reply

Leave a Reply Cancel reply

Exit mobile version