one primary key column foreign key to 2 other table columns.How to resolve data entry issue.

Question

I have a requirement according to which I have to create a central Login system.We have 2 things Corporate and Brand each represented by tables "Corporate" and "Brand".

When a corporate gets registered,corporateID is given,When a user under that corporate gets registered there is a table corporateuser in which corporateID is a foreign key and CorporateUserID is a primary key.Similarly in the case of a brand.

So we have CorporateUserId and BrandUserID.

Now i have a table called RegisteredUsers in which i want to have corporate as well as brand users.UserID is a primary key in this table which is a foreign key to both corporateuser as well as Branduser.

now when i enter a corporateuser,I do an entry to corporateuser as well as RegisteredUsers.When i enter CorporateUserID in userID for RegisteredUsers.It gives foreign key violation error.

I fully understand this error.How can i achieve this.This requirement is very rigid.Please tell a workaround

Answer 1

What you're trying to do is not totally clear, but it seems that you want the primary key of all three user tables to be the same. This is not a strict foreign key relationship, but it seems reasonable in your application.

You need to assign the userID in RegisteredUsers first, and use that key when you create your Corporate User or Brand User. Then the user id's will be unique across the whole system.

If that's not what you want, edit your entry with the table layouts to make the problem clearer.

Answer 2

If you are trying to insert records into tables with relational conatraints, you will need do all inserts under one SQL Transaction.