Foreign Key Constraints

Foreign Key Constraints:

A foreign key always references a primary key. It introduces a Referential Integrity into the table.

 

E.g. (Foreign Key)

 

SQL> CREATE TABLE emp(

empno NUMBER(4),

ename VARCHAR2(10) NOT NULL,

job VARCHAR2 (9)

hiredate DATE,

sal NUMBER(7,2)

comm NUMBER(7,2)

deptno NUMBER(7) NOT NULL,

CONSTRAIN emp_deptno_fk FOREIGN KEY (deptno)

REFERENCES dept (deptno));

 

Output:

Table created.

 

ON DELETE CASCADE:

This keyword can be used to make sure all foreign key references are deleted once the primary key reference is deleted.

Without this option, the primary key in the parent table cannot be deleted if it is referenced as a foreign key in the child table.

 

CHECK Constraint: Condition for row.

 

E.g. (CHECK)

 

SQL> CREATE TABLE emp(

empno NUMBER(4),

ename VARCHAR2(10) NOT NULL,

job VARCHAR2 (9)

hiredate DATE,

sal NUMBER(7,2)

comm NUMBER(7,2)

deptno NUMBER(2)

CONSTRAINT dept_deptno_ck CHECK (deptno BETWEEN 10 AND 99));

 

Output:

Table created.

VIEWS:

  • To restrict database access
  • To make complex queries easy.
  • To present different views of the same data.

 

NOTE:

When views are created by using joins, they cannot be modified.

 

E.g. (VIEWS)

 

SQL> CREATE VIEW empnv

AS (SELECT empno, ename, job

FROM emp

WHERE deptno = 10);

 

Output:

View created.

 

 

Student Exercise1:

 

The CREATE or REPLACE VIEW clause allows us to modify the view if it exists. Modify the previous view by giving the COLUMN ALIASES of employee, name & job title.

Output:

View created.

 

 

Rules For Performing DML on Views:

 

DATA cannot be modified in a view if,

  • It contains group functions.
  • It contains a GROUP BY clause.
  • It contains a DISTINCT keyword.
  • Columns defined by expression.
  • Contains NOT NULL columns in base tables that are not selected by the view.

 

NOTE:

To remove a view, use the drop view command.

 

 

Student Exercise:

DELETE the previous VIEW.

 

SQL> drop view empnv;

 

Output:

View dropped.

 

 

Student Exercise 1:

CREATE a view named dept20 that contains the empno, ename & deptno. or all employees in dept20. Label the view column emp_id, employee & ept_id.

 

 

Student Exercise 2:

Use the dept20 view to display the names of all employees whose names end with an ‘s’.

 

SQL> SELECT * FROM dept20

WHERE employee LIKE ‘S%’;

 

Output:

EMP_ID EMPLOYEE     DEPT_ID

——— ———- ———

7369 SMITH             20

7788 SCOTT             20

 

SEQUENCES:

  • They are number generating objects that are used to generate numbers for primary key.

 

Example: (SEQUENCES)

 

SQL> CREATE SEQUENCE dept_deptno

INCREMENT BY 1

START WITH 91

MAXVALUE 100

NOCACHE

NOCYCLE;

 

Output:

Sequence created.

 

USING SEQUENCES:

 

dept_deptno.NEXTVAL

dept_deptno.CURRVAL

 

These functions are commonly used.

 

Student Exercise 3:

Create a sequence by your name, that it starts with100, has the maximum value 1000 & is incremented by 10. Use the NEXTVAL & CURRVAL functions.

 

 

ALTER SEQUENCE:

 

  • Command is used to change the sequence.

 

 

Student Exercise 4:

ALTER the previous sequence so that it is incremented by 20.

 

 

DROP SEQUENCE:

 

  • Command is used to delete the sequence.

 

 

Student Exercise 5:

DROP the previous sequence.

 

 

 

Advertisements

2 thoughts on “Foreign Key Constraints

  1. I truly love your blog.. Pleasant colors & theme. Did you create this web site yourself?

    Please reply back as I’m looking to create my own personal website and would love to know where you got this from or exactly what the theme is named. Many thanks!

    • I created my blog myself and likes to upload it on weekly basis. You can view the theme name written on my blog.
      the material written in my blog is all crafted by myself .
      Thanks for appreciating it.

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