0

In my problem, i have two columns in my 'Person' database. One column stores the group the person belongs to and a column which stores that he is the admin of the group. Once group can have only one admin.

My thought is that i should put a unique contraint on both of the columns. But the problem is that for the other users which are not admins they unique contraint gets broken.

What should be the proper solution of he problem.

Example Database 

----------------------------------
Person_id | Group_Id  |  Is_admin
----------------------------------
1         | 9         | null
2         | 9         | null
3         | 9         | null
4         | 9         | 1
5         | 9         | null
6         | 4         | null
7         | 4         | null
8         | 4         | null
9         | 4         | 1

Now if I apply unique contraint on Group_Id and Is_admin they are unique but not in the case when the person is not an admin.

S. A. Malik
  • 3,465
  • 6
  • 37
  • 56

2 Answers2

2

Consider creating a new table to contain information about your groups, where group_id is the primary index and a column like admin_id that refers back to your person table as a foreign key. That way you eliminate the is_admin column completely and have a more relational design.

BellevueBob
  • 9,498
  • 5
  • 29
  • 56
  • this sir, summarized my thoughts. The other option could be to manage constraint by triggers. – Sebas Jan 13 '13 at 17:51
1

It works OK for me...

DROP TABLE IF EXISTS person_group;

CREATE TABLE person_group
(Person_id INT NOT NULL PRIMARY KEY
,Group_Id  INT NOT NULL
,Is_admin TINYINT NULL
,UNIQUE (group_id,is_admin)
);

INSERT INTO person_group VALUES
(1         , 9         , null),
(2         , 9         , null),
(3         , 9         , null),
(4         , 9         , 1),
(5         , 9         , null),
(6         , 4         , null),
(7         , 4         , null),
(8         , 4         , null),
(9         , 4         , 1);

SELECT * FROM person_group;
+-----------+----------+----------+
| Person_id | Group_Id | Is_admin |
+-----------+----------+----------+
|         6 |        4 |     NULL |
|         7 |        4 |     NULL |
|         8 |        4 |     NULL |
|         9 |        4 |        1 |
|         1 |        9 |     NULL |
|         2 |        9 |     NULL |
|         3 |        9 |     NULL |
|         5 |        9 |     NULL |
|         4 |        9 |        1 |
+-----------+----------+----------+

INSERT INTO person_group VALUES (10, 9, null);
Query OK, 1 row affected (0.00 sec)

INSERT INTO person_group VALUES (11, 9, null);
Query OK, 1 row affected (0.00 sec)
Strawberry
  • 33,750
  • 13
  • 40
  • 57
  • It gives me error '#1062 - Duplicate entry '0' for key 'is_admin''. I think you have inserted multiple values in single query that is why it does not give error. – S. A. Malik Jan 13 '13 at 17:49
  • Another possiblity is that you have used NULL where as i am using 0 – S. A. Malik Jan 13 '13 at 17:51
  • @backTangent A unique constrain allows for multiple NULL values. Your question includes the NULL value, so why are you talking about using 0? – ESG Jan 13 '13 at 17:55
  • @TheVedge: Actually have two tables one has null and the other has zeors. The first issue is solved but i thought to discuss the second one also. – S. A. Malik Jan 13 '13 at 18:00