Kak programmno udalit i vosstanovit atribut

← →
Леван (2002-12-24 08:49) [0]

Privet masteram! (Sorry za Runglish!)

Podskajite, please, kak programmno (ne ENTERPRISE MANAGER-om)
Udalit i vosstanovit atribut "identity" polia v MS SQL, ne udaliaia i zatem vosstanovlivaia samo pole).
Delo v tom, chto pri "delete from IMIATABLICI vse zapisi udaliaiutsia, no pri posleduiushem INSERT-e pole identity nachinaetsia ne s 1, as poslednego znachenia do udalenia zapisei)
Zaranee blagodariu
Levan

← →
Леван (2002-12-24 10:49) [1]

V dobavlenie k voprosu:
ne xochu ispolzovat :
alter table tablename drop column columnname
alter table tablename add columnname integer identity
potomu, chto
etot columnname ispolzuetsia , kak foreign key v drugix tablicax
a vot esli bi ia mog prosto peresozdat atribut "identity"
bez udalenia COLUMNNAME, to eto bilo bi udobneee!
spasibo

← →
ЮЮ © (2002-12-24 10:53) [2]

Esli on ispolzuetsia kak foreign key v drugix tablicax, to kak on mojet nachinaetsia s 1 ???

← →
stone © (2002-12-24 10:54) [3]

SET IDENTITY_INSERT tablename ON (OFF)

← →
Anatoly Podgoretsky © (2002-12-24 10:56) [4]

Идея сама несколько странная, ну раз не требуется автоинкримент, так зачем его делать

← →
Леван (2002-12-24 11:07) [5]

to stone:
poprobuiu, spasibo
to ЮЮ i to Anatoly Podgoretsky

Delo v tom, chto pri razrabotke prilojenia neskolko raz prixoditsia "vse nachinat snachala" ia ia xochu imet takuiu vozmojnost:
nesmotria na to, podchinennie tablici UJE PUSTI, sql ne
pozvoliaet udalit pole, kotoroe v drugix tablicax ispolzuetsia , kak foreign key. Peresozdavat celikom vse tablici po sxeme:

1.udalit constraint foreign key v podxhinennix tablicax
2. peresoxdavat pole v glabnoi tablice
3. vosstanovit foreign key v podchinennix tablicax

Ne xochetsia
vsem spasibo
levan

← →
ЮЮ © (2002-12-24 11:15) [6]

IdentitySeed Property
The IdentitySeed property exposes the initial row value for an identity column.

Applies ToColumn Object

Syntax
object.IdentitySeed [= value]

Parts
object

Expression that evaluates to an object in the Applies To list

value

Long integer greater than or equal to 1

Data Type
Long

Modifiable
Read/write prior to Microsoft® SQL Server™ column creation. Read-only for existing columns.

Prototype (C/C++)
HRESULT GetIdentitySeed(LPLONG pRetVal);

HRESULT SetIdentitySeed(LONG NewValue);

← →
Леван (2002-12-24 11:21) [7]

to ЮЮ:
spasibo. ia toje videl eto v BOOKS of SQL, no, otkrovenno govoria, iz-za moix ogranichennix znanii API
ne smog ispolzovat
kak imenno v delphi napisat eto:
priiamo: "SetIdentitySeed(1);" ?
poprobuiu .
Levan

← →
Леван (2002-12-24 11:30) [8]

to ЮЮ:

k sojaleniu,
IdentitySeed Property
The IdentitySeed property exposes the initial row value for an identity column.
Read/write prior to Microsoft® SQL Server™ column creation.
Read-only for existing columns.
ne proxodit, zaraza :)
levan

← →
Леван (2002-12-24 12:04) [9]

V dobavlenie:
Enterprize Manager v Query Analizer s pomoshiu DESIGN TABLE
zaprosto snimaiu ptichku Identity, coxraniaiu tablicu, zatem zanovo ustanavlivaiu ego i opiat soxraniaiu tablicu. VSE!
Neujeli Delphi ne mojet sdelat to je samoe?

← →
stone © (2002-12-24 12:26) [10]

> zaprosto snimaiu ptichku Identity, coxraniaiu tablicu

при этом происходит:
ALTER TABLE table
ALTER COLUMN column data_type

> zatem zanovo ustanavlivaiu ego i opiat soxraniaiu tablicu

ALTER TABLE table
ALTER COLUMN column data_type IDENTITY (1,1)

← →
Леван (2002-12-24 13:11) [11]

to Stone:
interesno i prosto
poprobuiu

← →
Леван (2002-12-24 13:24) [12]

to STONE:

k sojaleniu:

alter table <tablename" alter column <columnname>
new_data_type[(precision, scale)]

mojno tolko zadat novii tip, nmo de dobavit svoistvo "identity"
ZARAZA!!

spasibo, Levan

SET IDENTITY_INSERT table1 ON -- отключает IDENTITY на время выполнения
INSERT INTO [Archive].[dbo].[Table1]([field1])
VALUES(12)
SET IDENTITY_INSERT table1 OFF -- включает IDENTITY

← →
Леван (2002-12-24 13:53) [14]

TO STONE

NET
KOGDA IDENTITY_INSERT=ON, TO V INSERT MOJNO VKLIUCHIT I ZNACHENIE DKIA "IDENTITY" POLIA
NAPRIMER, POLEDNEE ZNACHENIE EST 30
MOJNO INSERT-IT ZNACHENOE 35 (BEZ ETOGO NOVOE ZNACHENIE BILO BI 31)

KOGDA=OFF
, TO V INSERT NELZIA ZADAVAT TAKOE ZNACHENIE
T.E. NELZIA PERECHISLIT V VSTAVLIAEMIX POLIAX IMIA POLIA
"IDENTITY"

MNE KAJETSIA, CHTO YO,CHTO MNE NUJNO, DELAETSIA TOLKO CHEREZ ENTERPRIZE MANAGER, CHTO OCHEN PECHALNO!
LEVAN

при IDENTITY_INSERT=ON можно указывать любые значения поля в любой последовательности, в т.ч. дублирующиеся (если поле не PRIMARY KEY)

← →
Леван (2002-12-24 14:14) [16]

TO STONE:
MOJET BIT, NE PROVERIAL
no, k sojaleniu, mne xotelos bi, xhtobi pri avtomaticheskom dobavlenii stroki v tablicu, gde bi eto v programme ne proisxodilo, esli eta tablica pusta, to pervim znacheniem bilo 1
(a ne znachenie, kotoroe bilo poslednim pered opustosheniem....)
spasbo za soveti, no etot variant ne proshel tak, kak mne xotelos bi

est eshe odna vozmojnost
SELECT TABLE_NAME, IDENT_SEED(TABLE_NAME) AS IDENT_SEED

FROM INFORMATION_SCHEMA.TABLES

no on vozvratit i ne pozvolit pomeniat eto znachenie
nu, spasibo vsem
levan

> stone © (24.12.02 12:26)

написано правильно. что у тебя не работает? Enterprise Manager именно так и включает/выключает identity

← →
Леван (2002-12-24 16:15) [18]

to jee:
ia povtorius, no osmelius napisat eshe raz sintaksis komandi alter table:

alter table <tablename> alter column <columnname>
new_data_type[(precision, scale)]

t.e. mojno tolko zadat novii tip, precision,scale, no nelzia dobavit svoistvo "identity"
imenno ob etom i rugaetsia delphi, esli napisat v sql etu komandu i zapustit na vipolnenie!

kstati, a aokuda vi znaete, chto pri sniatii i ustanovki svoistva "identity" Enterprize Manager imenno tak i postupaet (imenno takoi sql zapros vidaet k baze?)
:)

Levan

Kak programmno udalit i vosstanovit atribut Найти похожие ветки