Scribd Logo
Upload

Welcome to Scribd!

  • 69 views

    SQL 3

    Uploaded by

    jhansi rani
    The document discusses creating and establishing relationships between tables in SQL Server. It describes creating a tblPerson table with columns like ID and GenderID. A tblGender table is also created with an ID column as the primary key and a Gender column. A foreign key constraint is added to link the GenderID column in tblPerson to the ID column in tblGender, to enforce referential integrity. This can be done graphically in SQL Server Management Studio or by using ALTER TABLE queries.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd

    SQL 3

    Uploaded by

    jhansi rani
    69 views8 pages
    The document discusses creating and establishing relationships between tables in SQL Server. It describes creating a tblPerson table with columns like ID and GenderID. A tblGender table is also created with an ID column as the primary key and a Gender column. A foreign key constraint is added to link the GenderID column in tblPerson to the ID column in tblGender, to enforce referential integrity. This can be done graphically in SQL Server Management Studio or by using ALTER TABLE queries.

    Original Title

    sql3

    Copyright

    © © All Rights Reserved

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document discusses creating and establishing relationships between tables in SQL Server. It describes creating a tblPerson table with columns like ID and GenderID. A tblGender table is also created with an ID column as the primary key and a Gender column. A foreign key constraint is added to link the GenderID column in tblPerson to the ID column in tblGender, to enforce referential integrity. This can be done graphically in SQL Server Management Studio or by using ALTER TABLE queries.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    69 views8 pages

    SQL 3

    Uploaded by

    jhansi rani
    The document discusses creating and establishing relationships between tables in SQL Server. It describes creating a tblPerson table with columns like ID and GenderID. A tblGender table is also created with an ID column as the primary key and a Gender column. A foreign key constraint is added to link the GenderID column in tblPerson to the ID column in tblGender, to enforce referential integrity. This can be done graphically in SQL Server Management Studio or by using ALTER TABLE queries.

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    of 8

    Sql server, .

    net and c# video tutorial


    Free C#, .Net and Sql server video tutorial for beginners and intermediate
    programmers.

    Support us .Net Basics C# SQL ASP.NET Aarvi MVC Slides C# Programs


    Subscribe Download
    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.
    Email This
    BlogThis!
    Share to Twitter
    Share to Facebook
    Share to Pinterest
    27 comments:

    NandaNovember 20, 2013 at 6:02 PM


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

    Reply

    MariaFebruary 7, 2014 at 4:19 AM


    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!

    Reply

    UnknownMay 1, 2014 at 11:15 AM


    Hello Vankat,

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

    Reply
    Replies

    AbdulMay 13, 2014 at 12:34 AM


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

    KudosTechOctober 5, 2016 at 11:57 AM


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

    Saudagar JadhavMay 17, 2017 at 3:28 AM


    A foreign key must reference either a primary key or unique key column.
    Also foreign key value must match an existing primary key value or unique key
    value, or else be null.
    PrashanthSeptember 27, 2017 at 10:06 AM
    Not necessary it may be unique key also.

    Reply

    UnknownFebruary 27, 2015 at 4:12 AM


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

    Reply

    UnknownSeptember 13, 2015 at 9:38 PM


    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.

    Reply
    Replies

    NaveedJune 29, 2016 at 11:14 AM


    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

    PrashanthSeptember 27, 2017 at 10:19 AM


    create table #t (
    DBName 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.

    Reply

    AnonymousApril 21, 2016 at 3:49 AM


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

    Reply
    UnknownDecember 27, 2016 at 3:34 PM
    hi venkat,
    you 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

    Reply

    UnknownJanuary 23, 2017 at 9:47 AM


    Thank you so much for sharing all the knowledge and information. Did you publish a
    book I can buy?

    Reply

    AnonymousFebruary 6, 2017 at 8:26 AM


    I want to develop app using .net please prepare videos there is software name
    xamrain through which we can develop app

    Reply

    AnonymousFebruary 7, 2017 at 12:48 AM


    Really 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

    Reply

    AnonymousMarch 23, 2017 at 1:29 AM


    Hi Venkat,
    primary 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))

    Reply

    UnknownApril 26, 2017 at 1:28 AM


    it is optional
    You 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

    Reply

    UnknownJuly 21, 2017 at 7:15 AM


    Thank you for sharing your knowledge and yes your way of explanation is superb.

    Reply

    UnknownOctober 15, 2017 at 8:14 AM


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

    Reply

    cse cool stuffOctober 16, 2017 at 6:40 AM


    thank you for your excellent explanation Mr.venkat

    Reply

    UnknownDecember 12, 2017 at 3:08 AM


    I'm your biggest fan bro. You make all the concepts very clear and really love the
    way you explain.

    Reply

    UnknownFebruary 1, 2018 at 1:47 AM


    Thanks sir for all tutorial, you help lot to those who are come newly in some
    technology. Please provide some tutorial on big data science.

    Reply

    UnknownMarch 27, 2018 at 1:49 PM


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

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

    Reply

    seetharamNovember 15, 2018 at 2:48 AM


    plz upload EMI calculations in stored procedures.. with #temp table

    Reply

    RevathiJune 29, 2019 at 3:17 PM


    Primary key column cannot store a null value. So I believe the not null constraint
    is not required for the Primary key column.
    I am refering to the following create statement in this video

    Create Table tblGender


    (ID int Not Null Primary Key,
    Gender nvarchar(50))
    Reply

    TrinathDecember 26, 2019 at 2:18 AM


    Hi Sir,

    How many Primary keys and foreign keys per each table

    Reply

    It would be great if you can help share these free resources

    Newer PostOlder PostHome


    Subscribe to: Post Comments (Atom)
    Pragim Technologies - Best software training and placements in marathahalli,
    bangalore. For further details please call 09945699393.

    Complete Tutorials
    How to become a full stack web developer

    Cloud computing complete tutorial

    Healthy food for healthy mind and body

    JavaScript tutorial

    Bootstrap tutorial

    Angular tutorial for beginners

    Angular 5 Tutorial for beginners

    Important Videos
    The Gift of Education

    Web application for your business

    How to become .NET developer

    Resources available to help you

    Dot Net Video Tutorials


    Blazor tutorial

    C tutorial

    ASP.NET Core Tutorial

    ASP.NET Core Razor Pages Tutorial

    Angular 6 Tutorial

    Angular CRUD Tutorial

    Angular CLI Tutorial

    Angular 2 Tutorial

    Design Patterns
    SOLID Principles

    ASP.NET Web API

    Bootstrap

    AngularJS Tutorial

    jQuery Tutorial

    JavaScript with ASP.NET Tutorial

    JavaScript Tutorial

    Charts Tutorial

    LINQ

    LINQ to SQL

    LINQ to XML

    Entity Framework

    WCF

    ASP.NET Web Services

    Dot Net Basics

    C#

    SQL Server

    ADO.NET

    ASP.NET

    GridView

    ASP.NET MVC

    Visual Studio Tips and Tricks

    Dot Net Interview Questions

    Slides
    Entity Framework

    WCF

    ASP.NET Web Services

    Dot Net Basics

    C#

    SQL Server
    ADO.NET

    ASP.NET

    GridView

    ASP.NET MVC

    Visual Studio Tips and Tricks

    Java Video Tutorials


    Part 1 : Video | Text | Slides

    Part 2 : Video | Text | Slides

    Part 3 : Video | Text | Slides

    Interview Questions
    C#

    SQL Server

    Written Test
    Powered by Blogger.

    You might also like