Another idea is to create a trigger
I used your data for testing:
CREATE TABLE MemberRole_leny
(Team VARCHAR( 6) NOT NULL
,Name VARCHAR(15) NOT NULL
,Role CHAR(6) NOT NULL
CHECK( Role IN ('Leader', 'Member') )
,PRIMARY KEY(Team, Name));
--DROP TRIGGER MEMBER_CHECK;
--#SET TERMINATOR !
CREATE TRIGGER MEMBER_CHECK
NO CASCADE BEFORE INSERT
ON MemberRole_leny
REFERENCING NEW AS n
FOR EACH ROW MODE DB2SQL
WHEN (EXISTS
(SELECT TEAM FROM MemberRole_leny
WHERE TEAM = n.TEAM
AND ROLE = n.ROLE AND n.role = 'Leader'))
BEGIN ATOMIC
SIGNAL SQLSTATE '99999'
('ATTEMPT TO INSERT DUPLICATE Leader in TEAM - ONLY ONE PER TEAM');
END!
TESTING MY SOLUTION:
INSERT INTO MemberRole_leny(Team, Name, Role)
VALUES
('A', 'Superman', 'Leader')
,('A', 'Batman', 'Member')
,('A', 'Wonder', 'Member')
,('B', 'Alvin', 'Leader')
,('B', 'Peter', 'Member')
,('B', 'Charlie', 'Member');
----------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
/* OK */
INSERT INTO MemberRole_leny(Team, Name, Role)
VALUES
('B', 'Alzola', 'Member');
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
/* OK */
INSERT INTO MemberRole_leny(Team, Name, Role)
VALUES
('B', 'WonderWoman', 'Member');
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
/* NOT GOOD - SUPERMEN ALREADY EXISTS AS LEADER */
INSERT INTO MemberRole_leny(Team, Name, Role)
VALUES
('A', 'Superman', 'Member');
------------------------------------------------------------------------------
------------
sqlcode: -803
statement, or foreign key update caused by a DELETE statement are not valid
because the primary key, unique constraint or unique index identified by
"1" constrains table "LENY.MEMBERROLE
"
from having duplicate values for the index key.
/* NOT GOOD - TEAM "A" ALREADY HAVE A LEADER */
INSERT INTO MemberRole_leny(Team, Name, Role)
VALUES
('A', 'Spiderman', 'Leader');
sqlcode: -438, +438
Application raised error with diagnostic text: "ATTEMPT TO INSERT DUPLICATE
Leader in TEAM - ONLY ONE PER TEAM"
Tonkuma wrote:
Quote:
>AFAIK there is no such functionality in DB2.
>One idea is to separate table into Leader table and Member table.
>Another idea is to add a generated column, then to create unique index
>on it.
>Like this:
>------------------------------ Commands Entered
>------------------------------
>CREATE TABLE MemberRole
>(Team VARCHAR( 6) NOT NULL
>,Name VARCHAR(15) NOT NULL
>,Role CHAR(6) NOT NULL
CHECK( Role IN ('Leader', 'Member') )
>,PRIMARY KEY(Team, Name)
/* a person can belong to multiple groups */
>,UniqueLeader VARCHAR(30) NOT NULL
GENERATED ALWAYS AS (
Team || CASE Role WHEN 'Leader' THEN 'Leader' ELSE Name END
)
>);
>------------------------------------------------------------------------------
>DB20000I The SQL command completed successfully.
>
>------------------------------ Commands Entered
>------------------------------
>CREATE UNIQUE INDEX MemberRole_UniqueLeader
ON MemberRole (UniqueLeader)
>;
>------------------------------------------------------------------------------
>DB20000I The SQL command completed successfully.
>
>------------------------------ Commands Entered
>------------------------------
>INSERT INTO MemberRole(Team, Name, Role)
>VALUES
('A', 'Superman', 'Leader')
>,('A', 'Batman', 'Member')
>,('A', 'WonderWoman', 'Member')
>,('B', 'Alvin', 'Leader')
>,('B', 'Peter', 'Member')
>,('B', 'Charlie', 'Member');
>------------------------------------------------------------------------------
>DB20000I The SQL command completed successfully.
>
>------------------------------ Commands Entered
>------------------------------
>/* OK */
>INSERT INTO MemberRole(Team, Name, Role)
>VALUES
('B', 'Alzola', 'Member');
>------------------------------------------------------------------------------
>DB20000I The SQL command completed successfully.
>
>------------------------------ Commands Entered
>------------------------------
>/* OK */
>INSERT INTO MemberRole(Team, Name, Role)
>VALUES
('B', 'WonderWoman', 'Member');
>------------------------------------------------------------------------------
>DB20000I The SQL command completed successfully.
>
>------------------------------ Commands Entered
>------------------------------
>/* NG */
>INSERT INTO MemberRole(Team, Name, Role)
>VALUES
('A', 'Superman', 'Member');
>------------------------------------------------------------------------------
>DB21034E The command was processed as an SQL statement because it was
>not a
>valid Command Line Processor command. During SQL processing it
>returned:
>SQL0803N One or more values in the INSERT statement, UPDATE
>statement, or
>foreign key update caused by a DELETE statement are not valid because
>the
>primary key, unique constraint or unique index identified by "1"
>constrains
>table "DB2ADMIN.MEMBERROLE" from having duplicate values for the index
>key.
>SQLSTATE=23505
>
>------------------------------ Commands Entered
>------------------------------
>/* NG */
>INSERT INTO MemberRole(Team, Name, Role)
>VALUES
('A', 'Spiderman', 'Leader');
>------------------------------------------------------------------------------
>DB21034E The command was processed as an SQL statement because it was
>not a
>valid Command Line Processor command. During SQL processing it
>returned:
>SQL0803N One or more values in the INSERT statement, UPDATE
>statement, or
>foreign key update caused by a DELETE statement are not valid because
>the
>primary key, unique constraint or unique index identified by "2"
>constrains
>table "DB2ADMIN.MEMBERROLE" from having duplicate values for the index
>key.
>SQLSTATE=23505
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200808/1 
