|
JOIN |
KEY | Relationship| Identity | Constraints
JOIN
Employee
Name |
ID(fk) |
Smith |
1 |
| Jones |
2 |
Robin |
1 |
Adam |
3 |
Steve |
2 |
Bryan |
4 |
|
Department
ID(pk) |
Dept |
4 |
Sales |
2 |
Eng |
1 |
Clerical |
5 |
Market |
Total matched record=5
|
| Cross Join
|
Inner Join |
Left
Outer |
Right
outer |
Full
Join |
6x4=24 tuples |
matched record: 5 |
5 matched+1(adam/3) |
5matched+1(5/market) |
5 matched+2 unmatched. |
|
|
| Unique key VS Primary
key |
Unique key is primary key BUT can accept NULL
value |
| Composite key |
Primary key that make up of > 1 attributes |
| Candidate key
|
one or > one attributes contribute to possible Primary
key |
| Alternate key
|
Complement of candidate key ( CK can't become PK) |
| Foreign key : |
an attribute to related primary in other parent table.
|
Reference Integrity : |
Ensure relationship between tables remain consistent .
eg. can't add record
to foreign table which doesn't have assiciated PK in primary table
can't delete PK table record if thre is associated record in foreign
table. |
Back
|
|
1-to-1
|
can be implemented as a single table and rarely as two tables
with primary and foreign key relationships. |
1-to-M |
are implemented by splitting the data into two tables with primary
key and foreign key relationships. |
M-to-M |
are implemented using a junction table with the keys from both
the tables forming the composite primary key of the junction table.
( Avoid the duplicate record on the table doesnt' have foreign
key on related table )
|
| @@IDENTITY |
returns the most
recently created identity for all scope |
| SCOPE_IDENTITY() |
return the identity of the recently
added row in your INSERT SQL Statement
or Stored Procedure. (current scope) |
| IDENT_CURRENT('tbname') |
return last identity value on special
table |
Scenarios
Table A (5 rows) & B (8 rows)
, it has trigger to insert record into B IF there is
record added to A.
@@Identity will return identity row of B (9)
Scope_Identity() will return identity row of A (6)
pls . Always use Scope_Identity
to retrieve added row identity in MOST Cases
Back
Constraint ( Use
link )
| Constraints |
Examples |
| CHECK |
CREATE TABLE products (
product_no integer,
name text,
price numeric CHECK (price >
0),
discounted_price numeric CHECK (discounted_price
> 0),
CHECK (price > discounted_price)
); |
| NOT NULL |
CREATE TABLE products (
product_no integer NOT NULL,
name text NOT NULL,
price numeric NOT NULL CHECK (price
> 0)
); |
| UNIQUE |
CREATE TABLE products (
product_no integer,
name text,
price numeric,
UNIQUE (product_no)
/* column constraint */
);
OR
CREATE TABLE example (
a integer,
b integer,
c integer,
UNIQUE (a, c) /*
Table constraints */
); |
| PRIMARY KEY |
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric
);
OR
CREATE TABLE example (
a integer,
b integer,
c integer,
PRIMARY KEY (a, c)
);
|
FOREIGN KEY
(M-to-M )
Referemces is placed in foregin key table refering
back to PK table |
CREATE TABLE products (
PID integer PRIMARY
KEY,
name text,
price numeric
);
CREATE TABLE orders (
OID integer PRIMARY
KEY,
shipping_address text,
...
);
CREATE TABLE order_items (
PID integer REFERENCES
products (PID)
OID integer REFERENCES
orders,
quantity integer,
PRIMARY KEY (product_no,
order_id)
);
|
Back
|
