Create a One to Many Relationship Using SQL Server

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

How to create zero or one to many relationship using sql

You do this the same way that you implemented the one to zero or one relationship.

So far you have a foreign key in StudentAddress to Student. There is nothing enforcing one address per student. (You could do so with a unique index on `StudentAddress.StudentId'.)

So do the same thing. Have a StandardId field in the Student table, and you will have a many Student to zero or one Standard relationship.

How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?

One-to-one: Use a foreign key to the referenced table:

student: student_id, first_name, last_name, address_id
address: address_id, address, city, zipcode, student_id # you can have a
                                                        # "link back" if you need

You must also put a unique constraint on the foreign key column (addess.student_id) to prevent multiple rows in the child table (address) from relating to the same row in the referenced table (student).

One-to-many: Use a foreign key on the many side of the relationship linking back to the "one" side:

teachers: teacher_id, first_name, last_name # the "one" side
classes:  class_id, class_name, teacher_id  # the "many" side

Many-to-many: Use a junction table (example):

student: student_id, first_name, last_name
classes: class_id, name, teacher_id
student_classes: class_id, student_id     # the junction table

Example queries:

 -- Getting all students for a class:

    SELECT s.student_id, last_name
      FROM student_classes sc 
INNER JOIN students s ON s.student_id = sc.student_id
     WHERE sc.class_id = X

 -- Getting all classes for a student: 

    SELECT c.class_id, name
      FROM student_classes sc 
INNER JOIN classes c ON c.class_id = sc.class_id
     WHERE sc.student_id = Y

one to many relationship between 3 or more table

We have no idea bout your real requirement and the architecture. So, this is just a suggestion only to keep relation between image table.Your attached image little bit confused.

1. You can keep hotel and Restaurant both in one table by using a category [ flag the record as Restaurant or Hotel] . lets call this table as "Organizations"
   Then you can keep your image table without any issue as in below table.

2. If you need to keep separate tables for Hotels and Restaurants as you explained, then you have to add a Type column [img_type] to your Image table to categorize the image. Based on the category selected, you have to insert "hotel id" or "restaurant id" in to the "img_referance" field.

Note: Some hotels they have their own restaurants. If you need to manage those "Restaurant" then you have to introduce "Type column" to your "Restaurant" table as well. In that case, create a new field as "hotel id" in the restaurant table. (one hotel can have many restaurants).

How to implement One-To-Many relationship with the same table?

Your approach is the best approach. You want to represent an n-m relationship (one user has many friends and vice versa).

There are some considerations. If "friendship" is symmetric (that is A friends B automatically means that B friends A), then you probably want to include both in the table when one is inserted. You may also want to prevent a user from self-friending.

If you want to retrieve user's friends as a list, you can do so using a string concatenation function. In Standard SQL, this is:

select listagg(friendid, ',') within group (order by friendid) as friendids
from friends
where userid = XXX;

Different databases have different function names for listagg().

Create SQL one-to-many relationship with itself

You also need to make the two keys in your link table B, the primary key of this table.

Also, Linq2SQL seems to generate properties based on the names of your keys. This resulted in some odd names for me.

Sql Server one-to-many relationship in same table

You can add relationships with the primary key and foreign key in the same table as per any other relationship.

The following example would show you how to add a relationship between the (pk_col) PK and (fk_col) FK in the same table:

ALTER TABLE dbo.some_table
    ADD CONSTRAINT FK_some_table_some_table FOREIGN KEY
    (
        fk_col
    ) REFERENCES dbo.some_table
    (
        pk_col
    )

---

Related Topics