SQL baza – struktura

Odličan link o strategiji, dizajniranju i formiranju baze.
Kratko i dobro objašnjenje osnova kroz youtube tutorial.

Primary Key
Each table should have a column (or a set of columns), called primary key, that uniquely identifies every records of the table. A primary key is called a simple key if it is a single column; it is called a composite key if it is made up of several columns. Most RDBMSs build an index on the primary key to facilitate fast search and retrieval. The primary key is also used to reference other tables. The primary key shall always have a value. In other words, it shall not contain NULL.
A table can have only one primary key.
Dakle to je bila teorija.
U praksi, kada se kreira table (u toku kreiranja ili posle) potrebno je definisati primary key. Dobar link.
A primary key cannot be applied on a column with a null constraint (nullable). When a table is created, by default every column is nullable (link).
So first off all we have to remove the null constraint and apply a NOT NULL constraint on the column :
ALTER TABLE spisak alter column rednibroj int NOT NULL;
Pa onda dešinišemo kolonu “rednibroj” kao primary key tabele “spisak” :
ALTER TABLE spisak ADD PRIMARY KEY (rednibroj);

Kako izlistati primarne klučeve u tabeli :
SELECT * FROM information_schema.table_constraints
WHERE constraint_type = ‘Primary Key’ and TABLE_NAME = ‘spisak’;
Priznajem da je izlaz ovog upita prilično neupotrebljiv…..

Views (link)
A view is nothing more than a SQL statement that is stored in the database with an associated name. A view is actually a composition of a table in the form of a predefined SQL query.
Database views are created using the CREATE VIEW statement. Views can be created from a single table, multiple tables or another view.
The INFORMATION_SCHEMA views allow you to retrieve metadata about the objects within a database :
SELECT * FROM INFORMATION_SCHEMA.TABLES;
Što za moju testnu tabelicu daje ovo :

Dobar link vezan za INFORMATION_SCHEMA.TABLES.

You could create index on selected column(s) to facilitate data searching and retrieval. An index is a structured file that speeds up data access for SELECT, but may slow down INSERT, UPDATE, and DELETE