programmera.net

programmera.net -> oracle -> normal för utskrift

info@programmera.net

Restriktioner i Oracle

1. Olika typer av restriktioner
2. ALTER TABLE
3. NOT NULL
4. CHECK()
5. UNIQUE
6. PRIMARY KEY
7. Exempel: Bestäm tabellutrymme för primär nyckel
8. PRIMARY KEY med flera kolumner
9. FOREIGN KEY
10. FOREIGN KEY, ON DELETE
11. DISABLE, ENABLE
12. DISABLE CONSTRAINT
13. DISABLE PRIMARY KEY

1. Olika typer av restriktioner

Restriktioner (Constraints) används för att begränsa vilka värden som kan lagras i en kolumn. Varje kolumn har som bekant en datatyp, som begränsar vilken typ av data som kan lagras i kolumnen. Restriktioner står för alla andra typer av begränsningar som kan tvinga kolumnernas värden att uppfylla krav som t.ex. är beroende av vad det finns för värden i de andra kolumnerna. Följande 5 typer av restriktioner finns:

Restriktion Förkortning Beskrivning

NOT NULL    C    Kolumnen kan inte innehålla NULL-värden.

CHECK(krav1)    C    Kolumnen kan bara innehålla värden som uppfyller krav1.

UNIQUE    U    Alla värden i kolumnen måste vara unika.

PRIMARY KEY    P    Kolumnen är tabellens primärnyckel, och alla värden i kolumnen måste vara unika och skilda från NULL.

FOREIGN KEY    R    Kolumnen i tabell A refererar till en annan tabell Bs primärnyckel. Det måste finnas en primärnyckel i tabell B som är identiskt med värdet i kolumnen i tabell A.

2. ALTER TABLE

Med ALTER TABLE kan man lägga till, modifiera och ta bort restriktioner. Syntaxen för detta är:
ALTER TABLE tabellnamn ADD [CONSTRAINT restriktionsnamn] { PRIMARY KEY(kolumn [, ...]) | UNIQUE(kolumn [, ...]) | CHECK( utr.) | FOREIGN KEY(kolumn [, ...]) REFERENCES tabell(kolumn) } [restriktionstillstånd] | MODIFY CONSTRAINT restriktionsnamn restriktionstillstånd | RENAME CONSTRAINT restriktionsnamn TO restriktionsnamn2 | DROP { PRIMARY KEY | UNIQUE(kolumn [, ...]) | CONSTRAINT restriktionsnamn } [CASCADE]
Där restriktionstillstånd har följande utseende:
[ [NOT] DEFERRABLE ] [ INITIALLY {IMMEDIATE | DEFERRED} ] [ ENABLE | DISABLE ] [ [NO]VALIDATE ] [ [NO]RELY ]
Alternativet RENAME är nytt för Oracle 9i.

3. NOT NULL

NOT NULL innebär att kolumnen inte får innehålla några NULL-värden. Detta är kanske den enklaste restriktionen. Det finns tre sätt att skapa restriktionen:

1. Antingen samtidigt som man skapar tabellen. Här låter vi Oracle generera namn på restriktionerna.
SQL> CREATE TABLE emp( first_name VARCHAR2(100) NOT NULL, last_name VARCHAR2(100) NOT NULL ); Table created.
Vi kan se restriktionernas genererade namn i USER_CONSTRAINTS:
SQL> COLUMN search_condition FORMAT a30; SQL> SELECT constraint_type, constraint_name, search_condition FROM user_constraints WHERE table_name='EMP'; C CONSTRAINT_NAME SEARCH_CONDITION - ------------------------------ ------------------------------ C SYS_C001109 FIRST_NAME IS NOT NULL C SYS_C001110 LAST_NAME IS NOT NULL
Dessa namn är som synes inte speciellt talande, och man bör därför undvika att låta Oracle generera namnen.

2. Vi kan ange namn själva:
SQL> CREATE TABLE emp( first_name VARCHAR2(100) CONSTRAINT nn_emp_01 NOT NULL, last_name VARCHAR2(100) CONSTRAINT nn_emp_02 NOT NULL ); Table created.
3. Eller efter tabellen:
SQL> CREATE TABLE emp( first_name VARCHAR2(100), last_name VARCHAR2(100) ); Table created. SQL> ALTER TABLE emp MODIFY (first_name NOT NULL); Table altered. SQL> ALTER TABLE emp MODIFY (last_name NOT NULL); Table altered.
Nu, om vi glömmer att sätta något värde får vi ett fel:
SQL> INSERT INTO emp (first_name) VALUES( 'Olle'); ERROR at line 1: ORA-01400: cannot insert NULL into ("R001"."EMP"."LAST_NAME")
För att ta bort NOT NULL utför vi en ny MODIFY:
SQL> ALTER TABLE emp MODIFY (last_name NULL); Table altered.
Nu går det bra att lägga till en inkomplett rad:
SQL> INSERT INTO emp (first_name) VALUES( 'Olle'); 1 rows inserted.

4. CHECK()

CHECK(Krav1) innebär att kolumnens alla värden måste uppfylla "Krav1". I tabellen "emp" vill vi att ingen ska kunna ha en högre lön än 100 000 kr. Vi kan skapa restriktionen på följande sätt:

1. Vi kan lägga på kravet då vi skapar tabellen. Här låter vi Oracle generera restriktionens namn:
SQL> CREATE TABLE emp( salary NUMBER(10) CHECK(salary < 100000) ); Table created.
2. Eller så kan vi ange namnet själva:
SQL> CREATE TABLE emp( salary NUMBER(10) CONSTRAINT ck_emp_01 CHECK(salary < 100000) ); Table created.
3. Eller så kan man lägga på restriktionen på en redan existerande tabell:
SQL> CREATE TABLE emp( salary NUMBER(10) ); Table created. SQL> ALTER TABLE emp ADD CONSTRAINT ck_emp_01 CHECK(salary < 100000); Table altered.
Vi tittar på den genererade restriktionen, för att se att allt gick rätt till:
SQL> SELECT constraint_type, constraint_name, search_condition FROM user_constraints WHERE table_name='EMP'; C CONSTRAINT_NAME SEARCH_CONDITION - ------------------------------ -------------------- C CK_EMP_01 salary < 100000
Om vi nu försöker lägga till en rad som inte uppfyller restriktionen händer följande:
SQL> INSERT INTO emp (salary) VALUES (110000); ERROR at line 1: ORA-02290: check constraint (R001.CK_EMP_01) violated
För att ta bort CHECK() från "emp" skriver vi följande:
SQL> ALTER TABLE emp DROP CONSTRAINT ck_emp_01; Table altered.

5. UNIQUE

UNIQUE är en restriktion med följande två konsekvenser:

Alla värden i kolumnen måste vara unika (om de inte är NULL).

Ett index placeras på kolumnen.

Ett värde som passar bra som unikt värde är det svenska personnummret. Det finns tre sätt att skapa restriktionen:

1. Samtidigt med tabellen, här låter vi Oracle skapa restriktionens namn:

SQL> CREATE TABLE emp(
	id_nr CHAR(10) UNIQUE
);
Table created.

2. Samtidigt med tabellen, här bestämmer vi namnet själva:

SQL> CREATE TABLE emp(
	id_nr CHAR(10) CONSTRAINT un_emp_01 UNIQUE
);
Table created.

3. Eller efter att tabellen är skapad:

SQL> CREATE TABLE emp(
	id_nr CHAR(10)
);
Table created.
SQL> ALTER TABLE emp ADD CONSTRAINT un_emp_01
	UNIQUE(id_nr);
Table altered.

I tabellen USER_CONS_COLUMNS kan vi se vilka kolumner som är inblandade i UNIQUE:

SQL> COLUMN column_name FORMAT a30;
SQL> SELECT constraint_name, column_name, position 
FROM user_cons_columns 
WHERE table_name='EMP';

CONSTRAINT_NAME                COLUMN_NAME                      POSITION
------------------------------ ------------------------------ ----------
UN_EMP_01                      ID_NR                                   1

Att ta bort UNIQUE från id_nr kan göras på två sätt:

1. Ta bort restriktionen utan att veta dess namn:

SQL> ALTER TABLE emp DROP UNIQUE (id_nr);
Table altered.

2. Ta bort restriktionen via dess namn:

SQL> ALTER TABLE emp DROP CONSTRAINT un_emp_01;
Table altered.

6. PRIMARY KEY

PRIMARY KEY är en restriktion med följande 3 konsekvenser:

Alla värden i kolumnen måste vara unika.

Kolumnen är NOT NULL.

Ett index placeras på kolumnen.

PRIMARY KEY representerar också det värde som man använder för att hitta en specifik rad i tabellen. Man kan skapa en primärnyckel på flera sätt:

1. Samtidigt som man skapar tabellen. Här låter vi Oracle generera ett namn på restriktionen:

SQL> CREATE TABLE dep(
	dep_pk NUMBER(10) PRIMARY KEY,
	dep_name VARCHAR2(100)
);
Table created.

2. Samma som ovan, men vi bestämmer själva vad restriktionen ska kallas:

SQL> CREATE TABLE dep(
	dep_pk NUMBER(10) CONSTRAINT pk_dep_01 PRIMARY KEY,
	dep_name VARCHAR2(100)
);
Table created.

3. Man kan även skapa primärnycken efter tabellen:

SQL> CREATE TABLE dep(
	dep_pk NUMBER(10),
	dep_name VARCHAR2(100)
);
Table created.	
SQL> ALTER TABLE dep ADD CONSTRAINT pk_dep_01
	PRIMARY KEY (dep_pk);
Table altered.

När man sedan vill ta bort PRIMARY KEY gör finns det 3 sätt:

1. Ta bort restriktionen via namn:

SQL> ALTER TABLE dep DROP CONSTRAINT pk_dep_01;

2. Ta bort utan att känna till namnet:

SQL> ALTER TABLE dep DROP PRIMARY KEY;

3. Ta bort PRIMARY KEY och alla FOREIGN KEY CONSTRAINTs som refererar till den aktuella primärnyckeln:

SQL> ALTER TABLE dep DROP PRIMARY KEY CASCADE;

7. Exempel: Bestäm tabellutrymme för primär nyckel

Om vi vill ha alla index i ett speciellt tabellutrymme "APP1_IDX01" är det klokt att använda separata SQL-satser för skapa alla primära nycklar. På så sätt är det lättare att få till det rätt med tabellutrymmet:
SQL> ALTER TABLE dep ADD ( CONSTRAINT cons_pk_dep PRIMARY KEY (dep_pk) USING INDEX TABLESPACE app1_idx01 ); Table altered. SQL> ALTER TABLE emp ADD ( CONSTRAINT cons_pk_emp PRIMARY KEY (emp_pk) USING INDEX TABLESPACE app1_idx01 ); Table altered.
Nu ska vi kolla vilka tabellutrymmen som dessa restriktioner (constraints) har skapat sina index i:
SQL> SELECT constraint_name, index_name FROM user_constraints; CONSTRAINT_NAME INDEX_NAME ------------------------------ ------------------------------ CONS_PK_DEP CONS_PK_DEP CONS_PK_EMP CONS_PK_EMP SQL> SELECT index_name, tablespace_name FROM user_indexes; INDEX_NAME TABLESPACE_NAME ------------------------------ ------------------------------ CONS_PK_DEP APP1_IDX01 CONS_PK_EMP APP1_IDX01
Det verkar som våra index hamnade i rätt tabellutrymme.

8. PRIMARY KEY med flera kolumner

Ibland vill man använda flera kolumner som primär nyckel. Vi tittar på ett exempel:

Tabellen emp_proj används för att knyta ihop tabellerna emp (som lagrar anställda) och proj (som lagrar projekt). Relationen mellan emp och proj är "många till många" vilket innebär att en anställd kan delta i flera projekt och ett projekt kan ha flera anställda som jobbar på det. En "många till många"-relation kräver en egen tabell, och det kan vara bekvämt att använda de två tabellernas primärnycklar som primärnyckel för emp_proj.

Här representerar kolumnerna date_from och date_to det tidsintervall då den anställde jobbade med projektet. En sammansatt primärnyckel skapas separat med de involverade kolumnerna inom parantes:

SQL> CREATE TABLE proj(
	proj_pk NUMBER(10) PRIMARY KEY,
	proj_name VARCHAR2(100)
);
Table created.
SQL> CREATE TABLE emp(
	emp_pk NUMBER(10) PRIMARY KEY,
	first_name VARCHAR2(100)
);
Table created.
SQL> CREATE TABLE emp_proj(
	proj_pk NUMBER(10),
	emp_pk NUMBER(10),
	date_from DATE,
	date_to DATE
);	
Table created.	
SQL> ALTER TABLE emp_proj ADD CONSTRAINT pk_emp_proj
	PRIMARY KEY (proj_pk, emp_pk);
Table altered.

Vi tittar på tabellen:

SQL> DESC emp_proj;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 PROJ_PK                                   NOT NULL NUMBER(10)
 EMP_PK                                    NOT NULL NUMBER(10)
 DATE_FROM                                          DATE
 DATE_TO                                            DATE

Nu tittar vi på restriktionen:

SQL> SELECT constraint_name, column_name, position 
FROM user_cons_columns 
WHERE table_name='EMP_PROJ';

CONSTRAINT_NAME                COLUMN_NAME                      POSITION
------------------------------ ------------------------------ ----------
PK_EMP_PROJ                    PROJ_PK                                 1
PK_EMP_PROJ                    EMP_PK                                  2

9. FOREIGN KEY

Vi tänker oss att vi har en applikation där varje anställd i tabellen emp måste tillhöra en avdelning som representeras av en rad i tabellen dep. Här har tabellerna en "förälder/barn"-relation, där dep är "förälder" och emp är "barn", se figur:

I detta fall vill vi att ingen rad i emp ska ha en dep_pk som inte finns i dep-tabellen, därför lägger vi en FOREIGN KEY-restriktion i emp-tabellen. För att FOREIGN KEY ska kunna användas krävs att:

De två kolumnerna måste ha samma datatyp.

emp.dep_pk får inte innehålla något värde som inte finns i dep.dep_pk.

Först skapar vi föräldertabellen dep:

SQL> CREATE TABLE dep(
	dep_pk NUMBER(10) CONSTRAINT pk_dep PRIMARY KEY,
	dep_name VARCHAR(100)
);
Table created.

Nu vill vi skapa en FOREIGN KEY som refererar till dep. Restriktionen kan skapas på flera sätt:

1. Samtidigt som tabellen (Oracle skapar namnet på restriktionen):

SQL> CREATE TABLE emp(
	emp_pk NUMBER(10),
	dep_pk NUMBER(10) REFERENCES dep(dep_pk)
);
Table created.

2. Samtidigt som tabellen (vi bestämmer namnet själva):

SQL> CREATE TABLE emp(
	emp_pk NUMBER(10),
	dep_pk NUMBER(10) CONSTRAINT fk_emp_01 REFERENCES dep(dep_pk)
);
Table created.

3. Efter tabellen:

SQL> CREATE TABLE emp(
	emp_pk NUMBER(10),
	dep_pk NUMBER(10)
);
Table created.
SQL> ALTER TABLE emp ADD CONSTRAINT fk_emp_01
	FOREIGN KEY (dep_pk) REFERENCES dep(dep_pk);

Vi ser efter så att restriktionen är på plats:

SQL> SELECT constraint_type, constraint_name, search_condition
FROM user_constraints
WHERE table_name='EMP';

C CONSTRAINT_NAME                SEARCH_CONDITION
- ------------------------------ ------------------------------
R FK_EMP_01

SQL> SELECT constraint_name, column_name, position 
FROM user_cons_columns 
WHERE table_name='EMP';

CONSTRAINT_NAME                COLUMN_NAME                      POSITION
------------------------------ ------------------------------ ----------
FK_EMP_01                      DEP_PK                                  1

Om vi nu testar att lägga till en icke existerande dep_pk i emp så får vi ett fel:

SQL> INSERT INTO emp (emp_pk, dep_pk) VALUES(1,1);
INSERT INTO emp (emp_pk, dep_pk) VALUES(1,1)
            *
ERROR at line 1:
ORA-02291: integrity constraint (R001.FK_EMP_01) violated - parent key not found

Däremot går det bra om vi lägger till ett värde i dep först:

SQL> INSERT INTO dep (dep_pk,dep_name) VALUES(1,'Management');
1 row created.
SQL> INSERT INTO emp (emp_pk, dep_pk) VALUES(1,1);
1 row created.

Det finns två sätt att ta bort FOREIGN KEY-restriktionen:

1. Via dess namn:

SQL> ALTER TABLE emp DROP CONSTRAINT fk_emp_01;
Table altered.

2. Eller genom att man gör DROP PRIMARY KEY CASCADE på primärnyckeln:

SQL> ALTER TABLE dep DROP PRIMARY KEY CASCADE;
Table altered.

Nu har båda referenserna försvunnit (pk_dep och fk_emp_01):

SQL> SELECT constraint_type, constraint_name, search_condition
FROM user_constraints
WHERE table_name in ('EMP','DEP');

no rows selected.

10. FOREIGN KEY, ON DELETE

Vad händer om man tar bort det värde som en referensnyckel pekar på? Vi återgår till tabellerna emp och dep:

Vi har följande innehåll i emp och dep:
SQL> SELECT * FROM dep; DEP_PK DEP_NAME ---------- ------------------------------ 1 Management SQL> SELECT * FROM emp; EMP_PK DEP_PK ---------- ---------- 1 1
Vi testar att ta bort raden i dep:
SQL> DELETE FROM dep WHERE dep_pk=1; DELETE FROM dep WHERE dep_pk=1 * ERROR at line 1: ORA-02292: integrity constraint (R001.FK_EMP_01) violated - child record found
Det visar sig att vår FOREIGN KEY-restriktion förhindrar satsens genomförande. Vi kan ändra detta beteende genom att lägga till en ny parameter då vi skapar restriktionen, nämligen ON DELETE. Det finns två fall:

ON DELETE SET NULL: Nu kommer referensnyckeln att sättas till NULL då värdet i föräldertabellen tas bort.

ON DELETE CASCADE: Nu kommer hela raden i barntabellen som är knuten till föräldertabellen att tas bort.

1. Vi testar ON DELETE SET NULL vilket innebär att då föräldertabellens rad försvinner sätts referensen till NULL:

SQL> ALTER TABLE emp DROP CONSTRAINT fk_emp_01;
Table altered.
SQL> ALTER TABLE emp ADD CONSTRAINT fk_emp_01
	FOREIGN KEY (dep_pk) REFERENCES dep(dep_pk) ON DELETE SET NULL;
Table altered.

Nu kan man ta bort raden in dep och emp.dep_pk sätts till NULL.

2. Vi testar nu ON DELETE CASCADE vilket resulterar i att hela raden i emp försvinner:

SQL> ALTER TABLE emp DROP CONSTRAINT fk_emp_01;
Table altered.
SQL> ALTER TABLE emp ADD CONSTRAINT fk_emp_01
	FOREIGN KEY (dep_pk) REFERENCES dep(dep_pk) ON DELETE CASCADE;
Table altered.

Vi testar att ta bort raden ur dep och ser hur det fungerar:

SQL> SELECT * FROM emp;

    EMP_PK     DEP_PK
---------- ----------
         1          1	
SQL> DELETE FROM dep WHERE dep_pk=1;
1 row deleted.
SQL> SELECT * FROM emp;
no rows selected.

11. DISABLE, ENABLE

Det går att temporärt stänga av en restriktion med DISABLE. Detta kan vara bra om man t.ex. vill flytta data mellan databaser. När man sedan vill lägga på restriktionen igen används ENABLE. Vid ENABLE utförs en kontroll av kolumnens data. Om datat inte uppfyller restriktionens krav kan inte restriktionen läggas på. Nedan visas syntaxen för DISABLE:

ALTER TABLE x DISABLE PRIMARY KEY [CASCADE];

ALTER TABLE x DISABLE UNIQUE(y);

ALTER TABLE x DISABLE CONSTRAINT z;

Nedan visas syntaxen för ENABLE:

ALTER TABLE x ENABLE PRIMARY KEY;
ALTER TABLE x ENABLE UNIQUE(y);
ALTER TABLE x ENABLE CONSTRAINT z;

12. DISABLE CONSTRAINT

Nedan testar vi DISABLE på våra tabeller emp och dep.

Vi kör DISABLE på vår referensnyckel emp.dep_pk:
SQL> ALTER TABLE emp DISABLE CONSTRAINT fk_emp_01; Table altered.
Vi kollar vad som har hänt med vår restriktion:
SQL> SELECT constraint_type, constraint_name, status FROM user_constraints WHERE table_name='EMP'; C CONSTRAINT_NAME STATUS - ------------------------------ -------- R FK_EMP_01 DISABLED
Helt korrekt verkar restriktionen vara avstängd. Vi lägger in lite data:
SQL> INSERT INTO emp (emp_pk, dep_pk) VALUES (1,1); 1 row inserted. SQL> INSERT INTO emp (emp_pk, dep_pk) VALUES (2,2); 1 row inserted.
Vilket går bra eftersom restriktionen är avstängd och tolererar allt. Vi försöker göra ENABLE på restriktionen:
SQL> ALTER TABLE emp ENABLE CONSTRAINT fk_emp_01; ALTER TABLE emp ENABLE CONSTRAINT fk_emp_01 * ERROR at line 1: ORA-02298: cannot enable (R001.FK_EMP_01) - parent keys not found
Det gick inte så bra eftersom vissa värden i emp.dep_pk inte har någon motsvarighet i dep.dep_pk. Men om vi först lägger in lämpliga data i dep:
SQL> INSERT INTO dep (dep_pk, dep_name) VALUES(1, 'Management'); 1 row inserted. SQL> INSERT INTO dep (dep_pk, dep_name) VALUES(2, 'Development'); 1 row inserted.
Så ska det gå bättre:
SQL> ALTER TABLE emp ENABLE CONSTRAINT fk_emp_01; Table altered.

13. DISABLE PRIMARY KEY

Nedan testar vi DISABLE på våra tabeller emp och dep.

Vi utgår från att det ligger 2 rader i dep och 2 rader i emp. Vi testar att stänga av restriktionen på primärnyckeln i dep:
SQL> ALTER TABLE dep DISABLE PRIMARY KEY; ALTER TABLE dep DISABLE PRIMARY KEY * ERROR at line 1: ORA-02297: cannot disable constraint (R001.PK_DEP_01) - dependencies exist
Detta går inte eftersom vi har en annan restriktion fk_emp_01 som refererar till dep_pk. Vi måste stänga av restriktionen på primärnyckeln i dep med alternativet CASCADE. Nu stängs även alla restriktioner av som refererar till dep.dep_pk:
SQL> SELECT table_name, constraint_type, constraint_name, status FROM user_constraints WHERE table_name IN ('EMP','DEP'); TABLE_NAME C CONSTRAINT_NAME STATUS ------------------------------ - ------------------------------ -------- DEP P PK_DEP_01 ENABLED EMP R FK_EMP_01 ENABLED SQL> ALTER TABLE dep DISABLE PRIMARY KEY CASCADE; Table altered. SQL> SELECT table_name, constraint_type, constraint_name, status FROM user_constraints WHERE table_name IN ('EMP','DEP'); TABLE_NAME C CONSTRAINT_NAME STATUS ------------------------------ - ------------------------------ -------- DEP P PK_DEP_01 DISABLED EMP R FK_EMP_01 DISABLED
Vi vill testa litet och tar bort några rader ur dep:
SQL> DELETE FROM dep; 2 rows deleted.
Och försöker starta restriktionerna igen:
SQL> ALTER TABLE dep ENABLE PRIMARY KEY; Table altered. SQL> ALTER TABLE emp ENABLE CONSTRAINT fk_emp_01; ALTER TABLE emp ENABLE CONSTRAINT fk_emp_01 * ERROR at line 1: ORA-02298: cannot enable (R001.FK_EMP_01) - parent keys not found
Vilket i fallet fk_emp_01 så klart inte går. Vi måste först ta bort det felaktiga innehållet ur emp:
SQL> DELETE FROM emp; 2 rows deleted. SQL> ALTER TABLE emp ENABLE CONSTRAINT fk_emp_01; Table altered.