Page 1 of 1

Problem in unique key constraint

Posted: Sun Dec 17, 2006 3:38 am
by dibyendrah
Hello everybody,

I have following table :

Code: Select all

CREATE TABLE `tbl_rel_hw_pos_gw` (

  `hw_pos_gw_rel_id` INT(11) NOT NULL AUTO_INCREMENT,

  `hw_pos_rel_id` INT(11) NOT NULL,

  `gw_id` INT(11) DEFAULT NULL,

  PRIMARY KEY  (`hw_pos_gw_rel_id`),

  UNIQUE KEY `UNQ_tbl_rel_hw_pos_gw` (`hw_pos_rel_id`,`gw_id`),

  KEY `FK_tbl_rel_hw_pos_gw1` (`hw_pos_rel_id`),

  KEY `FK_tbl_rel_hw_pos_gw2` (`gw_id`),

  CONSTRAINT `CN_tbl_rel_hw_pos_gw1` FOREIGN KEY(`hw_pos_rel_id`) REFERENCES `tbl_rel_hw_pos`(`hw_pos_rel_id`) ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT `CN_tbl_rel_hw_pos_gw2` FOREIGN KEY(`gw_id`) REFERENCES `tbl_guide_words`(`gw_id`) ON DELETE SET NULL ON UPDATE CASCADE

) ENGINE=INNODB ROW_FORMAT=COMPACT COMMENT='Relationship Between Headword - Part of Speech & Guideword';
Here, I have made the composite unique key of two keys. Here key `gw_id` can be null in some case. Now, the situation here is that the unique key fails when the row has `gw_id` as NULl values.

Here, the unique key constraint fails when the row has following values :

Code: Select all

hw_pos_gw_rel_id        hw_pos_rel_id        gw_id
1                          1                  NULL
2                          1                  NULL
In the above case, the unique key will not stop the from duplicate entry when gw_id is assigned with null values.

But, unique key constraint works when the table has following values :

Code: Select all

hw_pos_gw_rel_id        hw_pos_rel_id        gw_id
1                          1                    5
2                          1                    5
In the above case, the unique key will stop the duplicate entry.

Any idea ?

Please help.

Posted: Sun Dec 17, 2006 7:26 am
by timvw
I don't understand what you're trying to say (but that's probably because i'm not very awake now...) But you are aware that NULL <> NULL ?

Posted: Mon Dec 18, 2006 1:17 am
by dibyendrah
timvw wrote:I don't understand what you're trying to say (but that's probably because i'm not very awake now...) But you are aware that NULL <> NULL ?
Will you please explain about NULL<> NULL issues please ?

Posted: Mon Dec 18, 2006 2:48 am
by dibyendrah
I want to know the duplicate entries from this table.

I used this query but failed :

Code: Select all

select hw_pos_rel_id, gw_id from tbl_rel_hw_pos_gw
group by hw_pos_gw_rel_id
having count (hw_pos_rel_id, gw_id) >1

Error:

Code: Select all

Error Code : 1305
FUNCTION np_dictionary_db.count does not exist
(3 ms taken)

I just wanted to know the duplicate entries (combination of hw_pos_rel_id, gw_id).

Please suggest.

Posted: Mon Dec 18, 2006 3:23 am
by timvw
dibyendrah wrote:
timvw wrote:I don't understand what you're trying to say (but that's probably because i'm not very awake now...) But you are aware that NULL <> NULL ?
Will you please explain about NULL<> NULL issues please ?
It means that if you compare two NULL values in SQL they will not be equal...

Eg: you have entitities that exist out of two properties. Two entities (a, b) and (c, d) are only equal if a == c and b == d. For the entities (a, NULL) and (a, NULL) that would mean they are not equal because NULL does not equal NULL.

Posted: Mon Dec 18, 2006 4:09 am
by dibyendrah
Thank you timvw for your response.

So, can't we make the key of two fields as unique key in which anyone of them can be null ?

Posted: Mon Dec 18, 2006 7:43 am
by timvw
DB2 does not allow unique keys if one of the columns in the key allows NULL values... but for MySQL you may want to consult the manual...

Posted: Tue Dec 19, 2006 1:01 am
by dibyendrah
Thank you so much timvw. Will you please suggest on how to find duplicate entries in this table ?

Code: Select all

CREATE TABLE `tbl_rel_hw_pos_gw` (
  `hw_pos_gw_rel_id` INT(11) NOT NULL AUTO_INCREMENT,
  `hw_pos_rel_id` INT(11) NOT NULL,
  `gw_id` INT(11) DEFAULT NULL,
  PRIMARY KEY  (`hw_pos_gw_rel_id`), 
UNIQUE KEY `UNQ_tbl_rel_hw_pos_gw` (`hw_pos_rel_id`,`gw_id`)
) ENGINE=INNODB;
Duplicates to check for two fields having same values :

Code: Select all

hw_pos_rel_id
gw_id
Please Suggest.

Posted: Tue Dec 19, 2006 3:33 am
by timvw
What about adding triggers (insert/update) that checks if one of the two values is NULL.. And then verifies if the other value already exists?

Posted: Tue Dec 19, 2006 3:54 am
by dibyendrah
That would be fine but I've never made a trigger to do this kind of jobs. Will you please show how to make a trigger work to do this kind of job ? I've to learn triggers now as your idea is good to stop all this kind of duplication. I would be thankful, if you please show how to get the duplicate values from this tables ?

Thank you.

Posted: Tue Dec 19, 2006 2:07 pm
by timvw
What have you tried so far? (http://dev.mysql.com/doc/refman/5.0/en/ ... igger.html may come in handy..)

Posted: Wed Dec 20, 2006 12:29 am
by dibyendrah
Thank you so much timvw.
I haven't made a triggers in database till now and I am learning from MySQL manual now.

But I have tried to figure out how much there are duplicate records from making this query :

Code: Select all

select hw_pos_gw_rel_id, hw_pos_rel_id, gw_id, count(*) as occ from tbl_rel_hw_pos_gw
group by hw_pos_rel_id, gw_id
having occ > 1
order by occ desc
Output :

Code: Select all

hw_pos_gw_rel_id	hw_pos_rel_id	gw_id	occ
7755 	8819 	(NULL) 	4
7995 	9031 	(NULL) 	3
7624 	8704 	(NULL) 	3
8539 	9519 	(NULL) 	3
7736 	8802 	(NULL) 	3
7257 	8393 	(NULL) 	3
8289 	9289 	(NULL) 	3
8005 	9036 	(NULL) 	2
7237 	8373 	(NULL) 	2
7424 	8530 	(NULL) 	2
7781 	8838 	(NULL) 	2
7588 	8672 	(NULL) 	2
7912 	8957 	(NULL) 	2
8122 	9148 	(NULL) 	2
........
Anyway, I'll look at the triggers which will be really helpful in future works.