1

There are 3 tables namely FN, ADM, USR representing a function, admin for the function, users who attend the function. Admin will be a user and ADM has to be inherited from the USR.
FN_I,USR_I should be the prim key for FN and USR.(This is referenced by many other tables)
ADMN is the new able added

enter image description here

I need to implement the following constraints.
  1. One Function can have only one admin.(1-1)
  2. One Admin can administer only one Function.(1-0/1)

I tried achieving 1 using by setting FN_ADM_I as foreign key to FN_I.
Could some one help me with constraints/relationships that can achieve both the requirement?

abatishchev
  • 98,240
  • 88
  • 296
  • 433
Raghav
  • 2,890
  • 7
  • 36
  • 56
  • You tried, but.. what had happened? What is your results? What does work and what does not? – abatishchev May 23 '11 at 11:17
  • Only one admin or one user (not simultaneously) can administer a function? – abatishchev May 23 '11 at 11:19
  • @abatishchev: The admin is inherited from the user. So basically only one Admin can administer a function. The reason i mentioned Admin/User is i am flexible to break the inheritance and keep the Admin-specific fields in USR with null for non-admins users. – Raghav May 23 '11 at 11:22
  • 1st requirement is achieved by setting FN_ADM_I as foreign key to FN_I. How to achieve the 2nd requirement by means of constraints? – Raghav May 23 '11 at 11:24
  • Sounds like this is a back to back, 1 to 1 relationship. – Raghav May 23 '11 at 11:26

2 Answers2

1

You may want to create a link table:

FN_ADMIN_REL

FN_I, -- PK
ADMIN_I --PK

where both fields are primary key with UNIQUE constraint:

ALTER TABLE FN_ADMIN_REL
ADD CONSTRAINT uc_Func_Admin UNIQUE (FN_I, ADMIN_I)
abatishchev
  • 98,240
  • 88
  • 296
  • 433
  • if I have FN_I and ADMIN_I as primary keys, then the combination of the fields should be a primary key, which means i can enter the value for (FN_I, ADMIN_I) as (1,2) (1,3) (1,5) etc.. Which means that FN 1 has 3 ADMINS. But i want only one admin for a function and only one function per admin. – Raghav May 23 '11 at 12:35
  • @Raghav: Then you need UNIQUE CONSTRAINT on both fields. – abatishchev May 23 '11 at 12:55
  • Could u pls tell me how to implement unique constraints on both the fields in Microsoft SQL server 2008 – Raghav May 23 '11 at 12:59
  • @Raghav: This also [Difference Between Unique Index vs Unique Constraint](http://blog.sqlauthority.com/2007/04/26/sql-server-difference-between-unique-index-vs-unique-constraint) – abatishchev May 23 '11 at 13:11
  • Thanks abatishchev. It worked. Wat u said is correct. But pls edit the ans to FN_I, -- PK ADMIN_I --unique constraint.. it might be helpful for others. – Raghav May 23 '11 at 13:24
  • @Raghav: Suggest an edit of my post as you see it, and i will approve it. – abatishchev May 23 '11 at 13:29
1

I think that you mixed up terminology here. I think 1-1 (one to one) relationship is not exactly what you think it is.

Regardless, if I understood your question correctly (which very easily might not be the case) here is what you do.

  • You already have FN and USR tables with primary key on each one.
  • Logically user who attends a function is many-to-many relationship: a user can attend many functions and each function can be attended by many users. Many-to-many relationships a traditionally modelled by a "link" table in sql server. You create FN_USR table that has FN_I field and USR_I field and make those foreign keys into FN and USR respectively. This is how you connect functions with attending users. (Note that on your diagram there is NO relationship between these two tables, so I'm just guessing that you want many-to-many, you have not explicitly specified this.)
  • Now let's look at the ADM table. This one "inherits" from USR. In sql server there is no concept of table inheritance. This is usually modelled via one-to-zero-or-one relationship. You create and ADM table and you make a primary ADM_I the primary key in this table. You also make it a foreign key into USR_I column in the USR table. This is how admin/user relationship is modelled. Every admin is a user (every ADM record has relevant USR record) but not every user is admin (not every USR record has relevant ADM record)
  • The last part is the relationship between Admins and Functions. Since no admin can administer more than one function this is also one-to-zero-or-one. In this case however we will add ADM_I column to the FUN table and make it a foreign key into ADM_I column in the ADM table. This will give us one-to-many. Now we can create a UNIQUE constraint on the ADM_I column in the FUN table to make this relationship one-to-zero-one.

Does this make sense?

Andrew Savinykh
  • 25,351
  • 17
  • 103
  • 158
  • 1 and 2 are perfectly fine. Now the problem comes in 3rd part of ur solution. This is the problematic one now. When I make ADM_I of FN table as foreign key to ADM_I of ADM table, and making the ADM_I as a composite primary key (in combination with existing FN_I) I end up having 2 primary keys in the FN table. Which means,(FN_I, ADM_I) is unique. So it allows (1,2) (1,3) etc.. meaning FN_I 1 can have 2 admins- 2 and 3. But what i want is one FN should have only one admin. – Raghav May 23 '11 at 12:37
  • "and making the ADM_I as a composite primary key (in combination with existing FN_I)" why would you do this? Just don't do this and you'll be fine. You don't need a composite key here. Leave FN_I as primary key in FN. Make a unique constraint on ADM_I in FN. Make a foreign key from ADM_I in FN to ADM_I in ADM. I'm actualy just repeated what I wrote in the answer above. Can you explain what is unclear? – Andrew Savinykh May 23 '11 at 19:13
  • yes the same way as u said. Actually i was thinking primary key and unique constraint are same. Now implemented and working fine. Thanks for the solution. – Raghav May 24 '11 at 06:32