SQL Server - create relationship - Asked By Aldo Lin Lin on 28-Oct-12 06:44 PM

I have 2 tables .  One is a parent table and one is a child table.  How do I create a foreign key relationship between these two tables? 

See these steps to Create Relationship - Sagar P replied to Aldo Lin Lin on 12-Aug-08 04:44 AM

Its easy to create relationship in sql server 2005 with the help of DataBase Diagram.

See these steps;

Constraint
Constraint is a check which is made on table which must be followed by user entering data. It reduce the number of Invalid data feed-in into system by prechecking its validity.

In this table 2 constraint are used that is "Allow Null" and "Primary Key"

  • Allow Null Constraint - If checked than will Allow Null values in field, else it will not allow you to bypass the column without entering data.
  • Primary Key Constraint - It checks that each record in table is unique and not null. http://dotnetguts.blogspot.com/2007/06/basic-dbms-concept.html


Creating Primary Key on Column of SQL Server 2005 Right click the column field and set the field as Primary Key

http://bp3.blogger.com/_nuQwSyDoLk8/RwfvnZahXjI/AAAAAAAAAPA/h7ERvxnTDMg/s1600-h/SQL-Server-2005-7.jpg

Now in similar fashion create Order Table, OrderDetails Table, Product Table, ProductType Table.

Order Table
http://bp3.blogger.com/_nuQwSyDoLk8/RwfxdZahXnI/AAAAAAAAAPg/tLNXaIMdGO8/s1600-h/SQL-Server-2005-8.jpg

OrderDetails Table
http://bp2.blogger.com/_nuQwSyDoLk8/RwfxLJahXmI/AAAAAAAAAPY/ZUqUnWpuFHs/s1600-h/SQL-Server-2005-9.jpg

Product Table
http://bp2.blogger.com/_nuQwSyDoLk8/Rwfw7JahXlI/AAAAAAAAAPQ/aZEtSvhJZQE/s1600-h/SQL-Server-2005-10.jpg

ProductType Table
http://bp3.blogger.com/_nuQwSyDoLk8/Rwfw2ZahXkI/AAAAAAAAAPI/z_flA5wYY8s/s1600-h/SQL-Server-2005-11.jpg

so your object explorer looks as under

http://bp3.blogger.com/_nuQwSyDoLk8/RwfyFZahXoI/AAAAAAAAAPo/ZUe66_svMHI/s1600-h/SQL-Server-2005-12.jpg



Creating Database Diagram in SQL Server 2005

http://bp0.blogger.com/_nuQwSyDoLk8/RwfyrpahXpI/AAAAAAAAAPw/7FkiketjcXY/s1600-h/SQL-Server-2005-13.jpg


Next step select Table and press Add button

http://bp2.blogger.com/_nuQwSyDoLk8/RwfzBJahXqI/AAAAAAAAAP4/HAmnPQFcOSs/s1600-h/SQL-Server-2005-14.jpg


Adding a releationship among tables

Right click the "Many" Table as They don't allow to change foreign key table in Releationship dialog.

Example: Click on Orders Table, as 1 Customer can place Many Orders

http://bp1.blogger.com/_nuQwSyDoLk8/RwjsmIrTfjI/AAAAAAAAAQA/PsF-xrpoMG4/s1600-h/SQL-Server-2005-15.jpg


Foreign Key Relationship dialog box will appear, press add button and you will find details appear on right pane of dialog box.

http://bp0.blogger.com/_nuQwSyDoLk8/RwjtG4rTfkI/AAAAAAAAAQI/1fr_tm7ZunU/s1600-h/SQL-Server-2005-16.jpg

Now click the open dialog button on right of "Table And Columns Specification" from right pane of dialog box.

For creating relationship between Orders and Customer, CustomerID is used. Select Table "Customer" for Primary Key Table and table "Orders" for Foreign Key.

http://bp0.blogger.com/_nuQwSyDoLk8/Rwjt-4rTflI/AAAAAAAAAQQ/N8pYbUQ6Dzs/s1600-h/SQL-Server-2005-17.jpg

And press ok button to create relationship between two table.

Advantage of creating relationship

  • It will not allow to enter orders details without customer information. That is it allow to enter orders details of valid customer.
  • It will not allow to delete customer information, until all details associated with customer is delete.
  • You may change setting as per your need by selecting No from dropdown of "Enforce for Replication" and "Enforce foreign key constraint"

Similarly you need to create relationship between remaining tables.

  • 1 Customer can place Many Orders
  • 1 Order can have Many OrderDetails
  • 1 Product can be in Many OrderDetails
  • 1 ProductType consist of Many Product

At the end Relationship diagram looks as under

http://bp3.blogger.com/_nuQwSyDoLk8/RwjxXorTfmI/AAAAAAAAAQY/2tS1YQX-cEc/s1600-h/SQL-Server-2005-18.jpg

When you are dealing with large database with 100's of table, you can change the view of table by selecting all table with "ctrl+A" and right clicking any of selected table and change its views so that it is convinent to view.

http://bp3.blogger.com/_nuQwSyDoLk8/RwjyPorTfnI/AAAAAAAAAQg/zYJF04vGNrM/s1600-h/SQL-Server-2005-19.jpg

A sample diagram will now look as under

http://bp0.blogger.com/_nuQwSyDoLk8/Rwjyq4rTfoI/AAAAAAAAAQo/_u9sa_V-TJA/s1600-h/SQL-Server-2005-20.jpg

Best Luck!!!!!!!!!!!!!!!!!!!!
Sujit.

create relationship - Sakshi a replied to Aldo Lin Lin on 12-Aug-08 04:54 AM

Foreign key of what primary key.

A foreign key should refer a Primary key.

Make your 2 tables columns as references to that table from which you make ur key foreign.


See this - san san replied to Aldo Lin Lin on 12-Aug-08 05:23 AM

Hi

A foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.

For example, say we have two tables, a CUSTOMER table that includes all customer data, and an ORDERS table that includes all customer orders. The constraint here is that all orders must be associated with a customer that is already in the CUSTOMER table. In this case, we will place a foreign key on the ORDERS table and have it relate to the primary key of the CUSTOMER table. This way, we can ensure that all orders in the ORDERS table are related to a customer in the CUSTOMER table. In other words, the ORDERS table cannot contain information on a customer that is not in the CUSTOMER table.

The structure of these two tables will be as follows:

Table CUSTOMER

column name characteristic
SID Primary Key
Last_Name  
First_Name  

Table ORDERS

column name characteristic
Order_ID Primary Key
Order_Date  
Customer_SID Foreign Key
Amount  

In the above example, the Customer_SID column in the ORDERS table is a foreign key pointing to the SID column in the CUSTOMER table.

Below we show examples of how to specify the foreign key when creating the ORDERS table:

Oracle:
CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date date,
Customer_SID integer references CUSTOMER(SID),
Amount double);

SQL Server:
CREATE TABLE ORDERS
(Order_ID integer primary key,
Order_Date datetime,
Customer_SID integer references CUSTOMER(SID),
Amount double);

Below are examples for specifying a foreign key by altering a table. This assumes that the ORDERS table has been created, and the foreign key has not yet been put in:

Oracle:
ALTER TABLE ORDERS
ADD (CONSTRAINT fk_orders1) FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);

SQL Server:
ALTER TABLE ORDERS
ADD FOREIGN KEY (customer_sid) REFERENCES CUSTOMER(SID);


Hope this helps
SAN
Use this... - Atul Shinde replied to Aldo Lin Lin on 12-Aug-08 05:40 AM
First select first table and create Primary key. For second table create column of same name (name can be differ but easy to use) and right click on that. Click on relationship. After that click Add. Select Primary Key Table and Column then Select Foreign Key Table and Column.