h1

Cap8- Constrangeri, domenii, chei pt.mentinerea integritatii bd

March 18, 2010

1) CREAREA UNUI RULE (CLAUZA CREATE RULE)

2) Crearea unui domeniu (CLAUZA CREATE DOMAIN)

Un domeniu – reprezinta un set de valori legale pe care o coloana le poate contine

3) Cheie primara

Tabela cu 4 chei primare:

4) Chei straine

Exemplu concret cu 3 tabele (o coloana identica in 2 tabele dar care are valori diferite):

Cod sql:

Constrangerea REFERENTIAL:

Ex1:

Ex2:

Ex3:

Ex4: Legatura dintre cele 2 tabele e data de cheile primare din cele 2 tabele (se poate lega cu left join practic pe cheile primare ale celor 2 tabele nu trebuie neaparat sa aiba acelasi nume de chei primare!!! )

Ex5: Clauza match full (impiedica existenta unei valori NULL in coloana cheie straina!!!)

Tipuri de MATCH pt. cheia straina (externa) tabelei parinte (cheia externa /straina nu e cheie primara in tabela copil!):

MATCH FULL. Tells the DBMS to require that all columns in a FOREIGN KEY be either NULL or not-NULL. For example, the definition of a two-column FOREIGN KEY constraint that includes a MATCH FULL clause tells the DBMS that the key value (NULL, NULL) is acceptable. However, key values such as (100, NULL) and (NULL, 100) always violate referential integrity because part of the key is NULL and part is non-NULL.

MATCH PARTIAL. Tells the DBMS to allow partially NULL FOREIGN KEY values, so long as the value in each of the non-NULL columns in the FOREIGN KEY matches the value in the corresponding column of the PRIMARY KEY. Thus, the definition of a two-column FOREIGN KEY constraint that includes a MATCH PARTIAL clause tells the DBMS that the key value (NULL, NULL) is acceptable. However, key values such as (100, NULL) and (NULL, 100) pass the referential integrity check only if the corresponding column of the PRIMARY KEY has a value of 100 in one more of the rows in the table.

MATCH SIMPLE. Tells the DBMS to skip the relational integrity check on partially NULL FOREIGN KEY values. Therefore, if the definition of a two-column FOREIGN KEY includes a MATCH SIMPLE clause (or has no MATCH clause at all), the DBMS will accept a FOREIGN KEY with the value (NULL, NULL), (NULL, 100), and (100, NULL)—whether or not the PRIMARY KEY has a value of 100 in the corresponding column of the rows in the table.

Ex6:

5) Constrangerea NOT NULL in coloane

6) Constrangerile  UNIQUE si CHECK in coloane

Constrangere UNIQUE multi-coloana:

7) Crearea unei reguli (clauza CREATE RULE)

8) Crearea unor constrangeri multi-tabela (clauza CREATE ASSERTION):

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: