Create a one to many relationship using SQL Server

Question

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

User · Answer

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

User · Answer

Define two tables  example A and B   with their own primary key 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

User · Answer

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

User · Answer

If you are not using SSMS then here is the syntax   ALTER TABLE  lt table name gt  ADD  lt constraint name gt  FOREIGN KEY    lt column name1 gt     lt column name2 gt    REFERENCES  lt table name gt    lt column name1 gt     lt column name2 gt     http   infogoal com sql sql-add-foreignkey htm

User · Answer

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

[sql] Create a one to many relationship using SQL Server

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005