Can I create unique index on selected records ?

[Alvin SIU]

Hi all,

I have a table in Db2 v8 like this:

Team Name Role
------ -------- ---------------------
A Superman Leader
A Batman Member
A WonderWoman Member
B Alvin Leader
B Peter Member
B Charlie Member
What I want to do is to ensure that
each team has one and only one Leader

The unique index on Team+Role
cannot achieve this purpose
because there are many Member records.

Functionally, what I want to do is to
have unique index on Team+Role
for those records with Role="Leader"

Is there any special method to do this in DB2
so as to enforce this constraint ?
Alvin SIU

[Tonkuma]

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

[--CELKO--]

CREATE TABLE Teams
(team_name CHAR(1) NOT NULL,
member_name VARCHAR(15) NOT NULL PRIMARY KEY,
team_role CHAR(1) DEFAULT 'L' NOT NULL
CHECK(team_role IN ('L', 'M'))
);

Now use this updatable view

CREATE VIEW Teams2 (team_name, member_name, team_role)
AS
SELECT team_name, member_name, team_role
FROM Teams
WHERE 1 = ALL (SELECT COUNT (*) -- always oen leader
FROM Teams
GROUP BY team_name)
WITH CHECK OPTION;

In standard SQL you could have use a regular CHECK() constraint or a
CREATE ASSERTION, but most products don't have table-level
constraints.

[Tonkuma]

------------------------------ Commands Entered
------------------------------
connect to SAMPLE user db2admin using ********;
------------------------------------------------------------------------------

Database Connection Information

Database server = DB2/NT 9.1.2
SQL authorization ID = DB2ADMIN
Local database alias = SAMPLE
A JDBC connection to the target has succeeded.
------------------------------ Commands Entered
------------------------------
CREATE TABLE Teams
(team_name CHAR(1) NOT NULL,
member_name VARCHAR(15) NOT NULL PRIMARY KEY,
team_role CHAR(1) NOT NULL DEFAULT 'L'
CHECK(team_role IN ('L', 'M'))
);
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered
------------------------------
INSERT INTO Teams
VALUES
('A', 'Superman', 'L')
,('A', 'Batman', 'M')
,('A', 'WonderWoman', 'M')
,('B', 'Alvin', 'L')
,('B', 'Peter', 'M')
,('B', 'Charlie', 'M')
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered
------------------------------
SELECT * FROM Teams;
------------------------------------------------------------------------------

TEAM_NAME MEMBER_NAME TEAM_ROLE
--------- --------------- ---------
A Superman L
A Batman M
A WonderWoman M
B Alvin L
B Peter M
B Charlie M

6 record(s) selected.

------------------------------ Commands Entered
------------------------------
CREATE VIEW Teams2 (team_name, member_name, team_role)
AS
SELECT team_name, member_name, team_role
FROM Teams
WHERE 1 = ALL (SELECT COUNT (*) -- always oen leader
FROM Teams
GROUP BY team_name)
WITH CHECK OPTION
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered
------------------------------
SELECT * FROM Teams2;
------------------------------------------------------------------------------

TEAM_NAME MEMBER_NAME TEAM_ROLE
--------- --------------- ---------

0 record(s) selected.

[Tonkuma]

------------------------------ Commands Entered
------------------------------
CREATE VIEW Teams3 (team_name, member_name, team_role)
AS
SELECT team_name, member_name, team_role
FROM Teams
WHERE 1 = ALL (SELECT COUNT (*) -- always oen leader
FROM Teams
WHERE team_role = 'L'
GROUP BY team_name)
WITH CHECK OPTION
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered
------------------------------
SELECT * FROM Teams3;
------------------------------------------------------------------------------

TEAM_NAME MEMBER_NAME TEAM_ROLE
--------- --------------- ---------
A Superman L
A Batman M
A WonderWoman M
B Alvin L
B Peter M
B Charlie M

6 record(s) selected.

[lenygold via DBMonster.com]

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:

>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

[--CELKO--]

On Aug 3, 5:23 am, Tonkuma <[e-mail]:

------------------------------ Commands Entered
------------------------------
CREATE VIEW Teams3 (team_name, member_name, team_role)
AS
SELECT team_name, member_name, team_role
FROM Teams
WHERE 1 = ALL (SELECT COUNT (*) -- always oen leader
FROM Teams
WHERE team_role = 'L'
GROUP BY team_name)
WITH CHECK OPTION
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.

------------------------------ Commands Entered
------------------------------
SELECT * FROM Teams3;
------------------------------------------------------------------------------

TEAM_NAME MEMBER_NAME TEAM_ROLE
--------- --------------- ---------
A Superman L
A Batman M
A WonderWoman M
B Alvin L
B Peter M
B Charlie M

6 record(s) selected.

Opps! Thanks!