DBMAN 5 DDL Constraints DML Transactions Examples V

DBMAN 5 DDL Constraints DML Transactions Examples V 1. 0 Szabo. Zs 1

SELECT Displayed order of suffixes 1. 2. 3. 4. 5. 6. 7. 8. V 1. 0 INTO FROM WHERE GROUP BY HAVING UNION/MINUS INTERSECT ORDER BY Szabo. Zs 2

DBMAN 5 DDL Constraints DML Transactions Examples V 1. 0 Szabo. Zs 3

CREATE TABLE … CREATE TABLE {name} AS {subquery}; TSQL: SELECT * INTO {name} FROM emp; CREATE TABLE {user/db}. {TABLENAME} ( {COLUMN DEFINITIONS} {TABLE CONSTRAINTS} ); COLUMN DEFINITION: {NAME} {TYPE} [{COLUMN CONSTRAINTS}] [DEFAULT {DEFAULT VALUE}] V 1. 0 Szabo. Zs 4

Data Types (Oracle) • These types are used for all columns and function parameters • Data types: – char(N) – varchar 2(N) [255 byte in the OLD days, now 2 K – 4 K] – blob (vs. clob, ~4 GB * X = “ 8 TB to 128 TB”) – numeric(N[, M]) / number(N[, M]) / int, float – date / timestamp (time – not available in Oracle instead usable: interval) alter session set NLS_DATE_FORMAT = 'YYYYMM-DD'; V 1. 0 Szabo. Zs 5

Data Types (My. SQL) • char(N) – varchar(N) [rowsize: 65 K] • blob (vs. text: tinytext, mediumtext, longtext, max. 4 GB) • tinyint (sbyte), smallint (integer), int, bigint (long), float, double • date / time / datetime / timestamp • Others (year, enum, set, binary, bit, decimal, numeric, bool – we will not use those) V 1. 0 Szabo. Zs 6

Data Types (TSQL) • char(N) / varchar(N) / nvarchar(N) binary(N) / varbinary(N) (8 K or MAX = 2 GB) • tinyint (sbyte), smallint (integer), int, bigint (long), real (=float), float(N) [“ 1<=n<=24, n is treated as 24. If 25<=n<=53, n is treated as 53. ”] • date / time / datetime 2 [more precise than datetime] • Others (money, smallmoney, decimal, datetimeoffset, smalldatetime, image, text – we will not use those) V 1. 0 Szabo. Zs 7

Most simple usage CREATE TABLE Test. Table ( Field 1 VARCHAR(50), Field 2 DATE, Field 3 NUMERIC(10, 2), Field 4 Int, Field 5 Float ); V 1. 0 Szabo. Zs 8

Most simple usage • If the table exists, it must be deleted before (no CREATE OR REPLACE TABLE)! • Oracle: SELECT * FROM user_catalog WHERE UPPER(table_name)='TESTTABLE'; • My. SQL: SHOW tables like '%TESTTABLE%'; • TSQL: IF OBJECT_ID(' Test. Table', 'U') IS NOT NULL DROP TABLE Test. Table; V 1. 0 Szabo. Zs 9

My. SQL table types • Unique (maybe the best? ) approach (Oracle: Tablespace + disk) • Possibility to use various storage modes for every table: according to the needs of the various tasks • My. Isam vs Inno. DB • CSV, Merge/MRG_My. Isam (union of tables) , • Federated (remote tables) , Memory, Archive, Blackhole • ENGINE=‘xxx’ after the CREATE TABLE • We usually do not use those, default=Inno. DB V 1. 0 Szabo. Zs 10

DROP TABLE / RENAME • DROP TABLE {tablename}; • My. SQL: [IF EXISTS] • RENAME {tablename} TO {newname}; V 1. 0 Szabo. Zs 11

ALTER TABLE • ALTER TABLE {tablename} ADD {column definition}; • TSQL: ALTER TABLE {tablename} ALTER COLUMN {column name} {column definition without name}; • TSQL: EXEC sp_RENAME 'table. field', 'name 2', 'COLUMN' • Oracle: ALTER TABLE {tablename} MODIFY {column name} {column definition without name}; • Oracle: ALTER TABLE {tablename} RENAME COLUMN {column name} TO {newname}; • My. SQL: ALTER TABLE {tablename} CHANGE {column name} {full column definition}; • ALTER TABLE {tablename} DROP COLUMN {name}; V 1. 0 Szabo. Zs 12

DBMAN 5 DDL Constraints DML Transactions Examples V 1. 0 Szabo. Zs 13

Column constraints • NOT NULL – Field cannot have NULL inside • UNIQUE – Field cannot have repeated values • PRIMARY KEY – Unique + NOT NULL (+ indexed) – this can be applied only to one field as a column constraint! • REFERENCES othertable(otherfield) – Define foreign key. The other field must be a primary key in the other table • REFERENCES othertable(otherfield) ON DELETE/UPDATE CASCADE – the delete/update commands have consequences • Others: auto_increment, identity, serial … V 1. 0 Szabo. Zs 14

Column constraints • CREATE TABLE test 1 ( test 1_id NUMERIC(3, 0) PRIMARY KEY, test_nev VARCHAR(30) NOT NULL UNIQUE, test_datum TIMESTAMP NOT NULL); SQL> desc test 1; Name ---------TEST 1_ID TEST_NEV TEST_DATUM V 1. 0 Null? -------NOT NULL Szabo. Zs Type ---------NUMBER(3) VARCHAR 2(30) TIMESTAMP(6) 15

Column constraints • CREATE TABLE test 2 ( test 2_id NUMERIC(3, 0) PRIMARY KEY, test 2_test 1 NUMERIC(3, 0) REFERENCES test 1(test 1_id) ON DELETE CASCADE, test 2_data INT NOT NULL); SQL> desc test 2 Name --------TEST 2_ID TEST 2_TEST 1 TEST 2_DATA V 1. 0 Null? Type -------------NOT NULL NUMBER(3) NOT NULL NUMBER(38) Szabo. Zs 16

Table constraints • After the field list • This is the only way we can define complex keys: – PRIMARY KEY (field 1, field 2) – FOREIGN KEY (field 1, field 2) REFERENCES othertable (field. X, field. Y) V 1. 0 Szabo. Zs 17

Defining simple keys • CREATE TABLE test 3 ( test 3_id NUMERIC(3, 0), test 3_test 1 NUMERIC(3, 0), test 3_data INT NOT NULL, PRIMARY KEY (test 3_id), FOREIGN KEY (test 3_test 1) REFERENCES test 1(test 1_id)); V 1. 0 Szabo. Zs 18

Defining complex keys • CREATE TABLE test 4 ( first_pkey INT, second_pkey INT, data INT, primary key (first_pkey, second_pkey)); V 1. 0 Szabo. Zs 19

Defining complex keys • CREATE TABLE test 5 ( kulcs INT PRIMARY KEY, first_fkey INT, second_fkey INT, data FLOAT, FOREIGN KEY (first_fkey, second_fkey) REFERENCES test 4 (first_pkey, second_pkey)); V 1. 0 Szabo. Zs 20

Constraint names • Every constraint can (and should) be named: CONSTRAINT [NAME], otherwise automatic name • E. g. : CONSTRAINT test 3_prim_key PRIMARY KEY (test 3_id) CONSTRAINT test 3_for_key FOREIGN KEY (test 3_test 1) REFERENCES test 1(test 1_id) V 1. 0 Szabo. Zs 21

CHECK constraint • CHECK (condition): define an always-true condition • CREATE TABLE test 6 ( id int primary key, data int, CONSTRAINT check_data CHECK ((data>=500) AND (data<=999))); • It must be true for all records (forbid insert/update) • insert into test 6 values (1, 300); • My. SQL> Query OK, 1 row affected (0. 06 sec) ? ? • My. SQL: „The CHECK clause is parsed but ignored by all storage engines. ” , even in v 8, even in Maria. DB 10. 1 … Finally enabled in Maria. Db 10. 2. 1 (alpha) / 10. 2. 6 (stable, MAY/2017) V 1. 0 Szabo. Zs 22

CHECK constraint • Must define an always-true condition (can't add CHECK (sal<2000) ) • "A CLERK's salary must be maximum 2000 USD" CHECK ( (sal<=2000 AND upper(job)='CLERK') OR upper(job)<>'CLERK') • NOW() / Sysdate, dynamic functions, subqueries are not allowed! • Alternative (also suggested by My. SQL devs): use triggers: sub program that is executed in connection with some SQL operation (INSERT, UPDATE, DELETE) V 1. 0 Szabo. Zs 23

Constraints • Oracle: – ALTER TABLE {tablename} DISABLE CONSTRAINT {constraint name} – ALTER TABLE {tablename} ENABLE CONSTRAINT {constraint name} – ALTER TABLE {tablename} ADD {constraint}; – ALTER TABLE {tablename} DROP CONSTRAINT {constraint name}; • My. SQL: – ALTER TABLE {tablename} ADD {constraint}; – ALTER TBALE {tablename} DISABLE KEYS / ENABLE KEYS; V 1. 0 Szabo. Zs 24

Constraints • TSQL: – ALTER TABLE test 6 check_data; – ALTER TABLE test 6 V 1. 0 NOCHECK CONSTRAINT ALL; NOCHECK CONSTRAINT check_data; Szabo. Zs 25

DBMAN 5 DDL Constraints DML Transactions Examples V 1. 0 Szabo. Zs 26

INSERT INTO • INSERT INTO {table} ({list of columns}) VALUES ({list of values}); • The column list can be left out, in this case every column's new value must be specified (in the same order as the table was created) • Otherwise: the skipped columns will have their default values (specified at the CREATE TABLE) V 1. 0 Szabo. Zs 27

INSERT INTO • INSERT INTO {table} ({list of columns}) VALUES ({list of values}); • CREATE TABLE test 8 ( id int PRIMARY KEY, str VARCHAR(30) NOT NULL UNIQUE, createdat datetime 2 NOT NULL); • INSERT INTO test 8 VALUES (5, 'BILL' , SYSDATETIME()); • INSERT INTO test 8 (id, str, createdat) VALUES (6, 'JOE' , GETDATE()); V 1. 0 Szabo. Zs 28

Sequence / Identity / Serial / Auto-increment • Oracle: sequence • My. SQL: auto_increment (if NULL or missing) • Maria. Db: auto_increment (if NULL or missing), sequence • Postgre. SQL: serial (if DEFAULT or missing), sequence • TSQL: identity (MUST be missing / IDENTITY_INSERT), sequence V 1. 0 Szabo. Zs 29

Identity / Sequence (TSQL) CREATE TABLE CITY ( ID INT IDENTITY PRIMARY KEY, NAME VARCHAR(30) ); INSERT INTO CITY (NAME) VALUES ('NEW YORK'); SELECT IDENT_CURRENT('city'); CREATE SEQUENCE DEPTSEQ AS int START WITH 10 INCREMENT BY 10; INSERT INTO DEPT VALUES (NEXT VALUE FOR DEPTSEQ, 'ACCOUNTING', 1); SELECT current_value FROM sys. sequences WHERE name = 'deptseq' ; V 1. 0 Szabo. Zs 30

UPDATE/DELETE • UPDATE {tablename} SET {field 1}={value 1}[, {field. N}={value. N}, … …] [WHERE {expression}] • DELETE FROM {tablename} [WHERE {expression}] • It is advised to execute the same WHERE beforehand using a SELECT command, to test the filter! • My. SQL: REPLACE INTO … / TRUNCATE TABLE Oracle/TSQL: MERGE INTO … V 1. 0 Szabo. Zs 31

DBMAN 5 DDL Constraints DML Transactions Examples V 1. 0 Szabo. Zs 32

Transactions = Treat several DML as one • We can treat multiple DML commands as one: – Start transaction – depends on dialect. Oracle: always in transaction. Others: must ask for start of transaction – COMMIT: accept & write every modification – ROLLBACK: cancel & undo every modification – SAVEPOINT: we can define specific points to be used as the rollback target – DDL: Automatic commit! • „A transaction is a group of operations that are atomic, consistent, isolated, and durable” (ACID-compliant = Useable RDBMS!) V 1. 0 Szabo. Zs 33

ACID • Atomic – Multiple DML queries must be treated as one – Cannot be interrupted (or at least it must look that way) – Transaction isolation levels • Consistent – The transaction must leave the database in a consistent state – Usually this is true even in between transaction statements – Not only true for SQL consistency, but also for BL consistency • Isolated – Error in any transaction must not affect others ( auto rollback) – Intermediate results must not be visible until COMMITed • Durable V 1. 0 – After a COMMIT, no data can be lost Szabo. Zs 34

Transactions = Simple? • A simple feature that requires some extra CPU/memory resources and A LOT of well-planned design and programming not so simple feature : ) • My. SQL: only in Inno. DB is ACID compliant, which is available since 5. 0. . . • But Inno. DB is only default since 5. 5 (Thanks to the Oracle acquisition, because Oracle owned Inno. DB) • Since 5. 5 = My. SQL is only ACID-compliant since 2010 !!! V 1. 0 Szabo. Zs 35

Transactions / TSQL • • • V 1. 0 begin transaction; SELECT * FROM test 8; -- 2 rows delete FROM test 8 WHERE id=5; save transaction testpoint; SELECT * FROM test 8; -- 1 row delete FROM test 8; SELECT * FROM test 8; -- 0 row rollback transaction testpoint; SELECT * FROM test 8; -- 1 row rollback transaction; SELECT * FROM test 8; -- 2 rows Szabo. Zs 36

DBMAN 5 DDL Constraints DML Transactions Examples … OK, only one example … V 1. 0 Szabo. Zs 37

Example #1 Increase the salaries with 1000 USD for those who don't have any commission and who have more than one person in their job and who work in Dallas or Chicago. drop table if exists emp 1; create table emp 1 as SELECT * FROM emp; TSQL> IF object_id('emp 1 ') is not null DROP TABLE emp 1; SELECT * into emp 1 FROM emp; V 1. 0 Szabo. Zs 38

Let's start… • No commission, Dallas or Chicago: UPDATE emp 1 SET sal=sal+1000 WHERE ((comm is null) or (comm = 0)) AND (upper(loc) in ('DALLAS', 'CHICAGO')) … … … loc? JOIN! V 1. 0 Szabo. Zs 39

Join in UPDATE? • With the SELECT command, the join is simple: an extra condition in the WHERE • Here, we only have to modify those records, WHERE the joined location is correct – but originally, we can put only one table in the UPDATE statement • Thus: the join must be put into the WHERE as a subquery • correlated sub-query (typical approach in the Oracle world) V 1. 0 Szabo. Zs 40

Approach #1 • Let's try to define the location for ONE worker: • SELECT upper(loc) FROM emp 1, dept WHERE emp 1. deptno=deptno and emp 1. empno={ID for the wanted worker} • This should go into the WHERE suffix of the UPDATE command V 1. 0 Szabo. Zs 41

Solution UPDATE emp 1 SET sal=sal+1000 WHERE ((comm is null) or (comm = 0)) AND ((SELECT upper(loc) FROM emp 1, dept WHERE emp 1. deptno=deptno and emp 1. empno={ID for the wanted worker}) IN ('CHICAGO', 'DALLAS')) Problem: two instances of emp 1 table? ? ? ID for the worker? ? ? V 1. 0 Szabo. Zs 42

Solution UPDATE emp 1 a SET sal=sal+1000 WHERE ((comm is null) or (comm = 0)) AND ((SELECT upper(loc) FROM emp 1 b, dept WHERE b. deptno=deptno and b. empno=a. empno) IN ('CHICAGO', 'DALLAS')) Problem: V 1. 0 two instances Aliases emp 1 a, emp 1 b ID of the worker a. empno Szabo. Zs 43

Solution More than one person in the job • Same principle: approach via the SELECT: SELECT count(*) FROM emp 1 WHERE job={some job}; • Same problems: use alias, use external_query. job V 1. 0 Szabo. Zs 44

Solution UPDATE emp 1 a SET SAL=SAL+1000 WHERE (SELECT count(*) FROM emp 1 b WHERE b. job=a. job)>1; • Now: Combine the previous conditions, so the UPDATE will have a WHERE that contains three conditions V 1. 0 Szabo. Zs 45

Solution • It is STRONGLY SUGGESTED to execute a SELECT before • No problem from having the same alias in two subqueries SELECT * FROM emp 1 a WHERE (comm=0 or comm is null) AND ((SELECT upper(loc) FROM emp 1 b, dept WHERE b. deptno=deptno and b. empno=a. empno) IN ('CHICAGO', 'DALLAS')) AND (SELECT count(*) FROM emp 1 b WHERE b. job=a. job)>1; V 1. 0 Szabo. Zs 46

Solution #1 UPDATE emp 1 a SET sal=sal+1000 WHERE (comm=0 or comm is null) AND ((SELECT upper(loc) FROM emp 1 b, dept WHERE b. deptno=deptno and b. empno=a. empno) IN ('CHICAGO', 'DALLAS')) AND (SELECT count(*) FROM emp 1 b WHERE b. job=a. job)>1; V 1. 0 Szabo. Zs 47

Solution #1 – Not in My. SQL / TSQL … • TSQL> Incorrect syntax near 'a'. (without alias: OK) • My. SQL> ERROR 1093 (HY 000): You can't specify target table 'a' for update in FROM clause • „Correlated Subquery”, this works in all other dialects (Maria. Db included) • But this one is VERY slow – and this type of correlated subqueries cannot even be optimized… • Reason: for every record, we execute a DIFFERENT subquery to verify if the location and the job are correct • Possibility to optimize: let's try to create sub-queries that remain the SAME, so that the query optimizer can cache them somehow (and in this case we might be able to use views too…) V 1. 0 Szabo. Zs 48

APPROACH #2 • Use lists! • Collect the people who work in the good locations • Collect the jobs that have enough people lists that contain ID fields are always preferred, especially in Oracle (key-preserved joined views) Collect the people who have good jobs! • Then increase the salaries for those who are in BOTH LISTS V 1. 0 Szabo. Zs 49

Location-condition • SELECT empno FROM emp 1, dept WHERE emp 1. deptno=deptno AND dept. loc IN ('DALLAS', 'CHICAGO'); • Result: a list of those employee IDs who work in Dallas or Chicago CREATE VIEW good. Loc AS SELECT empno FROM emp 1, dept WHERE emp 1. deptno=deptno AND dept. loc IN ('DALLAS', 'CHICAGO'); V 1. 0 Szabo. Zs 50

Job-condition • SELECT job FROM emp 1 GROUP BY job HAVING count(*)>1; Result: a list of those jobs WHERE there are more than one workers (NOT key-preserved! BAD!) • SELECT empno FROM emp 1 WHERE job IN (SELECT job FROM emp 1 GROUP BY job HAVING count(*)>1) Result: a list of those workers who have good jobs Subquery in the WHERE … not fast enough! V 1. 0 Szabo. Zs 51

Job-condition Better (faster) solution: • SELECT empno FROM emp 1 INNER JOIN (SELECT job FROM emp 1 GROUP BY job HAVING count(*)>1) sub ON emp 1. job=sub. job; • CREATE OR REPLACE VIEW good. Job AS SELECT empno FROM emp 1 INNER JOIN (SELECT job FROM emp 1 GROUP BY job HAVING count(*)>1) sub ON emp 1. job=sub. job; V 1. 0 Szabo. Zs 52

Solution #2 UPDATE emp 1 SET sal=sal+1000 WHERE ((COMM=0) OR (COMM IS NULL)) AND empno IN (SELECT empno FROM good. Job) AND empno IN (SELECT empno FROM good. Loc); V 1. 0 Szabo. Zs 53

Solution #2 – not working in My. SQL … • My. SQL> ERROR 1093 (HY 000): You can't specify target table 'a' for update in FROM clause • This works as well in the others – Subquery in the where should still be slow – Even thought the subquery is the same for every row, but it is (in theory) still executed for every row – The optimizer can (and will) make these queries faster – Can be speed up even more: materialized/indexed view (Oracle/MSSQL) – or CREATE TEMPORARY TABLE • Better: “Rewriting Subqueries as Joins” https: //dev. mysql. com/doc/refman/8. 0/en/rewritingsubqueries. html V 1. 0 Szabo. Zs 54

APPROACH #3 Correlated subqueries are always slow, because they can be executed more than once (. . . well, they should be – unless the optimizer kicks in ). AVOID them! Rewrite all the IN subqueries! SELECT * FROM emp 1 WHERE empno IN (…) SELECT emp 1. * FROM emp 1 INNER JOIN (…) sub ON emp 1. empno=sub. empno Same result, but the sub-query is only executed ONCE – decorrelated query! Best performance! In UPDATEs too! V 1. 0 Szabo. Zs 55

Decorrelated DML? Avoid subqueries in the WHERE, then the optimizer can maximize the performance of your query! In SELECT/UPDATE/DELETE as well Manual JOIN / JOIN-in-the-WHERE works only with SELECT statements always prefer the INNER JOIN Use lists that contain Primary Key fields! UPDATE emp 1 SET xxx WHERE empno IN (…) UPDATE emp 1 INNER JOIN (…) sub ON (emp 1. empno=sub. empno) SET xxx That is the My. SQL syntax… It’s different in all dialects… V 1. 0 Szabo. Zs 56

APPROACH #3 – this one. WORKS in My. SQL UPDATE emp 1 INNER JOIN g. Loc ON (emp 1. empno= g. Loc. empno) INNER JOIN g. Job ON (emp 1. empno= g. Job. empno) SET sal=sal+1000 WHERE ((COMM=0) OR (COMM IS NULL)); V 1. 0 Szabo. Zs 57

How does it work in Oracle? Almost the same (updateable inline view) : UPDATE ( SELECT * FROM emp 1 INNER JOIN g. Job ON (emp 1. empno= g. Job. empno) INNER JOIN g. Loc ON (emp 1. empno= g. Loc. empno) ) SET sal=sal+1000 WHERE ((COMM=0) OR (COMM IS NULL)); Important restriction : key-preserved table/view!!! V 1. 0 Szabo. Zs 58

How does it work in. Postgre. SQL? Almost the same (UPDATE FROM + JOIN-in-the-WHERE) : UPDATE emp 1 SET sal=sal+1000 FROM g. Job, g. Loc WHERE ((COMM=0) OR (COMM IS NULL )) AND (emp 1. empno= g. Job. empno) AND (emp 1. empno= g. Loc. empno); V 1. 0 Szabo. Zs 59

How does it work in TSQL? Almost the same (UPDATE FROM) : UPDATE emp 1 SET sal=sal+1000 FROM emp 1 INNER JOIN g. Job ON (emp 1. empno= g. Job. empno) INNER JOIN g. Loc ON (emp 1. empno= g. Loc. empno) WHERE ((COMM=0) OR (COMM IS NULL)); V 1. 0 Szabo. Zs 60

Final thoughts on DQL / DML ALWAYS AVOID SUBQUERIES IN THE FIELDLIST/WHERE V 1. 0 Szabo. Zs 61

V 1. 0 Szabo. Zs 62

Szabo. Zs 63