Adaugarea, modificarea si stergerea informatiilor din tabele

Adaugarea, modificarea si stergerea informatiilor din tabele

Operatiile uzuale asupra bazelor de date - adaugarea, modificarea si stergerea se realizeaza prin intermediul instructiunilor INSERT, UPDATE si DELETE. Aceste instructiuni pot sa opereze pe una sau mai multe linii dintr-o tabela si pot fi aplicate unei "vederi logice" peste mai multe tabele.

Pentru a modifica anumite caracteristici ale tabelei se utilizeaza comanda ALTER TABLE.

Sintaxa comenzii INSERT este:

INSERT [ into ] tinta [ ( coloane ) ] [ DEFAULT | valori | instructiune ]

tinta = poate fi numele unei tablele sau unei vederi, ceea ce inseamna ca inserarea se va face in tabela, respectiv in tabelele ce stau la baza vederii.

coloane = precizeaza in ce coloane se insereaza informatiile, ordinea informatiilor primite si daca exista coloane excluse de la inscrierea datelor.

Daca in comanda INSERT se specifica o coloana si nu se precizeaza informatia pentru coloana respectiva, iar aceasta nu admite valorea Null, operatia va esua.

Pentru a insera o linie care nu contine altceva decat valori implicite stabilite ca si constrangeri Default asociate tabelului, se poate folosi clauza Default a instructiunii INSERT.

In acest caz, in coloanele tabelei vor fi rescrise valorile implicite, sau Null.

O alta varianta pentru a insera informatie intr-o tabela este sa folosim o instructiune SELECT pentru a obtine valorile de inserat.

Exemple:

Inserare de inregistrari in tabela client:

insert into client (cod_client, den_client) values (1, 'client1')

insert into client (cod_client, den_client, judet) values (2, 'client2', 'ot')

insert into client (cod_client, den_client, judet) values (3, 'client3', 'oo')

Ultima comanda INSERT nu se va putea executa pentru ca este incalcata constringerea CHECK definita pe coloana judet. Va fi afisat mesajul de eroare de mai jos:

Server: Msg 547, Level 16, State 1, Line 1

INSERT statement conflicted with COLUMN CHECK constraint

'CK__client__judet__7DEDA633'.

The conflict occurred in database 'master', table 'client', column 'judet'.

Inserare de inregistrari in tabela facturi:

insert into facturi (valoare, cod_client) values (1200000, 1)

insert into facturi (valoare, cod_client) values (2000800, 1)

insert into facturi (valoare, cod_client) values (2000000, 2)

Pentru modificarea valorilor inscrise in liniile unei tabele se foloseste instructiunea UPDATE.

Actualizarea directa a liniilor dintr-o tabela se numeste actualizare imediata.

A doua modalitate de modificare a liniilor dintr-o tabela este actualizarea indirecta sau intirziata care se face prin stergerea liniei de modificat si apoi inserarea sa ca o linie noua.

Sintaxa comenzii UPDATE este :

UPDATE nume_tabela

SET nume_col_1=valoare, nume_col_n=valoare

WHERE nume_col operator_comparare valoare

Clauza WHERE se foloseste pentru a identifica liniile de modificat, iar daca lipseste, se modifica toate liniile tabelului. Instructiunea UPDATE permite atat modificarea tabelelor cat si a vederilor. In clauza SET a instructiunii UPDATE se pot utiliza expresii si cuvintele cheie DEFAULT si NULL. Pentru a inscrie valoare Null intr-o coloana, trebuie ca ea sa admita aceasta valoare.

Stergerea liniilor se face cu instructiunea DELETE care are sintaxa.

Delete [ FROM ] nume_tabela

Where nume_coloana=valoare

Stergerea liniilor ca si actualizarea lor se poate face si prin intermediul vederilor.

Daca nu se specifica clauza WHERE, vor fi sterse toate liniile din tabela. Daca se doreste stergerea tuturor liniilor din tabela, pastrand totusi tabela intacta se utilizeaza instructiunea:

TRUNCATE TABLE nume_tabela

Avantajul acestei instructiuni este acela ca stergerea liniilor se face mai repede decat in cazul unei instructiuni DELETE echivalente. Instructiunea TRUNCATE este mai rapida pentru ca sterge pagini de informatii care contin mai multe linii la un loc, in timp ce DELETE sterge fiecare linie in parte. Liniile sterse cu TRUNCATE nu pot fi recuperate pentru ca, spre deosebire de DELETE, aceasta instructiune nu pastreaza o copie a liniilor sterse. Stergerea liniilor cu instructiunea TRUNCATE, nu este inregistrata in jurnalul de tranzactii.

Pentru a inlatura definitiv o tabela impreuna cu toate liniile pe care aceasta le contine, se poate folosi comanda:

DROP TABLE nume_tabela.

Comanda ALTER TABLE

Permite modificarea, adaugarea sau stergerea de coloane intr-o tabela, activarea sau dezactivarea unor constrangeri. Asupra acestor operatii raman o serie de restrictii.

Sintaxa este:

ALTER TABLE table
ROWGUIDCOL
        }
    ]
    | ADD
        [,n]
    | [WITH CHECK | WITH NOCHECK] ADD
        [,n]
    | DROP
        [,n]
    | CONSTRAINT
        
    | TRIGGER
        
}

<column_definition> ::=
[ [ DEFAULT constant_expression ]
| [ IDENTITY [(seed, increment ) [NOT FOR REPLICATION] ] ]
]
[ ROWGUIDCOL ]
[ <column_constraint>] [ n]

<column_constraint> ::= [CONSTRAINT constraint_name]

        [CLUSTERED | NONCLUSTERED]
        [WITH FILLFACTOR = fillfactor]
        [ON ]]
     ]
    | [    [FOREIGN KEY]
        REFERENCES ref_table [(ref_column) ]
        [NOT FOR REPLICATION]
     ]
    | CHECK [NOT FOR REPLICATION]
        (logical_expression)
}

<table_constraint> ::= [CONSTRAINT constraint_name]

        [ CLUSTERED | NONCLUSTERED]
        
        [ WITH FILLFACTOR = fillfactor]
        [ON ]
    ]
    |    FOREIGN KEY
    [(column[,n])]
    REFERENCES ref_table [(ref_column[,n])]
    [NOT FOR REPLICATION]
    |    DEFAULT constant_expression
    [FOR column]
    |    CHECK [NOT FOR REPLICATION]
        (logical_expression)
}

Portiunea ADD din comanda ALTER TABLE se poate folosi pentru adaugarea unei noi coloane in tabela, a unei coloane calculate sau a unei constrangeri la nivel de tabela. La utilizarea comenzii SELECT, se afiseaza toate linile existente inclusiv valorile Null inscrise in noua coloana. Coloana adaugata poate avea atributul Identity. Pentru o coloana existenta nu se poate stabili atributul Identity.

Portiunea ALTER COLUMN din comanda ALTER TABLE poate fi utilizata pentru a modifica o anumita coloana. Coloana modificata nu poate fi:

o coloana cu tipul de date text, image, ntext, timestamp;

coloana calculata sau folosita in cadrul unei coloane calculate;

coloana replicata;

parte a unei chei primare sau constrangeri referentiale;

folosita intr-o constrangere de validare sau unicitate;

are asociata o valoare implicita;

parte a unui index, mai putin daca tipul de date este varchar sau varbinary.

new_data_type este noul tip de date pentru coloana modificata

Pentru stergerea unei coloane dintr-o tabela, sau a unei constrangeri se poate folosi portiunea DROP din sintaxa instructiunii ALTER TABLE.

Utilizarea clauzei WITH NOCHECK

In cadrul instructiunii ALTER TABLE se poate specifica o clauza WITH NOCHECK pentru a preveni aplicarea unei constrangeri CHECK sau FOREIGN KEY asupra liniilor deja existente in tabela. Constrangerile adaugate cu o instructiune ALTER TABLE care contine o clauza WITH NOCHECK sunt valabile doar pentru liniile modificate sau inserate ulterior.

Clauza WITH NOCHECK se utilizeaza pentru a grabi executia comenzii ALTER TABEL, dar numai in cazul in care datele deja existente nu intra in conflict cu noile constrangeri.

WITH NOCHECK nu se poate folosi pentru a trece peste verificarile initiale ale constrangerilor PRIMARY KEY si UNIQUE.

In mod implicit, SQL Server aplica o constrangere atat liniilor deja existente intr-o tabela, cat si celor care sunt adaugate sau modificate ulterior. Daca datele care exista intra in contradictie cu constrangerea adaugata, comanda ALTER TABLE va esua.

Exemple:

Se adauga la tabela client coloana localitate

alter table client add localitate varchar (20)

Se sterge coloana localitate din tabela client

alter table client drop column localitate

Se modifica dimensiunea coloanei den_client de la 30 la 40 de caractere

alter table client alter column den_client varchar (40) not null

Incercarea de a schimba tipul de date pentru coloana care este cheie primara esueaza.

alter table client alter column cod_client smallint

apare mesajul:

Server: Msg 4922, Level 16, State 3, Line 1

ALTER TABLE ALTER COLUMN cod_client failed because PRIMARY KEY CONSTRAINT

PK__client__6BAEFA67 accesses this column.

Constrangerea Primary key nu a putut fi inlaturata pentru ca este referita de o cheie externa:

alter table client drop constraint `PK__client__6BAEFA67

apare mesajul:

Server: Msg 3725, Level 16, State 1, Line 1

The constraint `PK__client__6BAEFA67` is being referenced by table `facturi`,

foreign key constraint `c1`.

Server: Msg 3727, Level 16, State 1, Line 1

Could not drop constraint. See previous errors.

Constrangerea CHECK asupra coloanei judet (se pot introduce numai valorile `dj` si `ot` ) poate fi inlaturata

alter table client drop constraint CK__client__judet__6CA31EA0

Ca urmare, este permisa urmatoarea inserare:

insert into client values (`altclient`, `ag`, `pitesti`)

Refacerea constrangerii CHECK asupra coloanei judet nu se poate realiza datorita datelor existente deja in coloana (exista valoarea `ag`)

alter table client add constraint cccc check (judet in (`dj` , `ot` ))

apare mesajul:

Server: Msg 547, Level 16, State 1, Line 1

ALTER TABLE statement conflicted with TABLE CHECK constraint `cccc`. The conflict occured in database `eda2`, table `client`.

Rezolvarea problemei de mai sus se face cu clauza WITH NOCHECK:

alter table client with nocheck add constraint cccc check (judet in (`dj` , `ot` ))