3. Modifying constraints3.1 giving the constraint a name
Name in the following format:
gender char (1 ) constraint Noandro check (Gender in ( " f ", " m )
CONSTRAINT Righttitle CHECK = ' F ' or not like ' ms.% ')
3.2 Modifying constraints on a table
① checking time to modify constraints
SET CONSTRAINT Myconstraint DEFERRED;
SET CONSTRAINT Myconstraint IMMEDIATE;
② with the ALTER TABLE statement
Delete Constraint drop
ALTER TABLE DROP CONSTRAINT Nameiskey; ALTER TABLE DROP CONSTRAINT Noandro; ALTER TABLE DROP CONSTRAINT Righttitle;
Adding constraints Add (note that constraints must be added only when the entire table is adhered to)
ALTER TABLEMoviestarADD CONSTRAINTNameiskeyPRIMARY KEY(name);ALTER TABLEMoviestarADD CONSTRAINTNoandroCHECK(Genderinch('F','M'));ALTER TABLEMoviestarADD CONSTRAINTRighttitleCHECK(Gender= 'F' ORName not like 'ms.%');
Note: SQL does not remember the constraints that have been removed, so if you want to add a previously deleted constraint, you must write it again.
1. Creating assertions
CREATE Assertion assert name CHECK (condition)
2. Using assertions
CREATE CHECK (notEXISTS (SELECT studio.name from Studio, movieexec WHERE=and<1000000 ) );
The above assertion does not allow managers with funds less than 1000000.
CREATE CHECK (10000>=all (SELECTSUMfromGROUP by Studioname));
The above assertion does not allow the film company's total movie length to exceed 10,000 minutes.
If this statement is written with a check constraint, it is
CHECK 10000 >= All (SELECTSUMfromGROUP by Studioname);
The difference between assertions and check:
Check is only valid for insertions and modifications, but does not check for deletions, and in some cases causes constraints to not be established.
The assertion will be checked when any of the mentioned relationships change, ensuring that conditions are established.
3. Delete assertions
DROP Assertion Assertion Name
When a particular event occurs, the trigger is activated. If the state satisfies the condition in the trigger, the action connected by the trigger is executed.
1. Not allowed to reduce the net assets of film producers
CREATE TRIGGERNetworthtriggerafterUPDATE ofNetWorth onmovieexecreferencing Old ROW asoldtuple, NEW ROW asNewtuple forEach ROW when(Oldtuple.networth>Newtuple.networth)UPDATEmovieexecSETNetWorth=Oldtuple.networthWHEREcert#=newtuple.cert#;
2. Prevent filmmakers ' average assets from falling to 500000. To set a trigger for inserts, deletions, and modifications, respectively. Listed here are the modified:
CREATE TRIGGERAvgnetworthtriggerafterUPDATE ofNetWorth onmovieexecreferencing OldTABLE asOldstuff, NEWTABLE asNewstuff forEach STATEMENT when(500000 >(SELECT AVG(NetWorth) frommovieexec))BEGIN DELETE frommovieexecWHERE(Name, address, cert#, NetWorth)inchNewstuff; INSERT intoMovieexec (SELECT * fromoldstuff);END;
3. Prevent the primary key from being empty as part of the primary key in year. Insert a default value of 1915 before inserting if you find that year is empty
CREATE TRIGGERFixyeartriggerbeforeINSERT onmoviesreferencing NEW ROW asNewRow NEWTABLE asNewstuff forEach ROW whenNewRow. Year is NULLUPDATENewstuffSET Year = 1915;
The relevant options are described:
After/before: Decide whether the test will occur before or after the event
Events that can be triggered:update\insert\delete
of Properties : Optional for UPDATE, if selected, triggers are activated only if the listed properties change.
When statement : Optional, if selected, the condition is true to execute the following statement, otherwise you must execute the following statement.
Multiple execution actions: by BEGIN, END
Old row as/new ROW as/old table as/newtable as: tuples and tables before and after referencing table modifications
For each row/for STATEMENT: Row-level trigger, statement-level trigger. If the entire table is updated, the row-level triggers are executed one at a time for the modified tuple, and the statement-level trigger is executed only once. Statement-level triggers cannot reference new and old tuples, but can refer to old and new tables, and row-level triggers can be referenced.
Insert tuple no old value, delete tuple no new value
"SQL" Constraints and Triggers 2