Oracle DB Identity Columns

Herkese Selam,

Bu yazıda sizlerle Oracle DB 12c R1 ile gelen yeni özelliklerinden biri olan identity column seçeneğinden bahsedeceğim umarım farkındalık anlamında faydalı bir yazı olur.

1966595Oracle’da bir tablo yaratırken Primary Key alanını built-in olarak otomatik artan bir şekilde olmasını sağlayamamak her zaman için hayıflandığımız konuların başında geldi. Bu ihtiyacımızı ya tabloya insert esnasında bir sequence’den manuel çekerek yada tabloya bir trigger tanımlayıp her insert esnasında p.k kolonunu yine bir sequence yardımı ile otomatik arttırarak bu ihtiyacımızı çözdük. Oracle 12c R1 sürümünde bu operasyonu otomatikleştirecek alt yapı geliştirdi ve kullanıma açtı. Artık otomatik olarak artabilen sayısal kolonlar yaratabiliyor durumdayız.

Identity kolon tipi arka tarafta 3 farklı özelliği destekler durumda geliştirilmiş. Hangisi işimize uygun ise seçip kullanabiliyoruz.

Bu özellikler;

1- GENERATED ALWAYS AS IDENTITY

2- GENERATED BY DEFAULT AS IDENTITY

3- GENERATED BY DEFAULT ON NULL AS IDENTITY

Bu özelliklerin detayını incelemeden önce identity tipinde yaratılan kolonlar için bazı kısıtlamalar var. Bu tip kolonları yaratırken aşağıdaki durumları mutlaka göz önünde bulundurmamız gerekiyor.

  • Bir tabloda yalnızca tek identity tipinde kolon olabilir.
  • Identity olarak yaratacağımız kolon mutlaka sayısal bir veri tipinde tanımlanmalı.
  • Identity olarak tanımladığımız bir kolona default değer tanımı yapamıyoruz.
  • Create table as select ile kopyasını aldığımız bir tabloda eğer identity tipte bir kolon var ise bu kolonun identity özelliği yeni yaratılan tabloya taşınmaz.
  • Identity tipinde bir kolon tanımladığımız anda bu kolon için NOT NULL constraint implicit olarak tanımlanmış olur.

Şimdi örnekler ile farklı özelikli Identity kolon tiplerini inceleyelim.

1- GENERATED ALWAYS AS IDENTITY:  Bu özellikli yarattığımız identity kolonuna dışardan müdahale edemiyoruz. Şimdi bir örnek ile inceleyelim.

CREATE TABLE tab_new_identity_col_table (
  tab_pk      NUMBER GENERATED ALWAYS AS IDENTITY,
  tab_name    VARCHAR2(50),
  tab_prop    NUMBER,
  tab_add     VARCHAR2(50)  
);


insert into tab_new_identity_col_table(tab_pk,tab_name,tab_prop,tab_add) values (10,'deneme',99,'dene12'); 

insert into tab_new_identity_col_table(tab_name,tab_prop,tab_add) values ('deneme',99,'dene12'); 

Evet yukarıdaki kod bloğumuzu çalıştırdıktan sonra şimdi çalıştırma loglarını inceleyelim.

Table TAB_NEW_IDENTITY_COL_TABLE created.

Error starting at line : 9 in command -
insert into tab_new_identity_col_table(tab_pk,tab_name,tab_prop,tab_add) values (10,'deneme',99,'dene12')
Error at Command Line : 9 Column : 40
Error report -
SQL Error: ORA-32795: cannot insert into a generated always identity column
32795.0000 -  "cannot insert into a generated always identity column"
*Cause:    An attempt was made to insert a value into an identity column
           created with GENERATED ALWAYS keywords.
*Action:   A generated always identity column cannot be directly inserted.
           Instead, the associated sequence generator must provide the value.

1 row inserted.

Çalıştırma loglarından da göreceğimiz üzere ilk yaptığımız insert’de identity tanımlanmış kolona dışardan değer gönderdiğimiz için sistem tarafından hata aldık. Ancak ikinci insert cümlesinde böyle bir müdehale olmadığı için 2. cümlemiz başarılı bir şekilde çalıştı ve identity kolonumuz otomatik artarak başladı.
fnl

 

2- GENERATED BY DEFAULT AS IDENTITY: Bu seçenek ile identity kolonumuzu dışardan bir değer ile doldurabiliyoruz ancak yine null geçemiyoruz. Şimdi benzer örnekle bunuda inceleyelim.

 

CREATE TABLE tab_new_identity_col_table (
  tab_pk      NUMBER GENERATED BY DEFAULT AS IDENTITY,
  tab_name    VARCHAR2(50),
  tab_prop    NUMBER,
  tab_add     VARCHAR2(50)  
);


insert into tab_new_identity_col_table(tab_pk,tab_name,tab_prop,tab_add) values (1,'deneme',99,'dene12'); 

insert into tab_new_identity_col_table(tab_name,tab_prop,tab_add) values ('deneme',99,'dene12'); 

Evet yukarıdaki kod bloğumuzu çalıştırdıktan sonra şimdi çalıştırma loglarını inceleyelim.

Table TAB_NEW_IDENTITY_COL_TABLE created.


1 row inserted.


1 row inserted.

Evet göründüğü üzere 2 cümlemizde başarılı bir şekilde insert edildi. Ancak elle verdiğimiz değer ile identity kolonunun ilk otomatik ürettiği değer aynı olduğu için iki kayıdın ilk değerleri aynı olmuş oldu. Eğer identity olarak tanımlamış olduğumuz kolon primary key olsaydı, 2. cümlemiz p.k constraintini çiğnediği için hata alacaktı.
fnl

 

 

3- GENERATED BY DEFAULT ON NULL AS IDENTITY: Indentity kolonumuzu bu opsiyon ile yaratırsak identity tanımladığımız kolona null değer gönderdiğimiz anda da hata almadan kolon değerinin otomatik olarak artarak devam etmesini sağlayabiliyoruz.

 

CREATE TABLE tab_new_identity_col_table (
  tab_pk      NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  tab_name    VARCHAR2(50),
  tab_prop    NUMBER,
  tab_add     VARCHAR2(50)  
);


insert into tab_new_identity_col_table(tab_pk,tab_name,tab_prop,tab_add) values (null,'deneme',99,'dene12'); 

insert into tab_new_identity_col_table(tab_name,tab_prop,tab_add) values ('deneme',99,'dene12'); 

Evet yukarıdaki kod bloğumuzu çalıştırdıktan sonra şimdi çalıştırma loglarını inceleyelim.

Table TAB_NEW_IDENTITY_COL_TABLE created.


1 row inserted.


1 row inserted.

Evet 2 insert cümlemizinde başarılı bir şekilde çalıştığını gördük. Şimdi tablomuzun durumuna göz atalım.
fnl

Evet 3 farklı özellikte nasıl kullanılabildiğini inceledik. Şimdide arka planda neler olduğundan bahsedelim.
Biz identity tipinde bir kolon yarattığımız anda bu otomatik değeri üretecek bir adet sequence yaratıyor bu kolona özel.
Daha sonra her yeni bir kayıtda bu sequence’den değeri üreterek tabloya yazma işlemini gerçekleştiriyor. Identity kolonumuz için üretilen sequence’e data dictionary’den aşağıdaki sorguyu yazarak ulaşabiliriz.

select * from ALL_TAB_IDENTITY_COLS;

fnl

Evet bu dictionary sorgundan da anlaşılacağı üzere arkada tarafta bir sequence yaratıldığını görüntüledik. Bu sırada akıllara şu soru gelebilir? Biz identity kolonunu yaratırken sequence’in çalışma mekanizmasını özelleştirebilirmiyiz? Yani sequence’in birer birer değilde beşer beşer artarak gitmesini sağlayabilirmiyiz? Cevap: Evet. Aşağıdaki örnekle bunu görselleştirelim.

CREATE TABLE tab_new_identity_col_table (
  tab_pk      NUMBER GENERATED ALWAYS AS IDENTITY(START WITH 1 INCREMENT BY 5),
  tab_name    VARCHAR2(50),
  tab_prop    NUMBER,
  tab_add     VARCHAR2(50)  
);


insert into tab_new_identity_col_table(tab_name,tab_prop,tab_add) values ('deneme',99,'dene12'); 

insert into tab_new_identity_col_table(tab_name,tab_prop,tab_add) values ('deneme',99,'dene12'); 

insert into tab_new_identity_col_table(tab_name,tab_prop,tab_add) values ('deneme',99,'dene12'); 

fnl
Evet görüldüğü üzere istediğimiz gibi identity kolonunu üreten sequence’i özelleştirebiliyoruz.

Evet bu yazıda sizlerle 12c ile gelen yeni özelliklerden biri olan identity tipinde kolonu ve özelliklerini derinlemesine inceledim umarım farkındalık anlamında faydalı bir yazı olmuştur.

KAYNAKLAR

Advertisements

About ... from Emrah METE

Bilgisayar Mühendisi
This entry was posted in Oracle, Uncategorized and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s