повтор в уникальном ключе

[Алексей++]

задача такова: в некой базе B имеется таблица T, в таблице несколько простых полей и одно поле - уникальный ключ. Таблица, возможно, содержит ошибку - повторы значений в ключе. Записей в таблице много, перебор отпадает )  Как быстро выяснить, есть ли в таблице T записи с повторными значениями ключа ?

[Sla]

select count(key),key from table where Count(key)>1

[RXL]

Код: (SQL)

SELECT id, COUNT(*)
FROM mytable
GROUP BY id
HAVING COUNT(*) > 1

id - столбец, нуждающийся в проверке.
На выходе: id и число повторов.

Конечно, проверка имеет смысл пока не создан уникальный ключ - после повторных значений быть не может в принципе.

[Алексей++]

RXL, Ром, ключ уже создан...

Sla, а твой вариант уже для созданого ключа сработает ? Проверить то не могу по техническим причинам  - база не даёт просто так создать дубли

----
параллельный вопрос, о транзакциях

CommitTrans и Rollback - это "взаимозаменяемые" функции, или надо сначала обязательно Commit, а только потом откат ? (если откат потребовался) . Или же можно откатить сразу без подтверждения?

[RXL]

Леш, тогда и проверять нечего. Просто обрабатывай ошибки вставки: если будет ошибка дублирования, то удали запись и повтори вставку.

-------
COMMIT - принять транзакцию
ROLLBACK - откатить транзакцию (отменить)

Действия прямо противоположные. После любой из этих команд транзакция закрывается.

[Алексей++]

RXL, вставок ещё нету, просто хочется проанализировать на наличие ошибки

----
значения процедур я понимяу, последовательность не совсем понимаю тогда, когда откат требуется. И ещё вот этот момент

begintrans();
if(!committrans)
{
  //вызывать rollback ?
}

[RXL]

Леш, "вставка" - это действие, а не сущность. Ты, видимо, путаешь с понятием "запись" (или "строка"). Это так или я ошибаюсь?

Ошибка происходит при выполнении вставки (INSERT) или при модификации записи (UPDATE), но не существует сама по себе. При возникновении ошибки база не меняется, а транзакцию следует откатить.

Код:

try
{
  do INSERT // <----- ошибка возникает тут
  do COMMIT
}
catch (....)
{
  do ROLLBACK
}

[Алексей++]

RXL, нет, я не путаю. Просто сбило мну с толку то, что у CDataBase::CommitTrans() тип функции BOOL , так вот - стоит ли проверять возвращаемое значение, или это всегда неважно ?

[Sla]

Алексей1153++, что мой, что RXL вариант , это одно и тоже
даже, мне кажется что RXL будет правильнее

кста, при использовании агрегатной функции, не обязательно делать group by

если таблица пустая, то тебе субд просто-напросто не даст что-либо вставить, хотя варианты бывают

[Алексей++]

если таблица пустая, то тебе субд просто-напросто не даст что-либо вставить, хотя варианты бывают

как это не даст ? Ещё как даёт )

[RXL]

кста, при использовании агрегатной функции, не обязательно делать group by

Обязательно. Стандарт требует. Если конкретно, то на MySQL и Oracle без GROUP BY не работает.

если таблица пустая, то тебе субд просто-напросто не даст что-либо вставить, хотя варианты бывают

Ээээ.... Не логично что-то...

[Алексей++]

а COUNT(*) - считает количество возвёрнутых записей что ли ?

[Sla]

поправлюсь
в приведенных примерах group by можно не использовать, но по стандарту НУЖНО!

поэтому... Нужно ипользовать group by

count(*) считает все записи

[Алексей++]

Sla, я имею в виду когда вот тут
SELECT id, COUNT(*)
FROM mytable
GROUP BY id
HAVING COUNT(*) > 1


------------------
всё же хотелось бы узнать, имеет ли смысл проверять значения,возвращаемые функциями

Код:

BOOL CDataBase::BeginTrans();
BOOL CDataBase::CommitTrans();
BOOL CDataBase::RollBack();

[Sla]

данная конструкция вернет количество ID

RXL тебе привел код

commit - грубо, зафиксирует транзакцию в базе
rollback - откатит

зы. когда ты выполняешь транзакцию, то это не значит, что изменения в базе произойдут мгновенно
выполнение транзакции нужно подтвердить

есть такое autocommit
если тру, то после закрытия приложения или коннекта  commit произойдет автоматически.

вот для примера,

Код:

select * from table;
insert into table values (.....);
select * from table;
// увидишь что значения добавлены
rollback;
select * from table;
// увидишь что значений нету

Код:

select * from table;
insert into table values (.....);
select * from table;
// увидишь что значения добавлены
commit;
select * from table;
// увидишь что значения есть

Дальше тоже интересно, но я не очень в этом ....

например:

пользователь 1:	пользователь 2:
select * from table; insert into table values (.....); select * from table; // увидишь что значения добавлены, но увидишь только свои rollback; select * from table; // увидишь что значения есть, но от второго пользователя	select * from table; insert into table values (.....); select * from table; // увидит только свои значения commit; select * from table; // увидишь что значения есть

[Dimka]

кста, при использовании агрегатной функции, не обязательно делать group by

Обязательно.

Обязательно. Стандарт требует. Если конкретно, то на MySQL и Oracle без GROUP BY не работает.

Не поэтому.

в приведенных примерах group by можно не использовать, но по стандарту НУЖНО!

поэтому... Нужно ипользовать group by

В приведённых примерах нужно использовать group by по условию задачи.

Запрос вида

Код:

SELECT COUNT(id)
FROM mytable

Вернёт просто количество записей.

Запрос вида

Код:

SELECT COUNT(DISTINCT id)
FROM mytable

Вернёт количество уникальных записей.

Следовательно, запрос вида

Код:

SELECT COUNT(id) - COUNT(DISTINCT id) AS repcnt
FROM mytable

Вернёт количество неуникальных записей в таблице - можно установить факт наличия дубликатов, но мы не будем знать, какие записи содержат дубликаты.

Секция группировки предписывает применять агрегатные функции к группам записей с одинаковым значением полей, перечисленных в описании группировки (в нашем случае поле id).

Группировка применяется к запросу после первичного горизонтального фильтра, поэтому в первичном горизонтальном фильтре (секция WHERE) использование агрегатных функций либо недопустимо, либо они будут возвращать результат по всем записям, а не для групп записей. Первый запрос Sla будет возвращать 1 запись с количеством строк в таблице, если в таблице более одной записи - это решение совсем не той задачи, что задана в начале.

Именно поэтому необходимо использовать группировку и секцию вторичного горизонтального фильтра (HAVING), как раз предназначенного для фильтрации результатов группировок.

Запрос вида

Код:

SELECT
    id,
    COUNT(id) AS idcnt
FROM mytable
GROUP BY id

Вернёт список уникальных идентификаторов и количество таких записей, которые содержат соответствующий идентификатор. Нас интересуют только те записи, которые содержат неуникальный идентификатор, т.е. их количество в группе больше 1.

Код:

SELECT
    id,
    COUNT(id) AS idcnt
FROM mytable
GROUP BY id
HAVING COUNT(id) > 1

Если нужно получить собственно записи с повторяющимися идентификаторами, то можно использовать подзапрос:

Код:

SELECT
    *
FROM mytable
WHERE id IN
    (SELECT id
     FROM mytable
     GROUP BY id
     HAVING COUNT(id) > 1)

[Алексей++]

вот теперь всё вроде понятно ) Пасиба

[Алексей++]

ещё попутные вопросы:

1)
добавляю в таблицу новое поле . Как инициализировать поле неповторяющимися значениями ? Только перебором ? Или есть какой нибудь специальный запрос ?

2)

Код:

BeginTrans;

//создаём новое поле
ALTER TABLE T ADD Fnew BIGINT NOT NULL;

//заполнить поле копированием из старого поля INTEGER NOT NULL
UPDATE T SET Fnew=Fold
 ---> происходит ошибка преобразования типов

 ...

CommitTrans

Когда же без BeginTrans, то всё исправно работает. В чём причина и как победить ?

[Dimka]

ещё попутные вопросы:

1)
добавляю в таблицу новое поле . Как инициализировать поле неповторяющимися значениями ? Только перебором ? Или есть какой нибудь специальный запрос ?

Смотря какое значение. Если оно может быть биективно отображено из первичного ключа, то можно

Код:

UPDATE mytable
SET newcol = <некое выражение от>id

Иначе только перебором.

//заполнить поле копированием из старого поля INTEGER NOT NULL
UPDATE T SET Fnew=Fold
 ---> происходит ошибка преобразования типов

А типы какие?

[Алексей++]

типы - новое BIGINT , старое - INTEGER.

кстати, ошибка не вылетает, если делать так

Код:

BeginTrans;

ALTER TABLE T ADD Fnew BIGINT NOT NULL;

CommitTrans;

BeginTrans;
UPDATE T SET Fnew=Fold

 ...

CommitTrans;

но это не совсем удобно

[Dimka]

А преобразование типов не работает? Что-то вроде:

Код:

UPDATE mytable
SET newField = CAST(oldField AS BIGINT)

Кстати, дело, похоже, в последовательности действий. FB поддерживает вложенные транзакции?

Код:

BEGIN TRANSACTION OutTran
BEGIN TRANSACTION FirstTran
ALTER TABLE ...
COMMIT TRANSACTION FirstTran
BEGIN TRANSACTION SecondTran
UPDATE ...
COMMIT TRANSACTION SecondTran
COMMIT TRANSACTION OutTran

Поддерживает ли FB или его клиент инструкцию типа "GO", которая предписывает безотлагательно выполнить все ранее переданные команды до начала выполенния следующих команд?

[Алексей++]

вложенные не получается делать, влетает. И в мсдн:

CDatabase::BeginTrans
Call this member function to begin a transaction with the connected data source.
...
Caution   Calling BeginTrans again without calling CommitTrans or Rollback is an error.

GO не поможет в любом случае - многострочные запросы из программы делать не получается.

с CAST попробовал на всякий случай - тоже не работает.

Пока вижу только такой выход - завести некую таблицу с "системными" полями, когда первый раз вся операция пройдёт удачно - выставлю флажок

[Dimka]

Пока вижу только такой выход - завести некую таблицу с "системными" полями, когда первый раз вся операция пройдёт удачно - выставлю флажок

Думаю, есть более цивилизованные способы. Искать надо.

Меня гложут сомнения: правильно ли вообще понимается проблема, точнее её причина?

Скажем, новое поле отмечено как NOT NULL. Чем оно заполняется по умолчанию, если не задано никакого DEFAULT-выражения?

[Алексей++]

dimka, по умолчанию поле заполнено null... Я сделал сейчас

ALTER TABLE T ADD Fnew BIGINT default 0 NOT NULL

всё равно то же самое. Без транзакции же исправно заполняется '0' . Раньше хоть и заполнялось NULL , но отличий не наблюдается )

[Алексей++]

dimka, ты меня натолкнул на МЫСЛЬ

было бы здорово, если бы можно было просто тупо изменить тип поля F_old с INTEGER на BIGINT . Все перечисленные выше проблемы бы сразу отпали )
Такое возможно ?

[Алексей++]

собственно вот, нашёл

ALTER TABLE mess_log alter MESSAGE_ID TYPE BIGINT

но есть одно но. Имя поля используется в триггере, база не даёт менять тип поля, когда есть ссылки на поле. Без триггера всё нормально. Как тут поступить ?

[Алексей++]

в qsl-editor в IBExpert попробовал так

Код:

/*выносим триггер (но хотелось бы не так жёстко :( )*/
drop trigger MESS_LOG_BI ;

/*меняем тип ключевого поля*/
ALTER TABLE mess_log alter MESSAGE_ID TYPE BIGINT  ;

/*создаём триггер обратно*/
create trigger MESS_LOG_BI for mess_log before insert as
 BEGIN
  IF (NEW.MESSAGE_ID IS NULL)
   THEN NEW.MESSAGE_ID = GEN_ID(new_mess_id,1);
 END

это получилось, но как-то всё это кажется коряво ...

[Dimka]

Имхо, не коряво, а логично. Всё равно тебе пришлось бы потом переключать триггер на новое поле.

[Алексей++]

тогда так и оставим )

ещё как-то надо сделать флажок о том, что я эту коррекцию вносил, в отсутствие опыта вижу два варианта:
1) вышеописанное некое поле-флаг в некой "системной" таблице
2) проверить тип поля (как ?)

[Алексей++]

программно проверить тип поля получилось так

Код:

CString sql;
CDBVariant var;
CRecordset rs(pbase);

try
{
	sql="SELECT FIRST(1) message_id FROM T";
	rs.Open(CRecordset::snapshot,sql,CRecordset::readOnly);

	CODBCFieldInfo FI;
	rs.GetODBCFieldInfo((short)0,FI);
	rs.Close();

	switch(FI.m_nSQLType)
	{
		default:
		{
			//какой то левый тип
		}
		break;

		case SQL_INTEGER:
		{
			//
		}
		break;

		case SQL_BIGINT:
		{
			//
		}
		break;
	}

}
catch(CException* e)
{
}