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.
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.
Hi Venkat,
ReplyDeleteYou 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 !.
Hello Venkat,
ReplyDeleteI 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!
Hello Vankat,
ReplyDeleteIs it necessary that the foreign key must be the primary key of another table?Can it be a Unique Key???
@sudarsan pradhan. foreign key always a primary key of any other table
Deleteforeign key is called as "foreign key" because it is the primary key in other table
DeleteA foreign key must reference either a primary key or unique key column.
DeleteAlso foreign key value must match an existing primary key value or unique key value, or else be null.
Not necessary it may be unique key also.
DeleteI heartally Thanks to you Sir .
ReplyDeleteYou and your videos total change my life....
I got very good knowledge and confidence with this Blog and Videos.
God bless you.......
Hi Venkat,
ReplyDeleteThanks 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.
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
Deletecreate table #t (
DeleteDBName sysname not null
)
go
exec sp_MSforeachdb 'use [?]; if OBJECT_ID(''dbo.mytable'') is not null insert into #t (DBName) select ''?'''
go
select * from #t
go
drop table #t
It returns Database names, where mytable contains in it.
you did great job. It is simple, people can understand easily, not complicated.
ReplyDeletehi venkat,
ReplyDeleteyou are a GEM,I am currently a Voice engineer but Now i want to learn a new technology and hence i started following you . I have just begun watching your .net videos .
your videos are so easy to understand ,the least that it can do is get a job to someone who wants to achieve their goals and behind their success is your utmost generosity of sharing these knowledgeable videos of yours.
thanks for sharing this.
Minhaj
Thank you so much for sharing all the knowledge and information. Did you publish a book I can buy?
ReplyDeleteI want to develop app using .net please prepare videos there is software name xamrain through which we can develop app
ReplyDeleteReally it is a great work.. Fully Organized.. Getting more opportunity after studying through this video.. Very Simple and Clear Explanation.. No One can be a better alternative for learning other than these videos
ReplyDeleteHi Venkat,
ReplyDeleteprimary key does not allow any null values, if any column is defined as primary key column then there is no need to define primary key on it, then why you have used not null in the create table statement?
Create Table tblGender
(ID int Not Null Primary Key,
Gender nvarchar(50))
it is optional
ReplyDeleteYou can create a table with primary column automatically not null will be applied on that column.
If you specify not null it will override primary key
Thank you for sharing your knowledge and yes your way of explanation is superb.
ReplyDeleteI recently started leaning SQL. By browsing youtube I found your video series. It is really nicely done and easy to follow. You are a very good man to spend your time and energy to create this wonderful video series.
ReplyDeletethank you for your excellent explanation Mr.venkat
ReplyDeleteI'm your biggest fan bro. You make all the concepts very clear and really love the way you explain.
ReplyDeleteThanks sir for all tutorial, you help lot to those who are come newly in some technology. Please provide some tutorial on big data science.
ReplyDeleteI want to explain why a foreign key might need to be null or might need to be unique or not unique. First remember a Foreign key simply requires that the value in that field must exist first in a different table (the parent table). That is all an FK is by definition. Null by definition is not a value. Null means that we do not yet know what the value is.
ReplyDeleteLet me give you a real life example. Suppose you have a database that stores sales proposals. Suppose further that each proposal only has one sales person assigned and one client. So your proposal table would have two foreign keys, one with the client ID and one with the sales rep ID. However, at the time the record is created, a sales rep is not always assigned (because no one is free to work on it yet), so the client ID is filled in but the sales rep ID might be null. In other words, usually you need the ability to have a null FK when you may not know its value at the time the data is entered, but you do know other values in the table that need to be entered. To allow nulls in an FK generally all you have to do is allow nulls on the field that has the FK. The null value is separate from the idea of it being an FK.
plz upload EMI calculations in stored procedures.. with #temp table
ReplyDeletePrimary key column cannot store a null value. So I believe the not null constraint is not required for the Primary key column.
ReplyDeleteI am refering to the following create statement in this video
Create Table tblGender
(ID int Not Null Primary Key,
Gender nvarchar(50))
Hi Sir,
ReplyDeleteHow many Primary keys and foreign keys per each table
One table can have only one primary key but it can have any number of foreign keys and unique constraints
DeleteGive foreign key name so by looking at name we can easly find the Primary key table name
ReplyDeletelike FK_tblPerson_GenderID_tblGender_ID
ALTER TABLE tblPerson ADD CONSTRAINT
FK_tblPerson_GenderID_tblGender_ID FOREIGN KEY
(GenderID) REFERENCES tblGender(ID)
Msg 547, Level 16, State 0, Line 62
ReplyDeleteThe INSERT statement conflicted with the FOREIGN KEY constraint "tblPersonGraph_GenderId_FK". The conflict occurred in database "SQLPRACTICE", table "dbo.tblGenderQuery", column 'ID'.
The statement has been terminated.
Msg 2627, Level 14, State 1, Line 67
Violation of PRIMARY KEY constraint 'PK__tblGende__3214EC2794FE64BD'. Cannot insert duplicate key in object 'dbo.tblGenderQuery'. The duplicate key value is (1).
The statement has been terminated.
Create Table tblGender(ID int not null Primary Key,Gender Varchar(50))
ReplyDeleteCreate Table tblPersons(ID int,Name Varchar(50),Email Varchar(50),GenderID int constraint tblPerson_GenderId_FK Foreign Key (GenderId) references tblGender(ID))