CODEWITHSUNDEEP
sqldatabasedatabase-design
CloudAnywhere
CloudAnywhere

Reputation: 759

SQL Foreign key issue with 2 parent tables

I have have 2 tables User and Group. I have a table Attributes shared by user and group with columns:

ObjectID points to either the primary key of user or the primary key of Group. I have added a foreign constraint with Cascade on Delete in order to delete automatically the attributes when user or a group is deleted.

The problem now is when I insert an attribute for the user, I have a foreign key constraint because the group does not exist.

How should I proceed?

Upvotes: 1

Views: 2196

Answers (3)

Branko Dimitrijevic
Branko Dimitrijevic

Reputation: 52107

You have basically 3 options:

  1. Keep your current design, but replace Attribute.ObjectID with UserID and GroupID, attach a separate FK to each of them (one towards Group and the other towards User) and allow either to be NULL. You'd also want a CHECK constraint to ensure not both of them are NULL.

    enter image description here

  2. Split Attribute table to UserAttribute and GroupAttribute, thus separating each foreign key into its own table.

    enter image description here

  3. Use inheritance, like this:

    enter image description here

The solution (1) is highly dependent on how your DBMS handles UNIQUE on NULLs and both (1) and (2) allow the same AttributeName to be used for two different attributes, one for user an the other for group.

Upvotes: 3

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

As you have discovered you can not have one column as foreign key to two different tables. You can't add a attribute for a user when it does not exist a group with the same id. And you can of course not know if the attribute is for a user or a group.

From comments you also mentioned a m:m relation between user and group so I would suggest the following.

create table [User]
(
  UserID int identity primary key,
  Name varchar(50) not null
)

go

create table [Group]
(
  GroupID int identity primary key,
  Name varchar(50) not null
)

go

create table UserGroup
(
  UserID int not null references [User](UserID),
  GroupID int not null references [Group](GroupID),
  primary key (UserID, GroupID)
)

go

create table UserAttribute
(
  UserAttributeID int identity primary key,
  Name varchar(50) not null,
  Value varchar(50) not null,
  UserID int not null references [User](UserID) on delete cascade
)

go

create table GroupAttribute
(
  GroupAttributeID int identity primary key,
  Name varchar(50) not null,
  Value varchar(50) not null,
  GroupID int not null references [Group](GroupID) on delete cascade
)

Note: The use of an attribute table should be for attributes you don't know before hand. All the stuff you know will be attributes should be fields in the actual table instead. Reserve the use of the attributes for customer defined attributes.

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79929

I think you should allow NULL values for this foreign key field ObjectId, so that you can insert any row with ObjectId = null that not referencing any user or group.

For a better design you should remove this ObjectId column, add a new column AttributeId to the two tables User and Group.

Upvotes: 0

Related Questions