modif tabele

Modificarea tabelelor (structura si constrangerile)

• Adaugarea unui nou camp in structura tabelului
     ALTER TABLE table_name ADD (field_name type, …);
     ALTER TABLE customer ADD (cname VARCHAR2(20));
• Adaugarea unei chei primare (numai daca valorile din camp sunt unice)
• Adaugarea de constrangeri UNIQUE sau conditii CHECK (numai daca valorile din campurile corespunzatoare satisfac respectivele conditii)
• Adaugarea unei chei straine (numai daca valorile din campul considerat sunt NULL sau exista in tabelul referit)
• Stergerea unei chei primare (se sterg de asemenea cheile straine de referinta)
• Stergerea constrangerii de cheie straina
• Schimbarea tipului sau dimensiunii unui camp
   ALTER TABLE table_name MODIFY (field_name new_declaration);
   ALTER TABLE customer MODIFY (cname VARCHAR2(15));
Schimbarea sau stergerea numelui unui camp (coloana) nu este permisain mod normal!

• Adaugarea unei constrangeri
ALTER TABLE table_name ADD CONSTRAINT constraint_name constraint_declaration;
         ALTER TABLE faculty ADD PRIMARY KEY (fid);
         ALTER TABLE customer ADD FOREIGN KEY(ordid) REFERENCES corder(ordid);
        ALTER TABLE orders ADD FOREIGN KEY(cust_num) REFERENCES customer;
        ALTER TABLE faculty ADD CONSTRAINT faculty_frank_cc
            CHECK ((frank = 'ASSO') OR (frank = 'FULL'));
        ALTER TABLE part ADD CHECK (item_class IN ('AP', 'HW', 'SG'));

• Schimbarea unei constrangeri existente se face prin stergerea sa urmata de adaugarea unei noi reguli
ALTER TABLE table_name DROP CONTRAINT constraint_name;
ALTER TABLE table_name ADD CONSTRAINT constraint_name new_check_condition ;
     ALTER TABLE student DROP CONSTRAINT student_sclass_cc;
    ALTER TABLE student ADD CONSTRAINT student_sclass_cc
        CHECK ((sclass = 'FR') OR (sclass = 'SO'));
    ALTER TABLE student MODIFY (age NULL);

• Schimbarea unei valori implicite
ALTER TABLE table_name MODIFY (field_name field_type DEFAULT default_value);
ALTER TABLE student MODIFY (sclass CHAR(2) DEFAULT 'FR');