Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
beg_inner
Guest
имеется длинная процедура, при выполнении которой вылазит ошибка
Cannot define PRIMARY KEY constraint on nullable column.

перед созданием PK явно задаю для столбца ALTER TABLE ... ALTER COLUMN ... NOT NULL.
и все равно ошибка вылазит.

пробую написать упрощенную процедуру по тому же самому сценарию:

create proc a.test1 as
begin tran
create table a.t(id int)

select *
into a.t1
from a.t

select *
into a.t2
from a.t1

alter table a.t2
alter column id int not null

alter table a.t2
add constraint pk_t1 primary key (id)
commit


в ней ошибка не вылазит.
что ставит меня в полный тупик.
логика 1 в 1:
создается таблица по select into,
в исходной таблице интересующее поле не имеет ограничение not null
(ограничение должно быть, но на ту таблицу у меня только селект, переправить не могу)

после этого снова по select into создается другая таблица,
где то поле первичный ключ.
не дает навесить констрейнт, хотя явно строкой выше задано ALTER COLUMN ... NOT NULL.
как такое может быть?
29 мар 12, 17:57    [12335623]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
А так?
create proc a.test1 as
begin tran
create table a.t(id int)

select *
into a.t1
from a.t

select *
into a.t2
from a.t1

alter table a.t2
alter column id int not null

exec ('alter table a.t2add constraint pk_t1 primary key (id)')
commit
29 мар 12, 18:01    [12335660]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
beg_inner
Guest
Гавриленко Сергей Алексеевич,

так конечно отработало.
я никак не пойму, почему в одной процедуре ошибка вылазит, а в другой, аналогичной - нет?
почему в упрощенной не вылазит?
29 мар 12, 18:08    [12335718]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
beg_inner
Guest
вернее, должно быть так:
(изначальная таблица уже существует)
create table a.t(id int)

alter proc a.test1 as
begin tran

select *
into a.t1
from a.t

select *
into a.t2
from a.t1

alter table a.t2
alter column id int not null

alter table a.t2
add constraint pk_t1 primary key (id)
commit

но все равно не вылазит ошибка даже так.
29 мар 12, 18:14    [12335764]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Потому что это, скорее всего, ошибка компиляции.
29 мар 12, 18:17    [12335788]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
Вот такой кейс, что ли, валится?

use tempdb
go
if object_id('tempdb..t') is not null
    drop table t
if object_id('tempdb..t1') is not null
    drop table t1    
if object_id('tempdb..t2') is not null
    drop table t2    
if object_id('tempdb..test1') is not null
    drop proc test1    
go    
create table t2(id int null)
go
create proc test1 as
begin tran

alter table t2
alter column id int not null

alter table t2
add constraint pk_t1 primary key (id)
commit
go
exec test1
29 мар 12, 18:24    [12335839]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
beg_inner
Guest
Гавриленко Сергей Алексеевич,

не поняла. разве ошибка компиляции не должна вылазить еще при комппиляции?
он же мне компилирует и сохраняет процедуру, а вылазит уже при выполнении.
и даже если он имена рассматривает уже при выполнении и обнаруживает, что копирую из таблицы с nullable полем.
он же и в упрощенном случае то же самое должен увидеть?
29 мар 12, 18:26    [12335856]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
beg_inner
Guest
Гавриленко Сергей Алексеевич
Вот такой кейс, что ли, валится?



да, такое валится с той же ошибкой.
Msg 8111, Level 16, State 1, Procedure test1, Line 7
Cannot define PRIMARY KEY constraint on nullable column in table 't2'.
Msg 1750, Level 16, State 0, Procedure test1, Line 7
Could not create constraint. See previous errors.

и в чем же отличие от моего опрощенного?
29 мар 12, 18:33    [12335893]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
SamMan
Member

Откуда: Moscow
Сообщений: 759
beg_inner,

Слушайте, ааатличная тема, еже ей! Сам ответа не знаю, но жажду узнать. Вот, "вычленил" суть проблемы:
USE master
go
CREATE DATABASE XXCX
go
USE XXCX
go
create table t(id int)
go
create proc test1 as
BEGIN
	alter table t alter column id int not NULL
	alter table t add constraint pk_t1 primary key (id)
END
go
EXEC test1
go
--clean up
USE master
go
DROP DATABASE XXCX


Требуется указать "точку ошибки" в предыдущем скрипте и ваш вопрос - решен. Но вот ГДЕ она (ошибка) - я решительно не вижу. Коллеги - помогайте!
29 мар 12, 20:15    [12336350]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
step_ks
Member

Откуда:
Сообщений: 936
SamMan, а такая ошибка вас удивляет?

USE master
go
CREATE DATABASE XXCX
go
USE XXCX
go
create table t(id int)
go
create proc test1 as
BEGIN
	alter table t add  id2 int 
	select id2 from t
END
go
begin try
EXEC test1
end try
begin catch
select xact_state()
end catch
go
--clean up
USE master
go
DROP DATABASE XXCX


Msg 207, Level 16, State 1, Procedure test1, Line 4
Invalid column name 'id2'.
29 мар 12, 20:49    [12336453]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
step_ks
Member

Откуда:
Сообщений: 936
упс, не тот код скопипастил, прошу прощения. Вот что имел ввиду

USE master
go
CREATE DATABASE XXCX
go
USE XXCX

go
create proc test1 as
BEGIN
	alter table t add  id2 int 
	select id2 from t
END
go
go
create table t(id int)
go
EXEC test1
go
--clean up
USE master
go
DROP DATABASE XXCX


Msg 207, Level 16, State 1, Procedure test1, Line 4
Invalid column name 'id2'.
29 мар 12, 20:52    [12336467]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
AndyD
Member

Откуда:
Сообщений: 30
Это ответ
Гавриленко Сергей Алексеевич
Потому что это, скорее всего, ошибка компиляции.


Отложенное разрешение и компиляция имен

Выделите создание PK в отдельную процедуру и вызывайте ее из основной после установки NOT NULL
29 мар 12, 21:07    [12336521]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
beg_inner
Guest
AndyD
Это ответ
Гавриленко Сергей Алексеевич
Потому что это, скорее всего, ошибка компиляции.


Отложенное разрешение и компиляция имен

Выделите создание PK в отдельную процедуру и вызывайте ее из основной после установки NOT NULL

товарищи, с этим я согласна, я же тоже предположила, что он выдает при выполнении, т.к. имена разрешает при выполнении, все ок!
НО, объясните плиз, почему вот так:
create table a.t(id int)
go

create proc a.test1 as
begin tran

select *
into a.t1
from a.t

select *
into a.t2
from a.t1

alter table a.t2
alter column id int not null

alter table a.t2
add constraint pk_t1 primary key (id)
commit

go

exec a.test1

ошибка НЕ вылазит?
29 мар 12, 22:42    [12336853]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
beg_inner
ошибка НЕ вылазит?
Не вылазит потому, что при отсутствии объекта на момент запуска процедуры по нему не проверяется ничего. В случае существования объекта на момент запуска процедуры, будет проверена возможность выполнения каждого из стейтментов, даже если они написаны за каким-нибудь if-ом, который вообще никогда реально выполняться не будет.

Такой дизайн, да.
29 мар 12, 23:18    [12336989]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
iljy
Member

Откуда:
Сообщений: 8711
beg_inner,

потому что при первичной компиляции процедуры таблица не существует, но в процедуре есть код ее создания. Соответственно к коду работы с этой таблицей также применяются правила отложенной компиляции.
30 мар 12, 00:19    [12337233]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
beg_inner
Guest
Гавриленко Сергей Алексеевич
при отсутствии объекта на момент запуска процедуры по нему не проверяется ничего. В случае существования объекта на момент запуска процедуры, будет проверена возможность выполнения каждого из стейтментов

все. теперь дошло.
мой упрощенный вариант не учитывал,
что ВСЕ таблицы заранее созданы,
а внутри процедуры им предварительно делался drop.
вот так ошибка воспроизводится:
create table a.t(id int)
create table a.t1(id int)
create table a.t2(id int)
go

create proc a.test1 as
begin tran

drop table a.t1
select *
into a.t1
from a.t

drop table a.t2
select *
into a.t2
from a.t1

alter table a.t2
alter column id int not null

alter table a.t2
add constraint pk_t1 primary key (id)
commit

go

exec a.test1


Msg 8111, Level 16, State 1, Procedure test1, Line 17
Cannot define PRIMARY KEY constraint on nullable column in table 't2'.
Msg 1750, Level 16, State 0, Procedure test1, Line 17
Could not create constraint. See previous errors.


спасибо!
30 мар 12, 11:00    [12338630]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
SamMan
Member

Откуда: Moscow
Сообщений: 759
step_ks
упс, не тот код скопипастил, прошу прощения. Вот что имел ввиду


Ваш код удивляет меня точно так же как и мой исходный, а вас?
30 мар 12, 11:11    [12338727]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
SamMan
Member

Откуда: Moscow
Сообщений: 759
AndyD
Отложенное разрешение и компиляция имен

Выделите создание PK в отдельную процедуру и вызывайте ее из основной после установки NOT NULL


Сссылка (точнее текст по ней) никак не ведет нас к логическому выводу представленному второй фразой. Т.е. вообще никакой связи. Такая связь появилась бы, если бы текст по ссылке содержал что-то типа

то что могло бы быть и чего нет
Структура объектов баз фиксируется на момент начала исполнения ХП. Если ХП эту структуру меняет, то такие изменения вступают в действие с точки окончания выполнения ХП.


Но НЕТ такого! Ни в тексте, ни в жизни. Убедитесь:
USE master
go
CREATE DATABASE XXCX
go
USE XXCX
go
create proc test1 as
--BEGIN
--	alter table t DROP COLUMN kp9
--	select kp9 from t
--END
--BEGIN
--	alter table t ALTER COLUMN kp9 varchar(10) NOT NULL
--	INSERT t VALUES (2, NULL)
--END
go
create table t(id int, kp9 varchar(10))
go
EXEC test1
go
--clean up
USE master
go
DROP DATABASE XXCX


Снимайте коммент с любой пары BEGIN/END и убеждайтесь - ХПшки прекрасно и моментально меняют как структуру данных, так и свойства этой структуры. Что, в общем-то, и ожидалось - разве были какие-то запреты?

Так шо... не убедили. Повторяю вопрос: где ошибка в коде из мессаджа [12336350]? Может кто пальцем показать ее?
30 мар 12, 11:24    [12338837]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
beg_inner
Guest
SamMan,

ну ты что?
разжевали же, он смотрит то, что есть в таблице сейчас(в момент запуска),
а не то, что там ПОТОМ В ПРОЦЕДУРЕ добавят/изменят.
ему не видно, что мы там внутри добавили столбец или объявили not null,
он уже отказывается компилировать, потому что в варианте step_ks в тблице изначально нет id2,
а у меня изначально столбец null.
30 мар 12, 11:27    [12338860]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
beg_inner
Guest
SamMan
[ХПшки прекрасно и моментально меняют как структуру данных, так и свойства этой структуры. Что, в общем-то, и ожидалось - разве были какие-то запреты?


блин, ну снова смотри мой пример!
у меня изначально ошибка не воспроизводилась, потому что
ТАБЛИЦ НЕ БЫЛО, ему нечего проверять.
и конечно, внутри процедуры он менял структуру, почему нет.

как только таблицы создала СНАРУЖИ ПРОЦЕДУРЫ,
он уже не компилирует, потому что прямо перед выполнением увидел, что в таблице нужное поле null,
а я типа ключ на него вешаю!
он бы поменял внутри, но до этого дело не доходит
30 мар 12, 11:32    [12338897]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
SamMan
Member

Откуда: Moscow
Сообщений: 759
beg_inner
разжевали же, он смотрит то, что есть в таблице сейчас(в момент запуска),
а не то, что там ПОТОМ В ПРОЦЕДУРЕ добавят/изменят.
ему не видно, что мы там внутри добавили столбец или объявили not null,


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

beg_inner
потому что в варианте step_ks в тблице изначально нет id2

А как раз-таки ВОТ ЭТОТ момент документирован и возражения не вызывает:

BOL
Deferred name resolution can only be used when you reference nonexistent table objects. All other objects must exist at the time the stored procedure is created. For example, when you reference an existing table in a stored procedure you cannot list nonexistent columns for that table.


Объекты с которыми мы работаем в ХП ДОЛЖНЫ СУЩЕСТВОВАТЬ на момент написания кода этой ХП (за исключением если не существует сама таблица, коей объекты принадлежат).

Не возражаю - должны. И? Вы ответили на мой вопрос? Тогда перечитайте его (и его код) заново.
30 мар 12, 11:49    [12339017]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
SamMan
Member

Откуда: Moscow
Сообщений: 759
beg_inner
у меня изначально ошибка не воспроизводилась, потому что
ТАБЛИЦ НЕ БЫЛО, ему нечего проверять.


Так, стоп. Что бы не "копать" сразу две ветки этого не простого вопроса, договоримся: с этой точки обсуждения тестовая ТАБЛИЦА создана ДО ХП. И никак иначе. Когда эту ветку решим - переключимся на вариант "таблица не создана до ХП".
30 мар 12, 11:52    [12339035]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
daw
Member

Откуда: Муром -> Москва
Сообщений: 7381

> то что могло бы быть и чего нет
> Структура объектов баз фиксируется на момент начала исполнения ХП. Если ХП эту структуру меняет, то такие изменения
> вступают в действие с точки окончания выполнения ХП.

омг. да никакая структура нигде не фиксируется, конечно же.

> Так шо... не убедили. Повторяю вопрос: где ошибка в коде из мессаджа [12336350]

перед запуском процедуры происходит ее компиляция.
для всех объектов, которые в этот момент уже существуют, проверяется
возможность выполнения стейтментов из процедуры, затрагивающих эти объекты.
на момент компиляции таблица "из мессаджа [12336350]" существует.
при проверке стейтмента (при компиляции):

alter table t add constraint pk_t1 primary key (id)

обнаруживается, что столбец id дозволяет null-ы, а значит выполнить его нельзя,
о чем и выдается ошибка.

теперь о процедуре из текущего поста.
на момент компиляции процедуры все стейтменты внутри нее представляются выполнимыми.
alter table внутри процедуры приводит к рекомпиляции процедуры.
при рекомпиляции оператора
select kp9 from t
обнаруживается, что выполнить его нельзя - выдается ошибка.

а ошибка для оператора
INSERT t VALUES (2, NULL)

так и вообще выдается уже в момент его выполнения.

так понятно, или еще остаются какие-то вопросы?

Posted via ActualForum NNTP Server 1.5

30 мар 12, 11:54    [12339057]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
beg_inner
Guest
молодец Samman, что спросил.
твой пример прикольнее и про
"alter table внутри процедуры приводит к рекомпиляции процедуры"
я тоже не знала,
спасибо daw.

значит, перепишем
"он смотрит то, что есть в таблице сейчас(в момент запуска),
а не то, что там ПОТОМ В ПРОЦЕДУРЕ добавят/изменят"
на
"перед запуском процедуры происходит ее компиляция.
1: для всех объектов, которые в этот момент уже существуют, проверяется
возможность выполнения стейтментов из процедуры, затрагивающих эти объекты.
alter table внутри процедуры приводит к рекомпиляции процедуры, goto 1"
30 мар 12, 12:09    [12339227]     Ответить | Цитировать Сообщить модератору
 Re: что-то не то с Cannot define PRIMARY KEY constraint on nullable column  [new]
SamMan
Member

Откуда: Moscow
Сообщений: 759
daw
перед запуском процедуры происходит ее компиляция.
для всех объектов, которые в этот момент уже существуют, проверяется
возможность выполнения стейтментов из процедуры, затрагивающих эти объекты.


Ложно. Как минимум НЕ верно на 100%.

USE master
go
CREATE DATABASE XXCX
go
USE XXCX
go
create table t(id int NOT NULL)
go
create proc test1 as
BEGIN
	PRINT 'EEE'
	alter table t drop COLUMN id
END
go
EXEC test1
go
--clean up
USE master
go
DROP DATABASE XXCX


Колонка id:
-существует до запуска процедуры
-невозможность ее дропа очевидна еще до компиляции

При этом компиляция проходит и начинается исполнение ХП.

alter table t add constraint pk_t1 primary key (id)

обнаруживается, что столбец id дозволяет null-ы, а значит выполнить его нельзя,
о чем и выдается ошибка.


Согласен, это вроде как подтверждает ваш посыл, НО:

USE master
go
CREATE DATABASE XXCX
go
USE XXCX
go
create table t(id int)
go
create proc test1 as
BEGIN
	PRINT 'EEE'
	alter table t drop constraint pk_t1
END
go
EXEC test1
go
--clean up
USE master
go
DROP DATABASE XXCX


О том что НЕ существующий констрейнт нельзя дропнуть уж тем более можно было "прочухать" на этапе компиляции? Не "прочухивает" - отправляет на исполнение.

daw
alter table внутри процедуры приводит к рекомпиляции процедуры.


Это единственное что не вызывает никаких вопросов. То же самое выразил и я менее техническим термином "НЕТ никакой фиксации структуры".

daw
так понятно, или еще остаются какие-то вопросы?


Пожалуй, можно сказать что все (теперь) свелось к вопросу (одному): Как так получается, что SQL-компилятор ХПшек иногда "очень умный", а иногда...
30 мар 12, 12:45    [12339498]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить