first steps

display criteria

aggregation functions

operations on strings

operations on tables

all keywords

Test yourself - QUIZ

Theory

Basics

• database models
• Codd's 12 rules
• keys
• relationships
• dependencies
• ERD diagrams
• transactions

Normalization

• 1st normal form
• 2nd normal form
• 3rd normal form
• Boyce-Codd normal form

PL/pgSQL language

• introduction
• conditional instructions
• loops
• functions

you are at >> basics >>keys

Keys

General

• The key is basic identifying set
• Such set of attributes of relations, whose combination clearly identify the value of any record of this relation and no subset of this set doesn't have this property
• The key may not contain the NULL value
• It is used to identyfing and/or data sorting

If the set creating a key is single this key is named as a simple key, Otherwise, it is the complex key.

candidate key

The relation may have many attributes that may be unique and they can clearly identify every record. Among the candidates key one select the primary key and other keys are called alternate keys

alternate key, secondary key

It is each key which wasn't chosen for primary key.

Unique key

This is a value that within a single table is unique, and so can not receive two rows of the same unique value. A key may be, for example, passport number or ISBN number for book.

For columns, you can enforce it, that it is unique by setting a unique index UNIQUE.

Primary key

This is a key selected among the candidate keys and is used to uniquely identify each row in the table. It may not contain the NULL value. A key number may be, for example, a student id or social security number. There is also a possibility of creating an artificial key that will be transmitted automatically through a system of databases.

Foreign key

This type of key is used to create a relation between a pair of tables. The value of the foreign key in one table (child table) must correspond to the value of primary key or candidate key in the second table (parent table). Foreign key enforces integrity ties. In determining the relation between the tables, you can determine what action will be taken in case updating or removing the record from the parent table:

• CASCADE
  • ON UPDATE CASCADE - change of key in parent table causes change of foreign key in referencing table
  • ON DELETE CASCADE - deleting record from referenced table causes deleting corresponding record in child table
• RESTRICT
  • ON UPDATE RESTRICT - A row in the referenced table cannot be updated if dependent rows still exist.
  • ON DELETE RESTRICT - A row in the referenced table cannot be deleted if dependent rows still exist.
• SET NULL
  • ON UPDATE SET NULL - set NULL value for foreign key in case of changing value of primary key in parent table
  • ON DELETE SET NULL - set default value for foreign key in case of deleting corresponding record in parent table
• SET DEFAULT
  • ON UPDATE SET DEFAULT - set default value for foreign key in case of changing value of primary key in parent table
  • ON DELETE SET DEFAULT - set default value for foreign key in case of deleting corresponding record in parent table
• ON UPDATE NO ACTION - don't change value for foreign key in case of changing value of primary key in parent table

[ wróć na górę strony ]