Naked JDBC in common

The JDBC™ API provides programmatic access to relational data from the Java™ programming language.

Using the JDBC API, applications can execute SQL statements, retrieve results, and propagate changes back to an underlying data source.

The JDBC API can also interact with multiple data sources in a distributed, heterogeneous environment.

Transactions Control in SQL & PostgreSQL

Transactions control is the most important thing for the Data Integrity.
Transaction record is one or more related changes to the database that are made at once.

Properties of Transactions

• Atomicity:
  ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.
  
  
• Consistency:
  ensures that the database properly changes states upon a successfully committed transaction.
  
  
• Isolation:
  enables transactions to operate independently of and transparent to each other.
  
  
• Durability:
  ensures that the result or effect of a committed transaction persists in case of a system failure.
  
  

PostgreSQL Calculate Age From Birthdate

There is AGE() function that performs two calculations depending of provided params:

• It substracts date2 from date1 if two params are provided:

  --calculation: date1 - date2
  age(date1, date2)

• It substracts date from the current date if only one param is provided:

  --calculation: current_date - date
  age(date)    
  

SQL data reliability - CONSTRAINTS usage

Data Integrity

The following categories of the data integrity exist with each RDBMS:

• Entity Integrity: There are no duplicate rows in a table.
• Domain Integrity: Enforces valid entries for a given column by restricting the type, the format, or the range of values.
• Referential integrity: Rows cannot be deleted, which are used by other records.
• User-Defined Integrity: Enforces some specific business rules that do not fall into entity, domain or referential integrity.

Constraints

Rules enforced on data columns that ensures the accuracy and reliability of the DB data.

Constrains could be column or table level.

All existing Constraint types will be described below: NOT NULL, DEFAULT, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY.

Constraints Naming

Ensure that each column has a descriptive name!

Doing so makes DB more self-documenting and easy to understand for those who didn't develop it.

Consider adding surrogate keys – columns that have no business meaning but can uniquely identify each row. Identity columns are a great example of surrogate keys.

If you do use surrogate keys be sure that their name includes the table/entity name.
For example, do not add a column called ID to each table. Instead use customer_id, supplier_id, store_id and so forth.

There are a lot of conventions and holy wars regarding the Constraints naming.
Just choose the most understandable one and follow it.

I use the following Constraint prefixes:

Name prefix	Statement type
PK_	PRIMARY KEY
FK_	FOREIGN KEY
DF_	DEFAULT
UQ_	UNIQUE
CHK_	CHECK
IDX_	INDEX

I prefer UQ_ over UK_ or UN_ which u can find in some SQL standarts, cause UQ_ isn't intercepted with others abbreviations.
The same for CHK_ instead of CC_ or CK_ and IDX_ instead of IX_ - cause it's more precise & understandable.

Whatever, if u work in a team, just consider the team guidelines and don't mess it up with your own keys!

CRUD operations in SQL

CRUD acronym

CRUD is an acronym that represents four basic functions of persistent storage:

Code Completion

Operation	SQL
Create	INSERT
Read (Retrieve)	SELECT
Update (Modify)	UPDATE
Delete (Destroy)	DELETE

These operations in SQL are described below via using MYSQL dbms: