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
AGO

3)      Alter Table to Drop Default Constraint

ALTER TABLE Customer
DROP CONSTRAINT df_customer_Add
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

120 thoughts on “SQL SERVER – 2008 – Creating Primary Key, Foreign Key and Default Constraint

  1. 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 ?

    Like

  2. Pingback: SQL SERVER - Guidelines and Coding Standards Part - 1 Journey to SQL Authority with Pinal Dave

    • 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

      Like

    • 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

      Like

  3. 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?

    Like

    • 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

      Like

  4. 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

    Like

  5. 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’.

    Like

  6. 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

    Like

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

    Like

  8. 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

    Like

  9. 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

    Like

  10. 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..!

    Like

    • 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

      Like

  11. I’ve a problem in sql server just could you tel me ?
    there are four tables in my database and there are two primary keys in two different table I need to filter data s by using those primary keys when I prepare reports, but in the beginning I’ve given only 1 primary key as foreign key to other tables

    My question is can I insert in this stage the other particular primary key as foreign key to other tables?

    its very argent ! I know you can help me

    Like

  12. Hello Mr. Stephen,

    You can insert the other primary key as foreign key to other tables provided you have the data in the tables that do not conflict with the combination keys (existing primary key and the new foreign key). In that case, you will have to make the corrections in the data already existing in the table and then insert the keys.

    Also, after making changes in the design of the table, do not forget to refresh the data in reports.

    Kind Regards,
    Pinal Dave

    Like

  13. sir
    please tell me how can i create table in sql server.what process of start sql and then reached the table sheet.and also create contrant primary key foreign key .

    Like

  14. Hello Mr.Pinal Dave,

    I read your post regarding Constraints in Sql.Its very useful & Easy to understand.

    As well,Short and Sweet.

    Thanks a lot..

    Regards
    Anbu with Love

    Like

  15. I am a newbee to SQL and I am working on a metrics table. I have the SQL 2008 Express on my computer.

    The name of the database is PlantMetrics and the name of the table is dbo.PerformanceIndexes.

    I have three columns containing:
    PlantName
    Month
    Year

    The combination of values in these three columns is unique. When I post the metrics for each plant for each month, the values of PlantName, Month and Year will repeat from record to record.

    Can I denote these three columns as primary keys? If so how?
    Or do I leave the table without primary key constraint?

    I want to add other tables to the database with details of other characteristics for the plants and relate them to this table.
    The other tables will have unique values for the PlantName etc.

    Like

  16. hi sir,

    i am vinoth. we are using for sql server 2008. i want display

    recorded data… for example when i entered in office using

    my id card that time..the product software recorded…

    already recorded and new record data i want to display

    screen with using sql server 2008.

    which command we have to use…. please give me solution..

    Thanks & Regards,
    vinoth.A

    Like

  17. Hi Pinal Sir,
    Thanks for your tutorial about adding primary key and foreign key using alter statement at column level and table level.
    Anshu

    Like

  18. Hi Pinal Sir,
    In the scenario of foreign key and primary key both are providing the same feature but what is the actual difference between column level and table level ?

    Like

  19. CREATE table Customers
    (CustomersID char(5) not null,
    Address varchar (60) null,
    City char(15) null,
    Phone char (24)null,
    Fax char (24) null,
    constraint pk_Customers primary key (CustomerID)
    )

    CREATE table Orders
    (OrdersID int,
    CustomerID char not null,
    Orderdate datetime null,
    Shippeddate datetime null,
    Quantity int null,
    constraint pk_Orders primary key (OrdersID, CustomerID)
    )

    Like

  20. Hi Pinal
    I create salespeople table
    create table salespeople(snum int CONSTRAINT pk_snum PRIMARY KEY,sname varchar(50),city varchar(50),comm decimal(12))
    i inserted data like this
    insert into salespeople values(1002,’bama’,’bangalore’,.18)
    insert into salespeople values(1003,’kama’,’Bobbili’,.15)
    insert into salespeople values(1004,’anjuri’,’vizag’,.14)
    ———————-
    then i create customer table
    create table customer(cnum int INT CONSTRAINT pk_productSales_sid PRIMARY KEY,
    Cname varchar(50),
    city varchar(50),rating int ,
    snum int CONSTRAINT fk_snum_pid FOREIGN KEY REFERENCES salespeople(snum))
    inser values
    insert into customer values(200,’jayanth’,’bangalore’,100,1004)
    insert into customer values(2005,’bhagyasri’,’guntur’,200,1006)
    —————-
    i create order table
    create table orders(onum int CONSTRAINT pk_productSales_sid PRIMARY KEY,
    amount decimal(10),Orderdate datetime,
    snum int CONSTRAINT fk_snum2_pid FOREIGN KEY REFERENCES salespeople(snum),
    cnum int CONSTRAINT fk_cnum_pid FOREIGN KEY REFERENCES customer(cnum))

    i got the error
    Msg 2714, Level 16, State 4, Line 1
    There is already an object named ‘fk_snum_pid’ in the database.
    Msg 1750, Level 16, State 0, Line 1
    Could not create constraint. See previous errors.

    How to solve those errors
    plz send details urgent pinla

    Like

  21. Hi Pinal Sir,
    I hava a problem in creating foreign key in my sql table.im using sql 2008.
    Can i set two foreign keys in two child tables with references to one unique key in main table .
    pleas help me.

    Like

    • try to chage your main table unique key bcoz unique key access from the null values so try to use primary key then u il set the foreign key ur table

      Like

  22. dear sir,
    i have a table called student_ id with 3 column id(pkey),name and course.

    i wants to access id (start with 3 and end with 8,means 5 values)
    hw can this is possible with asp.net application

    Like

  23. can anyone help me!!!
    when ever i create identity primary key tat error showing all the times

    “Must declare the scalar variable “@”ID”

    Like

  24. 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….

    Like

  25. 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?

    Like

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

      Like

  26. Pingback: SQL SERVER – Various Ways to Create Constraints – Quiz – Puzzle – 17 of 31 « SQL Server Journey with SQL Authority

  27. 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

    Like

  28. 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.

    Like

  29. 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

    Like

  30. 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

    Like

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

        Like

  31. 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.

    Like

  32. 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

    Like

  33. 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

    Like

  34. 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

    Like

  35. 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

    Like

  36. 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

    Like

  37. 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

    Like

  38. 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

    Like

  39. 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.

    Like

    • 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’)

      Like

  40. Pingback: SQL SERVER – Weekly Series – Memory Lane – #046 | Journey to SQL Authority with Pinal Dave

  41. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s