Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

Creating and Working with tables - Part 3

The aim of this article is to create tblPerson and tblGender tables and establish primary key and foreign key constraints. In SQL Server, tables can be created graphically using SQL Server Management Studio (SSMS) or using a query.








To create tblPerson table, graphically, using SQL Server Management Studio
1. Right click on Tables folder in Object explorer window
2. Select New Table
3. Fill Column Name, Data Type and Allow Nulls, as shown below and save the table as tblPerson.



The following statement creates tblGender table, with ID and Gender columns. The following statement creates tblGender table, with ID and Gender columns. ID column, is the primary key column. The primary key is used to uniquely identify each row in a table. Primary key does not allow nulls.
Create Table tblGender
(ID int Not Null Primary Key,
Gender nvarchar(50))


In tblPerson table, GenderID is the foreign key referencing ID column in tblGender table. Foreign key references can be added graphically using SSMS or using a query.


To graphically add a foreign key reference
1. Right click tblPerson table and select Design
2. In the table design window, right click on GenderId column and select Relationships
3. In the Foreign Key Relationships window, click Add button
4. Now expand, in Tables and Column Specification row, by clicking the, + sign
5. Click on the elipses button, that is present in Tables and Column Specification row
6. From the Primary Key Table, dropdownlist, select tblGender
7. Click on the row below, and select ID column
8. From the column on the right hand side, select GenderId
9. Click OK and then click close.
10. Finally save the table.


To add a foreign key reference using a query
Alter table tblPerson 
add constraint tblPerson_GenderId_FK FOREIGN KEY (GenderId) references tblGender(ID)


The general formula is here
Alter table ForeignKeyTable add constraint ForeignKeyTable_ForiegnKeyColumn_FK 
FOREIGN KEY (ForiegnKeyColumn) references PrimaryKeyTable (PrimaryKeyColumn)


Foreign keys are used to enforce database integrity. In layman's terms, A foreign key in one table points to a primary key in another table. The foreign key constraint prevents invalid data form being inserted into the foreign key column. The values that you enter into the foreign key column, has to be one of the values contained in the table it points to.

9 comments:

  1. Hi Venkat,

    You did a great work. We can see n number of videos and articles of many technology in internet .but many of them are scattered . You did it in a systematic way so that people can find it easy and read and UNDERSTAND EASY. Well Done !.

    ReplyDelete
  2. Hello Venkat,

    I am an absolute beginner trying to learn SQL Server and together with the book I bought your videos on you tube are a great assistance for me. Great work, very well organized! Excellent! Thank you!

    ReplyDelete
  3. Hello Vankat,

    Is it necessary that the foreign key must be the primary key of another table?Can it be a Unique Key???

    ReplyDelete
    Replies
    1. @sudarsan pradhan. foreign key always a primary key of any other table

      Delete
    2. foreign key is called as "foreign key" because it is the primary key in other table

      Delete
  4. I heartally Thanks to you Sir .
    You and your videos total change my life....
    I got very good knowledge and confidence with this Blog and Videos.
    God bless you.......

    ReplyDelete
  5. Hi Venkat,

    Thanks for such a good video.
    I have one question to ask,
    I create a table in sql and execute it by mistake it goes in some other database how can i fine in which database it goes?
    like in this video you create table gender and it goes to master database here you check Available Database option, in case you create table gender and it goes to master database or some other database and you didn't check that time and close the sql server and after few days when you check your database and didn't find the table in database, now how you check in which database it goes.
    you are going to check manually each database?

    Thanks in advance.

    ReplyDelete
    Replies
    1. It is not possible that a table can go any where, when some one create any table then every one first of all check that where i am creating the table, if he does not do that then he must check all the databases one by one, because there is no other alternative solution for that

      Delete
  6. you did great job. It is simple, people can understand easily, not complicated.

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.