Three main paradigms of database and five constraints

One or three major paradigms:

1. First paradigm (1NF): Each column in the data table (each field) must be the smallest unit that is not to be split, that is, the atomicity of each column;

2. Second paradigm (2NF): After 1NF is satisfied, all columns in the table must be dependent on the primary key, and no one column has nothing to do with the primary key, which means that a table only describes one thing

3. The third paradigm (3NF): The second paradigm (2NF) must first be met, requiring that each column in the table be directly related to the primary key rather than indirectly, (each column in the table can only depend on the primary key).

Summarize:
The first and second paradigm is that there is no two tables, the second paradigm is that a table contains the different entity attributes, then must be divided into multiple tables, the third paradigm is the requirement has been divided into multiple tables, then a table can only have another table ID (primary key), And no other information (other information will be used in the primary key in another table query)

Two or five major constraints:
1. Primary key: Primary key
(1) Description: The default non-null primary key, the default uniqueness constraint, only the primary key to set autogrow, auto-growth must be the primary key, the primary key does not necessarily automatically grow;
(2) Setting mode:
Set when defining a column: ID INT PRIMARY KEY
Set after column definition: primary KEY (ID)

2. Unique: Uniqueness constraint, cannot have duplicate value;

3. Default: Defaults constraint,

For example: Height DOUBLE (3,2) The default 1.2------height does not enter

4. NOT NULL: non-null constraint, the field cannot be empty;

5. FOREIGN Key: Foreign key
(1) Precautions:
Only the INNODB database engine supports foreign keys;

The foreign key must be the same as the data type of the reference column (the numeric requirement length and unsigned are the same, the string requires the same type and the length can be different)

(2) Setting mode:
CONSTRAINT foreign Key Name FOREIGN key (foreign key field) REFERENCES reference table (Reference field) on DELETE set NULL on UPDATE CASCADE set referential integrity

(3) When the Reference field of the reference table is deleted or updated, the Foreign key table response:
RESTRICT: Deny reference table delete or update reference field----------------RESTRICT and no action are the same, but this command only takes effect in MySQL;
CASCADE: When you delete or update a reference field for a reference table, the record for the foreign key table is synchronized to delete the update;
Set NULL: The foreign key of the foreign key table is set to NULL when the reference field of the reference table is deleted or updated;

Note: The database engine (here is a brief summary of the difference between the InnoDB engine and my iasm engine)

1. InnoDB Engine:

MySQL default engine, provides support for the database acid transaction, and implements the SQL standard four isolation levels, in addition, the engine also provides row-level lock and foreign key constraints, it is designed to deal with large-capacity database system;

The MySQL runtime InnoDB creates a buffer pool in memory for buffering data and indexes. However, the engine does not support an index of type Fulltext, and it does not save the number of rows in the table, and the full table needs to be scanned when Select COUNT (*) from table. the engine is of course preferred when it is necessary to use a database transaction . Because the lock granularity is smaller, the write operation does not lock the full table , so using the InnoDB engine increases efficiency when high concurrency occurs. However, using row-level locks is not absolute, and if MySQL does not determine the scope to scan when executing an SQL statement, the INNODB table will also lock the full table.

2. My iasm Engine

The engine does not provide support for database transactions, and does not support row-level locks and foreign keys , so the write operation needs to lock the entire table, the efficiency will be lower;

Unlike InnoDB, however, the number of rows in the table is stored in myiasm, so the Select COUNT (*) from table only needs to read the saved values directly and does not require a full table scan. Myiasm is also a good choice if the table reads much more than writes and does not require support for database transactions.

3. How to view:

　　

How to Modify:/etc/my.cnf file (Linux) or My.ini (Windows) Modify default-storage-engine entry

Transferred from: http://www.cnblogs.com/waj6511988/p/7027127.html

Three paradigms of database and five constraints (RPM)