SQL must-know note 22nd Chapter Understanding Advanced SQL Features

22.1 constraints

To properly design a relational database, you need a way to ensure that only valid data is inserted into the table. For example, if the Orders table stores order information and the OrderItems table stores the order details, you should ensure that any order IDs referenced in OrderItems are present in orders. Similarly, any user referenced in the Orders table must exist in the Customers table.
Although it is possible to check when new rows are inserted, it is best not to do so for the following reasons:
(1) If database integrity rules are enforced at the client level, each client is forced to enforce these rules, but it is likely that some clients will not enforce them.
(2) These rules must also be enforced when the update and delete operations are performed.
(3) Performing client-side checks is time-consuming, and the DBMS performs these checks relatively efficiently.
Constraints (constraint) Manage rules for how database data is inserted or processed.
The DBMS enforces referential integrity by imposing constraints on database tables. Most constraints are defined in the table definition.

22.1.1 PRIMARY Key

A primary key is a special constraint that is used to guarantee that values in a column (or set of columns) are unique and never changed. In other words, the value of one column (or columns) in a table uniquely identifies a row in the table.
Any column in the table can be used for the primary key as long as the following conditions are true:
(1) The primary key values of any two rows are not the same.
(2) Each row has a primary key value (that is, null values are not allowed in the column).
(3) Columns that contain primary key values are not modified or updated.
(4) Primary key values cannot be reused. If a row is deleted from the table, its primary key value is not assigned to the new row.
One way to define a primary key is to create it as follows:

CREATE TABLEvendors{vend_idCHAR(Ten) not NULL PRIMARY KEY, Vend_nameCHAR( -) not NULL, vend_addressCHAR( -)NULL, vend_cityCHAR( -)NULL, Vend_stateCHAR(5)NULL, Vend_zipCHAR(Ten)NULL, Vend_countryCHAR( -)NULL};

Modify the primary key

ALTER TABLE VendorsADD CONSTRAINT PRIMARY KEY(vend_id);

22.1.2 FOREIGN Key

A foreign key is a column in a table whose value must be listed in the primary key of another table. foreign keys are a very important ingredient in guaranteeing referential integrity.
For example, the Orders table contains one row for each order entered into the system. Customer information is stored in the Customers table. Orders in the Orders table are associated with a specific row in the Customers table through the customer ID. The customer ID is the primary key for the Customers table, and each customer has a unique ID. The order number is the primary key for the Orders table, and each order has a unique order number.
One way to define a foreign key

CREATE TABLE Orders{     order_num      INTEGER        NOT NULL PRIMARY KEY,     order_date     DATETIME       NOT NULL,     cust_id        CHAR(10)       NOT NULL REFERENCES Customers(cust_id)};

You can also use the ALTER TABLE statement to complete the constraint syntax.

ALTER TABLE CustomersADD CONSTRAINTFOREIGN KEY (cust_id) REFERENCES Customers (cust_id);

Foreign keys help prevent accidental deletion: besides helping to guarantee referential integrity, foreign keys have another important role to play. After you define a foreign key, the DBMS does not allow you to delete rows that have associated rows in another table.

22.1.3 UNIQUE Constraint

A unique constraint is used to guarantee that the data in a column is unique. They are similar to primary keys, but there are several important differences:
(1) A table can contain multiple unique constraints, but only one primary key is allowed per table.
(2) The unique constraint column can contain null values.
(3) The unique constraint column can be modified or updated.
(4) The value of the unique constraint column can be reused.
(5) Unlike a primary key, a unique constraint cannot be used to define a foreign key.
The syntax of a unique constraint is similar to the syntax of other constraints. The unique constraint can be defined either in the table definition with the unique keyword or in a separate constraint.

22.1.4 CHECK Constraints

A check constraint is used to guarantee that the data in a column satisfies a specified set of conditions. The plug-in purpose of the CHECK constraint is:
(1) Check the minimum or maximum value.
(2) Specify the range.
(3) Only specific values are allowed.
Constraints are given when defining:

CREATE  table  orderitems{order_num integer  not  null , Order_item integer  not  null , prod_id char  (10 ) not  null , Quantity     integer  not  null  check  (Quantity > 0 ), Item_price money not  null };   

To check that a column named gender contains only m or F, you can write the following Altet table statement

LIKE‘[MF]‘);

22.2 Index

Indexes are used to sort data to speed up search and sort operations. The best way to understand an index is to imagine the index behind a book.
An index can be defined on one or more columns, allowing the DBMS to save an ordered list of its contents. After an index is defined, the DBMS uses it in a way that is similar to the index used by the book. The DBMS searches for an ordered index, finds the matching locations, and then retrieves the rows.
Before you start to create an index, you should keep in mind the following:
(1) Indexes improve the performance of retrieval operations, but reduce the performance of data insertions, modifications, and deletions. When performing these operations, the DBMS must dynamically update the index.
(2) The index data may occupy a large amount of storage space.
(3) Not all data is appropriate for the index. The benefits of poor data from indexing are no more than the benefits from indexing of data with more possible values.
(4) Indexes are used for data filtering and data sorting. If you often sort the data in a particular order, the data might be an alternative to the index.
(5) You can define multiple columns in the index. Such indexes are only useful when sorting in the order of States and cities. This index is useless if you want to sort by city.
The index is created with the CREATE INDEX statement. The following statement creates a simple index on the product list of the Products table:

CREATE INDEX prod_name_indON PRODUCTS(prod_name);

The index must be uniquely named. the index name here is defined after the keyword CREATE index. On is used to specify the table to be indexed, and the columns contained in the index are given in parentheses after the table name.
Check index: The efficiency of the index varies with the increase or change of the table data. Many database administrators have found that an ideal index created in the past may not be ideal after several months of data processing. It is a good idea to top up the index and adjust the index as needed.

22.3 triggers

a trigger is a special stored procedure that executes automatically when a particular database activity occurs. triggers can be associated with INSERT, update, and delete operations on a specific table.
Unlike stored procedures, triggers are associated with a single table. Triggers that are associated with an insert operation on the Orders table are executed only when the row is inserted in the Orders table. Similarly, the triggers for INSERT and update operations on the Customers table are executed only when these actions occur on the table.
The code within the trigger has access to the following data:
(1) All new data in the insert operator;
(2) All new data and old data in the update operation;
(3) Deleted data in delete operation.
Some common uses of triggers:
(1) Ensure consistent data.
(2) Perform activities on other tables based on changes to a table.
(3) Perform additional validation and fallback data as needed.
(4) Calculate the value of the computed column or update the timestamp.
Creates a trigger that is capitalized on all insert and update operations, converting the Cust_state column in the Customers table.

CREATE TRIGGER customer_stateAFTER INSERT OR UPDATEFOR EACH ROWBEGINUPDATE CustomersSET cust_state = Upper(cust_state)WHERE Customers.cust_id = :OLD.cust_idEND;

Constraints are faster than triggers: in general, constraints are handled faster than triggers, so constraints should be used whenever possible.

22.4 Database Security

Most DBMS provide administrators with administrative mechanisms that can be used to grant or restrict access to data.
The basis of any security system is user authorization and identity verification. This is a process by which the user is confirmed by this processing to ensure that he is entitled to the user, allowing him to perform the operation he is trying to perform. Some DBMS use the operating system's security measures for this purpose, while others maintain their own lists of users and passwords, and some use external directory services servers together.
Actions that need to be protected:
(1) Access to database management functions.
(2) Access to a particular database or table.
(3) Type of access.
(4) Access to a table only through views or stored procedures.
(5) Create multi-layered security measures that allow for multiple login-based access and control.
(6) Limit the ability to manage user accounts.
Security is managed through the grant and REVOKE statements of SQL, but most DBMS provide an interactive administrative usage program that uses the GRANT and REVOKE statements internally.

SQL must-know note 22nd Chapter Understanding Advanced SQL Features