Oracle Training To Help Make Your

Systems Better, Faster and Smarter

For more valuable Oracle tips and tricks just subscribe to our ezine and every month we'll send you some tips and tricks to help you make your Oracle systems better, faster and smarter.

Contents

• How to create foreign key constraints in Oracle From: Apoorva - I am doing SQL / PLSQL course for Oracle. Right now, I am doing SQL and have came across a problem. Following is the SELECT statement : CREATE TABLE title_copy ( copy_id number(10), title_id number(10), status varchar2(15) CONSTRAINT title_copy_status_nn NOT NULL, CONSTRAINT title_copy_status_chk CHECK(status IN ('AVAILABLE', 'DESTROYED', 'RENTED', 'RESERVED')), CONSTRAINT title_copy_titleid_fk FOREIGN KEY(title_id) REFERENCES title_copy(copy_id), CONSTRAINT title_copy_pk PRIMARY KEY(copy_id, title_id)); I am getting following error : REFERENCES title_copy(copy_id), * ERROR at line 12: ORA-02270: no matching unique or primary key for this column-list  Please help... The foreign key constraint has to reference another (different) table. You can't refer to the same table that you're creating. The whole point of the foreign key is to ensure that any value entered in one table already exists in the reference table thereby preventing “orphan” records. For example, let's assume you have in your Oracle database a table called dept with 2 columns: dept_id and dept_name to hold department details and you want to create a table called emp with 3 columns: emp_id, dept_id and emp_name to hold employee details. To ensure that you don't assign employees to a non-existent department you would create a foreign key on emp.dept_id to reference dept.dept_id as follows:- CREATE TABLE emp ( emp_id number(10) PRIMARY KEY, dept_id number(10), emp_name varchar2(60) NOT NULL, CONSTRAINT emp_fk FOREIGN KEY(dept_id) REFERENCES dept(dept_id); For more help, see the many free articles and tutorials we have on our site or consider taking some of our formal Oracle training courses.  --------------------------------------- Looking to sky-rocket productivity, save time and reduce costs?Training is a highly cost-effective and proven method of boosting productivity.  Smartsoft offers instructor-led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training.  See our scheduled Oracle training courses, or let us know your requirements. Oracle tips and tricks Subscribe to our newsletter, jam-packed full of tips and tricks to help you slash costs, sky-rocket productivity and make your systems better, faster and smarter. © Smartsoft Computing Ltd, Bristol, England Need help? Contact Us This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy.     View Smartsoft privacy policy Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.