Relationships and Constraints SQL

Ikinci makalemde yine baslangic icin cesitli bilgiler ve pratik SQL bilgisi paylasmak istedim.

create table DEPT

(DEPTNO  number(2),

 DNAME   varchar2(14),

 LOC     varchar2(13));

 Select * from dept;

 insert into DEPT (DEPTNO, DNAME, LOC)

 values (50,'MANUFACTURING','MADISON');

commit;

insert into DEPT (DEPTNO, DNAME, LOC)

 values (47,'Veysi','Gunay');

 select deptno,dname,loc from dept;

 rollback;

Data Dictionary Views

Data dictionary views DBA_,ALL_, ve USER_ adlari ile baslar.

1.       DBA_TABLES view            Database’deki butun tablolar hakkinda bilgi gosterir.              
-----  select * from dba_tables; -----

2.       ALL_TABLES view,            Sadece belirli olan Database’in tum tablo ve hangi user’e sahip ise onu gosterir.   

 -----  select * from all_tables;    -----

3.       USER_TABLES view ,     Sadece user’e ait object’leri gosterir. -----

.-----  select * from user_tables;   -----

4.       DBA_VIEWS ,                           Database icindeki butun view’ler hakkinda bilgileri gosterir.

  -----  select * from dba_views;    -----

5.       DBA_TAB_COLUMNS,    Database icindeki tablo sutunlarina ait olan datatiplerini gosterir.

   -----  select * from dba_tab_columns;     -----

Dynamic Performance Views

V$ ile baslayan adlar en cok bu view kisminda gosterilir.

1.       V$DATABASE           Database nezaman Create oldu, Database adi ve Database hakkinda bilgi icerir.

select * from v$database;

2.       V$VERSION                              Hangi software version Database’de kullaniyor gosterir.

select * from v$version;

3.       V$OPTION                 Bu görüş, veritabanı seçenekleri ve özellikleri listeler.

select * from v$option;

 Data Dictionary & Dynamic Performance

 DBA_ views genellikle cogul adlarinda kullanilir.        V$ views genellikle tekil adarinda kullanir             

(ornegin, DBA_DATA_FILES).                                                              (ornegin, V$DATAFILE).

select * from DBA_DATA_FILES;                select * from V$DATAFILE;

DBA_ views Sadece open ve calisiyor durumda          Bazi V$ views hatta database not fully open ve Uygundur.                                                                                        calisiyor durumda uygundur.

Veri genellikle DBA_ views de BUYUKHARLEDIR.       Veri genellikle V$ views  de KUCUKHARFLEDIR.

Relationships and Constraints

Constraint Type Description

Not Null                  Not null olan sutun hic birzaman null deger olarak kalamamasini saglar.

create table ri_not_null (

  a number not null,

  b number     null,

  c number

);

insert into ri_not_null values (   1, null, null);

select * from ri_not_null;

insert into ri_not_null values (null,    5,    6);

alter table ri_not_null modify a null;

Unique Key      Bu sutun icinde her deger unique olmali,ama null degerlerine izin verilir.

create table ri_unique (

  a number unique,

  b number

);

insert into ri_unique values (4,   5);

insert into ri_unique values (2,   1);

select * from ri_unique;

insert into ri_unique values (null,9);

----2 NUMARASINI TEKRAR GIRELIM---

insert into ri_unique values (2,7);

---CONSTRAINT SILMEK ICIN---

alter table ri_unique drop constraint SYS_C0011872;

---CONSTRAINT EKLEMEK ICIN----

alter table ri_unique add constraint uq_ri_b unique (b);

---MULTI COLUMN CONSTRAINT YAPMAK ICIN---

create table ri_3 (

  a number,

  b number,

  c number,

  unique (a,b)

);

--- DEGISIK BIR SEKILDE DE CONSTRAINT KULLANMAK---

create table ri_3 (

  a number,

  b number,

  c number,

  constraint uq_ri_3 unique (a,b)

);

Primary Key      Bu sutun icinde her deger  unique olmali ve null olmamalidir.

create table ri_primary_key (

  a number primary key,

  b number

);

insert into ri_primary_key values (8,9);

insert into ri_primary_key values (null,9);

select * from ri_primary_key;

--- Baska bir sekilde primary key----

create table ri_primary_key_1 (

  a number,

  b number,

  c number,

  constraint pk_name primary key (a, b)

);

Foreign Key    Bu sutunun her degeri baska bir sutuna bagli  olmali(Ayni tablonun icinde yada farkli tablonun icinde) yada ki deger null’dur.

create table EMP

 (empno    number(4) constraint EMP_PK primary key,

 ename    varchar2(10),

 job      varchar2(9),

 mgr      number(4),

 hiredate date,

 sal      number(7,2),

 comm     number(7,2),

 deptno   number(2) constraint EMP_PK_DEPTNO references DEPT(DEPTNO)

 );

 select * from dept;

 DESCRIBE DEPT;

 alter table DEPT add constraint DEPT_PK primary key (DEPTNO);

insert into EMP (empno, ename, deptno)

values (84,'JOHNSON',99);

ORA-02291: integrity constraint (SCOTT.EMP_PK_DEPTNO) violated - parent key

     not found

insert into DEPT (deptno, dname, loc)

values (99,'RESEARCH','FREEPORT');

select * from DEPT;

insert into EMP (empno, ename, deptno)

values (84,'JOHNSON',99);

select * from emp;

delete from DEPT

where deptno = 99;

ORA-02292: integrity constraint (SCOTT.EMP_PK_DEPTNO) violated - child

     record found

delete from EMP

where deptno = 99;

delete from DEPT

where deptno = 99;

update DEPT

set deptno = 1

where deptno = 99;

ORA-02292: integrity constraint (SCOTT.EMP_PK_DEPTNO) violated - child

     record found

drop table DEPT;

ORA-02449: unique/primary keys in table referenced by foreign keys

select dname, ename

from DEPT, EMP

where DEPT.deptno = EMP.deptno;

Check                       Tabloya deger girilerek o sutun icin belirli degerlere bagli olmalidir.

create table ri_check_1 (

  a number check (a between 0 and 100),

  b number

);

insert into ri_check_1  values (1,1);

select * from ri_check_1;

insert into ri_check_1 values (111,1);

alter table ri_check_1

  add constraint ch_b check (b > 50);

 

  create table ri_check_2 (

  begin_   number,

  end_     number,

  value_   number,

  check (begin_ < end_)

);

MODIFY CONSTRAINT

alter table table_name DROP CONSTRAINT const_name

alter table table_name DROP PRIMARY KEY CASCADE

alter table RENAME CONSTRAINT ri_check_2 TO ri_check_1

ALTER TABLE DISABLE CONSTRAINT ri_check_1

ALTER TABLE ENABLE CONSTRAINT ri_check_1

ENABLE VALIDATE

Kayitlar icin varsayilan durumdur,Tablolarda var olan kayitlarin constraint’e uyup uymadigini denetler.

ALTER TABLE ri_check_1 enable validate CONSTRAINT ch_b;

ENABLE NOVALIDATE

Kayitlar icin varsayilan durumdur,Tablolarda var olan kayitlarin constraint’e uyup uymadagini denetlemez.

ALTER TABLE ri_check_1 enable novalidate CONSTRAINT ch_b;

DISABLE VALIDATE

Constraint aktif olman hale getirir.(constrain icin kullanilan index’ler dusurulur.)

Ancak constraint gecerli kalir.Tablo uzerinde hicbir DML islemine izin verilmez.Cunku sonraki degisikliklerin gecerliligini denetlenemez.

ALTER TABLE ri_check_1 disable validate CONSTRAINT ch_b;

DISABLE NOVALIDATE

Bir constraint disable yapildigi zamanki varsayilan durumudur.Constraint aktif olmayan durumdadir ve var olan yada sonraki kayit girisleri denetlenemez.

ALTER TABLE ri_check_1 disable novalidate CONSTRAINT ch_b;

CONSTRAINT’LERIN BILIGILEIRNI GORMEK ICIN (DBA_CONSTRAINTS, DBA_CONS_COLUMNS)

select * from  DBA_CONSTRAINTS;

select * from  DBA_CONS_COLUMNS;