Can I create unique index on selected records ?
Question posted by: Alvin SIU
(Guest)
on
August 1st, 2008 06:05 AM
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
Would you like to answer this question?
Sign up for a free account, or Login (if you're already a member).
|
|
August 1st, 2008 03:05 PM
# 2
|
Re: Can I create unique index on selected records ?
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
|
|
|
August 2nd, 2008 03:05 PM
# 3
|
Re: Can I create unique index on selected records ?
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.
|
|
|
August 3rd, 2008 09:55 AM
# 4
|
Re: Can I create unique index on selected records ?
------------------------------ 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.
|
|
|
August 3rd, 2008 10:25 AM
# 5
|
Re: Can I create unique index on selected records ?
------------------------------ 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.
|
|
|
August 6th, 2008 08:05 PM
# 6
|
Re: Can I create unique index on selected records ?
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:
Originally Posted by
>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...bm-db2/200808/1
|
|
|
August 6th, 2008 09:25 PM
# 7
|
Re: Can I create unique index on selected records ?
On Aug 3, 5:23 am, Tonkuma <tonk...@fiberbit.netwrote:
Quote:
Originally Posted by
------------------------------ 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!
Not the answer you were looking for? Post your question . . .
182,080 Experts ready to help you find a solution.
Sign up for a free account, or Login (if you're already a member).
|
|
|
Top Community Contributors
|
