Jun

2006

Oracle Databaser - Del III, General Database Struktur: Datatyper og Constraints

Skrevet af: Karsten Birch-Johansen i Oracle

0 kommentarer : Blogs der linker : Tilføj til del.icio.us : Tilmeld dig feeds herfra : E-Mail posten

Vi ved, på nuværende tidspunkt, lidt generelt, omkring databasen, dens strukturer, hvordan man, så og sige, kommunikerer med den via SQL (Structured query language), som udgangspunkt, er det på tide, vi kigger lidt på hvilke datatyper, og reglsæt en Oracle database tilbyder.

Oracle Databaser - Del I, General Database Struktur: memory, prosesser, filer

Oracle Databaser - Del II, General Database Struktur: DML, DDL- og DCL

Oracle Datatyper

Oracle's datatyper er som følger;

Karakter strenge

CHAR (størrelse) – Et fastlængde felt af bokstaver. Det største, feltet kan indeholde er 2000 karakterer. Hvis der ikke angives længde ved oprettelse antager CHAR sin defaulte værdi, nemlig 1 byte.
NCHAR (størrelse) – Et fastlængde felt af bokstaver, hvor karaktersæt er bestemt ved definitionen. Maximum størrelse er 2000 bytes - og feltet kan håndterer multibyte karaktersæt.
VARCHAR2 (størrelse) – Et variabel længde felt af bokstaver. Største antagelse er 4000 karakterer.
NVARCHAR2 (størrelse) – Et variabel længde felt af bokstaver, hvor karaktersæt er bestemt ved definitionen. Maximum størrelse er 4000 bytes - og feltet kan håndterer multibyte karaktersæt.

NOTE: VARCHAR2 datatypen er efterfølgeren af VARCHAR. Det anbefales at benytte VARCHAR2, der er et variable længde antal karkterer - fremfor VARCHAR.

LONG – Et variabel længde felt af bokstaver. Største antagelse er 2GB.

Number

NUMBER (præcision, størrelse) – Et variable størrelse, hvor præcision er mellem 1 og 38 og størrelse er mellem -84 og 127. Et NUMBER felt med kun en parameter er, NUMBER (præcision), hvor parameteren specificerer præcisionen af tallet. En NUMBER datatype uden paramtere er sat til dens maximum størrelse.

Dato og Tid

DATE – Et fast størrelse felt på 7 bit, til at indeholde en dato. Noter; at tid gemmes som en del af datoen. Default format er DD-MON-YY og kan overskrives af NLS_DATE_FORMAT.
TIMESTAMP (præcision) – En variabel størrelse fra 7 til 11 byte, der repræsenterer en dato/tid værdi, begge inklusive. Præcisionen angiver antal tal i SECOND feltet. Præcisionen af dette felt antager default 6, men kan angives mellem 0 og 9.
TIMESTAMP (præcision) WITH TIME ZONE – En fast størrelse på 13 bytes, der repræsentere dato/tid plus tidszone. Der er 2 måder man kan sætte tidszonen. Den første er at benytte et UTC offset, f.eks ‘+10:0’, eller ved at benytte regionsnavn som f.eks ‘Australia/Sydney’.
TIMESTAMP (præcision) WITH LOCAL TIME – En variabel størrelse fra 7 til 11 byte. Denne datatype er den samme som TIMESTAMP WITH TIME ZONE datatypen. Forskellen er at den er normaliseret til databasens tidszone når den gemmes.

Intervaler

INTERVAL DAY (dag_præcision) TO SECOND (sekund_præcision) – En fast størrelse værdi der repræsenterer en periode i tid. Den inkluderer dag/timer/minutter og sekunder.
INTERVAL YEAR (år_præcision) TO MONTH - En fast størrelse værdi der repræsenterer en periode i tid. Den inkluderer år og måneder..

Binære

RAW (størrelse) – Et variabel størrelse felt af raw binære data. Maximum størrelse er 2000 bytes.
LONG RAW - Et variabel størrelse felt af raw binære data. Maximum størrelse er 2 GB.
BLOB – Binary Large Object er et felt der indeholder ustrukturerede binære data. Maximum kapasitet er 4 GB.
CLOB – Character Large Object er et felt der indeholder enkelt byte karakter data. Maximum kapasitet er 4 GB.
NCLOB – National Character Large Object er et felt der indeholder enkelt byte eller multibyte karakter data afhængigt af NLS. Maximum kapasitet er 4 GB.
BFILE – En ekstern binær fil. Maximum kapasitet er 4 GB. Størrelsen er dog begrænset af operativ systemet.

Rækker

ROWID – En datatype der indeholder binære data til identificering af en række.

Hvert ROWID er:

6 bytes for normale indexer- og ikke partisionerede tabeller, lokale indexer på partisionerede tabeller samt række pointere for kædede/migrerede rækker.

10 bytes for globale indexer på partisionerede tabeller.

UROWID – Universal ROWID er en datatype til at gemme logiske og fysiske ROWID værdier såvel som "foreign" tabeller der tilgås gennem gateways.

Alternativer for ANSI Standard Datatypeer

ANSI standard datatyper kontra Oracle definerede datatyper. Se nedenfor, for oracle datatype alternativer fremfor ANSI.

ANSI Standard	Oracle Datatype
CHARACTER and CHAR	CHAR
CHARACTER VARYING and CHAR VARYING	VARCHAR2
NUMERIC, DECIMAL, DEC, INTEGER, INT and SMALLINT	NUMBER
FLOAT, REAL, DOUBLE PRECISION	FLOAT

Abstrakte datatyper

I Oracle, kan man lave sine egne datatyper. Abstrakte datatyper tillader Oracle at indeholde en bred vifte af datatyper. For at muliggøre dette skal man lave en datatype som et objekt. Et objekt er udgjort af en eller flere datatyper.

Eksempel på en abstrakt datatype

Lad os sige at vi vil lave en datatype der opdeler person adresser. Kan vi definere den abstrakte datatype som;

CREATE OR REPLACE TYPE person_adresse AS OBJECT
(
          v_doernummer                      NUMBER,
          v_gadenavn                         VARCHAR2(30),
          v_by                                   VARCHAR2(50),
          v_postkode                          NUMBER
);

Når vi efterfølgende laver en tabel der repræsenterer denne datatype skal værdier indsættes som;

person_adresse(21, ‘Dag Hammer Skjolds Alle’, ‘København’, 2100)

Constraints

Udover ovenstående datatyper, tillader Oracle at man kan definerere reglsæt, der opretholder definerede data kvalitet. Hvis en bruger eller et program - herefter forsøger at indsætte data der ikke overholder disse regler, afviser Oracle operationen.

Med andre ord,- det der før i tid lå i checks, i koden - kan fastsættes og bestemmes på database niveau. Dvs. det en udvikler mod et veldefineret Oracle system - blot behøver, er at lave sin applikation - og håndtere fejl situationen når den opstår, men ellers blot sende sine insert, update, delete udtryk afsted, uden at skulle kode sig ud af om hvorvidt der allerede findes en post i tabellen med dit og dat unikke id.

Der findes 5 typer constraints i Oracle

NOT NULL En kolonne i en tabel kan specificeres til at være at typen NOT NULL. Det vil herefter ikke være muligt at indsætte NULL værdier for kolonnen. Default er NULL for kolonner. Så i følgende CREATE table udtryk kan der indsættes NULL ind i kolonnen C.

create table kb_not_null (
a number not null,
b number null,
c number
);

insert into kb_not_null values (   1, null, null);
insert into kb_not_null values (   2,    3,    4);
insert into kb_not_null values (null,    5,    6);

De første 2 poster kan indsættes, den tredie giver følgende fejl:

ORA-01400: cannot insert NULL into ("KBIRCH"."KB_NOT_NULL"."A").

NOT NULL/NULL constrainten kan modificeres/ændres med;

ALTER TABLE kb_not_null MODIFY a NULL;

Efter den ændring kan kolonnen igen indeholde NULL værdier.

UNIQUE KEY
Unique key constraint tillader ikke 2 af samme værdier i en kolonne. Hvis constrainen omfatter 2 eller flere kolonner (sammensat nøgle), er kombinationer af samme slags heller ikke tilladt.

create table kb_unique (
a number unique,
b number
);

Det at kolonnen er unique - tillader somsagt ikke 2 slags af samme værdi indsættes.

insert into kb_unique values (4,   5);
insert into kb_unique values (2,   1);
insert into kb_unique values (9,   8);
insert into kb_unique values (6,   9);
insert into kb_unique values (null,9);
insert into kb_unique values (null,9);

Tilgengæld kan man på en unique kolonne udemærket indsætte NULL flere gange, uden problemmer. Hvis der indsættes en værdi der allrede eksistere, sker følgende;

insert into kb_unique values (2,7);

ORA-00001: unique constraint (KBIRCH.SYS_C001463 violated).

Hvert constraint har et navn. Med mindre andet er angivet hedder det SYS_C000_et_eller_andet i databasen. Det er en god ide - af hensyn til b.la. fejlfinding, design forståelse m.v at navngive sine constraint - mere herom senere.

Et constraint der ikke bruges - kan selfølgelig fjernes igen;

alter table kb_unique drop constraint sys_c001463;

Man kan selfølgelig også tilføje constraints til eksisterende tabeller.

alter table kb_unique add constraint uq_kb_b unique (b);

Det unikke index eller constraint kan også være sammensat - strække sig over flere kolonner;

create table kb_3 (
a number,
b number,
c number,
unique (a,b)
);

Som nævnt er det et god ide at navngive sine constraints; Følgende opretter en unique constraint, på kolonnerne a og b og navn giver constraintet UQ_KB_3. I navnet, forsøger jeg at sige UQ, betyder unique, KB_3 er tabel navnet. Det er selfølgelig op til den enkeltes "husholdning" mht. navngivninger og blot et eksempel.

create table kb_3 (
a number,
b number,
c number,
constraint uq_kb_3 unique (a,b)
);

PRIMARY KEY
Primary key er UNIQUE samt NOT NULL sat sammen. Hver tabel kan max have en primary key. Efter en primary key er oprettet kan den refereres til via FORREIGN KEY's.

create table kb_primary_key (
a number primary key a_PK_kb_primary_key,
b number
);

Primary keys kan også explicit navngives/oprettes på tabel niveau fremfor på kolonne niveau. Følgende tabel oprettelse, laver en tabel med en primary key, hvis navn er pk_name;

create table kb_primary_key_1 (
a number,
b number,
c number,
constraint pk_name primary key (a, b)
);

FOREIGN KEY
Et foreign key constraint og så kaldet (referential integrity constraint), på en kolonne sikre at værdien i den kolonne findes som primær nøgle i en anden, eller samme tabel. Hvis en tabel har en foreign key der refererer en anden tabel, kan reference tabellen kun droppes med en DROP TABLE .... CASCADE. Det er ikke muligt at etablerere en foreign key mod et GLOBAL TEMPORARY TABLE. Hvis du forsøger, giver Oracle en fejlmeddelse.

ORA-14455: attempt to create referential integrity constraint on temporary table.

CHECK
Et check constraint tillader at man for constrainten opsætter et forhold for værdier der indsættes. Hvis man skal have mere avancerede forhold/regler skal man benytte en TRIGGER. Følgende table tillader vha. check kun at værdier er mellem 0 og 100 for kolonnen a;

create table kb_check_1 (
a number check (a between 0 and 100),
b number
);

Check constraints kan sagtens tilføjes eksisterende tabeller;

alter table kb_check_1
add constraint ch_b check (b > 50);

Det er også muligt at få check constriants til at checke for på værdier i flere kolonner. Følgende sikre at værdien i kolonnen begin_ er mindre end værdien i end_

create table kb_check_2
begin_   number,
end_     number,
value_   number,
check (begin_ < end_)
);

Det at slå constraints fra kan gøres ved navngine constraints samt anonyme constraints. F.eks;

create table foo (bar number, baz number, unique (bar, baz));
alter table foo disable unique (bar, baz);

Eller

create table foo (bar number, baz number, constraint uq_foo unique (bar, baz));
alter table foo disable constraint uq_foo;

Mht. Reglsæt kunne man forestille sig, at man havde lavet en applikation, således at HR afdelingen, ved ansættelse af personer i et firma - Indtastede navn, adresse, konto nummer osv. Ligeledes specificerede man hvilken afdeling - pågældende medarbejder skulle tilhøre.

Således kunne man sige af afdelings tabellen så sådan ud;

create table afdeling (
afdelingsnavn VARCHAR2(50) constraint afdeling_afdnavn_PK PRIMARY KEY);

insert into afdeling values ('Salg');
insert into afdeling values ('Computer');
insert into afdeling values ('Direktion');
insert into afdeling values ('Support');

commit;

create table medarbejder (
fornavn VARCHAR2 (100),
efternavn VARCHAR2 (100),
konto_nummer VARCHAR2 (12),
afdelingsnavn VARCHAR2(50));

ALTER TABLE medarbejder
ADD CONSTRAINT medarbejder_fk FOREIGN KEY (afdelingsnavn) REFERENCES afdeling(afdelingsnavn);

insert into medarbejder values ('Karsten','Birch-Johansen','10101010-10','Salg');
insert into medarbejder values ('Zara','Birch-Johansen','10101010-12','Hardware');

Den allersidste insert ville fejle med;

ORA-02291: integrity constraint (KBIRCH.MEDARBEJDER_PK) violated - parent key not found

Der findes ikke en afdeling der hedder Hardware.