CODEWITHSUNDEEP
sqlsql-serverone-to-many
demo
demo

Reputation: 6235

One-to-many relationship without column that represent "one"

I have 2 tables: Group, Person and Address.

Group:

Id| Name| ...

Person:

Id| FirstName| LastName|...

Address:

Id| Address1|Address2|...

Table Address should contains multiple records from Group or from Person. In general I would like to create relationship one(Group)-to-many(Address), but I don't want to add column GroupId into table Address.

How can I create this connection?

Would it be like many-to-many structure? When I need to create additional table GroupAddress, but just add constraint for unique id from Group table?

Upvotes: 0

Views: 948

Answers (1)

Shmiel
Shmiel

Reputation: 1251

As allmhuran suggested in the comments. You can create a new table with a foreign key to both tables, but you should add a unique constraint on the address, so it should enforce a one-to-many relationship, and not allow many-to-many.

There are two ways you can do it, one:

create table [schema].GroupAddress
(
    GroupAddressId int not null identity primary key,
    GroupId <datatype> <foreign_key_constraint_name> foreign key references Group(id),
    AddressId <datatype> <foreign_key_constraint_name> foreign key references Address(id) <unique_constraint_name> unique
)

Or you can make the AddressId column as the primary key, and that will become a unique column, but you shouldn't make it as identity because that will create issues inserting on the identity column.

I would rather go with the first one (creating three columns), because it's generally a good thing to have a separate id column on each table.

The reason I say you can create a new table, is because the best way to do it, is by creating a new column on the address table.

Upvotes: 0

Related Questions