Create a one to many relationship using SQL Server

How do you create a one to many relationship using SQL Server?

  • SQL Server: rollback after insert that contains an output inserted fails mysteriously
  • table-join selection performance: get members from nested groups (large amount)
  • Subquery with multiple columns
  • How to create recurring calendar events?
  • What is the difference between square brackets and single quotes for aliasing in SQL Server?
  • Increment columns when more than one record in table
  • 5 Solutions collect form web for “Create a one to many relationship using SQL Server”

    1. Define two tables (example A and B), with their own primary key
    2. Define a column in Table A as having a Foreign key relationship based on the primary key of Table B

    This means that Table A can have one or more records relating to a single record in Table B.

    If you already have the tables in place, use the ALTER TABLE statement to create the foreign key constraint:

    ALTER TABLE A ADD CONSTRAINT fk_b FOREIGN KEY (b_id) references b(id) 
    
    • fk_b: Name of the foreign key constraint, must be unique to the database
    • b_id: Name of column in Table A you are creating the foreign key relationship on
    • b: Name of table, in this case b
    • id: Name of column in Table B

    This is a simple example of a classic Order example. Each Customer can have multiple Orders, and each Order can consist of multiple OrderLines.

    You create a relation by adding a foreign key column. Each Order record has a CustomerID in it, that points to the ID of the Customer. Similarly, each OrderLine has an OrderID value. This is how the database diagram looks:

    SQL one-to-many diagram http://www.subbot.net/personal/external/stackoverflow/sql-one-to-many.png

    In this diagram, there are actual foreign key constraints. They are optional, but they ensure integrity of your data. Also, they make the structure of your database clearer to anyone using it.

    I assume you know how to create the tables themselves. Then you just need to define the relationships between them. You can of course define constraints in T-SQL (as posted by several people), but they’re also easily added using the designer. Using SQL Management Studio, you can right-click the Order table, click Design (I think it may be called Edit under 2005). Then anywhere in the window that opens right-click and select Relationships.

    You will get another dialog, on the right there should be a grid view. One of the first lines reads “Tables and Columns Specification“. Click that line, then click again on the little […] button that appears on the right. You will get this dialog:

    Foreign key constraint http://www.subbot.net/personal/external/stackoverflow/sql-fk.png

    The Order table should already be selected on the right. Select the Customer table on the left dropdown. Then in the left grid, select the ID column. In the right grid, select the CustomerID column. Close the dialog, and the next. Press Ctrl+S to save.

    Having this constraint will ensure that no Order records can exist without an accompanying Customer record.

    To effectively query a database like this, you might want to read up on JOINs.

    This is how I usually do it (sql server).

    Create Table Master (
    MasterID int identity(1,1) primary key,
    Stuff varchar(10)
    )
    GO
    Create Table Detail (
    DetailID int identity(1,1) primary key,
    MasterID int references Master, --use 'references'
    Stuff varchar(10))
    GO
    Insert into Master values('value')
    --(1 row(s) affected)
    GO
    Insert into Detail values (1, 'Value1') -- Works
    --(1 row(s) affected)
    insert into Detail values (2, 'Value2') -- Fails
    --Msg 547, Level 16, State 0, Line 2
    --The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Detail__MasterID__0C70CFB4". 
    --The conflict occurred in database "Play", table "dbo.Master", column 'MasterID'.
    --The statement has been terminated.
    

    As you can see the second insert into the detail fails because of the foreign key.
    Here’s a good weblink that shows various syntax for defining FK during table creation or after.

    http://www.1keydata.com/sql/sql-foreign-key.html

    If you are talking about two kinds of enitities, say teachers and students, you would create two tables for each and a third one to store the relationship. This third table can have two columns, say teacherID and StudentId.
    If this is not what you are looking for, please elaborate your question.

    If you are not using SSMS then here is the syntax:

    ALTER TABLE <table_name>
    ADD <constraint_name> FOREIGN KEY 
    (<column_name1> ,
    <column_name2> )
    REFERENCES <table_name>
    (<column_name1> ,
    <column_name2>)
    

    http://infogoal.com/sql/sql-add-foreignkey.htm

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.