Problem in unique key constraint

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Problem in unique key constraint

Post 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.
Last edited by dibyendrah on Mon Dec 18, 2006 12:48 am, edited 2 times in total.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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 ?
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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 ?
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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.
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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 ?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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...
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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?
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

What have you tried so far? (http://dev.mysql.com/doc/refman/5.0/en/ ... igger.html may come in handy..)
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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.
Post Reply