Watch, Follow, &
Connect with Us

Welcome, Guest
Guest Settings
Help

Thread: Not Unique?


This question is answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 5 - Last Post: Jan 16, 2017 11:18 AM Last Post By: Jeff Overcash (... Threads: [ Previous | Next ]
Eric ten Westen...

Posts: 76
Registered: 12/8/99
Not Unique?  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 10, 2017 3:29 AM
Hi

select distinct bedrijf_id, createdate, count(*) from REI_BDR_DOCUMENT group by bedrijf_id, createdate
having count(*) > 1

This sql does not give any result

howeever

ALTER TABLE REI_BDR_DOCUMENT ADD CONSTRAINT PK_REI_BDR_DOCUMENT PRIMARY KEY (BEDRIJF_ID,CREATEDATE)

Gives

Attempt to store duplicate values in unique index ... null segment of Unique key

select * from REI_BDR_DOCUMENT where bedrijf_id is null and
select * from REI_BDR_DOCUMENT where CREATEDATE is null
give no records

How can this be?

Eric

Edited by: Eric ten Westenend on Jan 10, 2017 3:31 AM

Edited by: Eric ten Westenend on Jan 10, 2017 3:33 AM

Jeff Overcash (...

Posts: 1,328
Registered: 9/23/99
Re: Not Unique? [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 10, 2017 10:30 AM   in response to: Eric ten Westen... in response to: Eric ten Westen...
Eric ten Westenend wrote:
Hi

select distinct bedrijf_id, createdate, count(*) from REI_BDR_DOCUMENT group by bedrijf_id, createdate
having count(*) > 1

This sql does not give any result

howeever

ALTER TABLE REI_BDR_DOCUMENT ADD CONSTRAINT PK_REI_BDR_DOCUMENT PRIMARY KEY (BEDRIJF_ID,CREATEDATE)

Gives

Attempt to store duplicate values in unique index ... null segment of Unique key

select * from REI_BDR_DOCUMENT where bedrijf_id is null and
select * from REI_BDR_DOCUMENT where CREATEDATE is null
give no records

How can this be?


Remove the distinct. The distinct is applied before the counting takes place so
your duplicates are being reduced down to a single row, whose count is always 1
so failing the having part.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Eric ten Westen...

Posts: 76
Registered: 12/8/99
Re: Not Unique? [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 11, 2017 7:19 AM   in response to: Jeff Overcash (... in response to: Jeff Overcash (...
Hi Jeff

Even without the distinct it does not return any records

Eric

Jeff Overcash (TeamB) wrote:
Eric ten Westenend wrote:
Hi

select distinct bedrijf_id, createdate, count(*) from REI_BDR_DOCUMENT group by bedrijf_id, createdate
having count(*) > 1

This sql does not give any result

howeever

ALTER TABLE REI_BDR_DOCUMENT ADD CONSTRAINT PK_REI_BDR_DOCUMENT PRIMARY KEY (BEDRIJF_ID,CREATEDATE)

Gives

Attempt to store duplicate values in unique index ... null segment of Unique key

select * from REI_BDR_DOCUMENT where bedrijf_id is null and
select * from REI_BDR_DOCUMENT where CREATEDATE is null
give no records

How can this be?


Remove the distinct. The distinct is applied before the counting takes place so
your duplicates are being reduced down to a single row, whose count is always 1
so failing the having part.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Eric ten Westen...

Posts: 76
Registered: 12/8/99
Re: Not Unique? [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 11, 2017 7:20 AM   in response to: Eric ten Westen... in response to: Eric ten Westen...
Hi

This is really strang. now my

ALTER TABLE REI_BDR_DOCUMENT ADD CONSTRAINT PK_REI_BDR_DOCUMENT PRIMARY KEY (BEDRIJF_ID,CREATEDATE)

is created???????

What happend?

Eric ten Westenend wrote:
Hi Jeff

Even without the distinct it does not return any records

Eric

Jeff Overcash (TeamB) wrote:
Eric ten Westenend wrote:
Hi

select distinct bedrijf_id, createdate, count(*) from REI_BDR_DOCUMENT group by bedrijf_id, createdate
having count(*) > 1

This sql does not give any result

howeever

ALTER TABLE REI_BDR_DOCUMENT ADD CONSTRAINT PK_REI_BDR_DOCUMENT PRIMARY KEY (BEDRIJF_ID,CREATEDATE)

Gives

Attempt to store duplicate values in unique index ... null segment of Unique key

select * from REI_BDR_DOCUMENT where bedrijf_id is null and
select * from REI_BDR_DOCUMENT where CREATEDATE is null
give no records

How can this be?


Remove the distinct. The distinct is applied before the counting takes place so
your duplicates are being reduced down to a single row, whose count is always 1
so failing the having part.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Eric ten Westen...

Posts: 76
Registered: 12/8/99
Re: Not Unique? [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 16, 2017 3:59 AM   in response to: Eric ten Westen... in response to: Eric ten Westen...
Hi

Saw other issue about this "Not able to set primary key"

Same issue

So after restarting my pc next day, i could add the primary key

Look like some kind of caching problem?

Eric


Eric ten Westenend wrote:
Hi

This is really strang. now my

ALTER TABLE REI_BDR_DOCUMENT ADD CONSTRAINT PK_REI_BDR_DOCUMENT PRIMARY KEY (BEDRIJF_ID,CREATEDATE)

is created???????

What happend?

Eric ten Westenend wrote:
Hi Jeff

Even without the distinct it does not return any records

Eric

Jeff Overcash (TeamB) wrote:
Eric ten Westenend wrote:
Hi

select distinct bedrijf_id, createdate, count(*) from REI_BDR_DOCUMENT group by bedrijf_id, createdate
having count(*) > 1

This sql does not give any result

howeever

ALTER TABLE REI_BDR_DOCUMENT ADD CONSTRAINT PK_REI_BDR_DOCUMENT PRIMARY KEY (BEDRIJF_ID,CREATEDATE)

Gives

Attempt to store duplicate values in unique index ... null segment of Unique key

select * from REI_BDR_DOCUMENT where bedrijf_id is null and
select * from REI_BDR_DOCUMENT where CREATEDATE is null
give no records

How can this be?


Remove the distinct. The distinct is applied before the counting takes place so
your duplicates are being reduced down to a single row, whose count is always 1
so failing the having part.

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)

Jeff Overcash (...

Posts: 1,328
Registered: 9/23/99
Re: Not Unique? [Edit]  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jan 16, 2017 11:18 AM   in response to: Eric ten Westen... in response to: Eric ten Westen...
Eric ten Westenend wrote:
Hi

Saw other issue about this "Not able to set primary key"

Same issue

So after restarting my pc next day, i could add the primary key

Look like some kind of caching problem?

Eric


No it will be that there is an outstanding transaction that has made a change
that has yet to be committed. If you see a message like that bring up the
performance monitor in IBConsole and look at the transactions and look closely
at the transactions that have done an insert/update/delete. Those long running
transactions will be the reason you are having issues putting a primary key on
like that.

As a general rule best practices type of thing, meta data changes should
normally be done as the exclusive user in the DB with no one else logged in
(some pretty much require it).

--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
Learning is finding out what you already know. Doing is demonstrating that you
know it. Teaching is reminding others that they know it as well as you. We are
all leaners, doers, teachers. (R Bach)
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02