SQL & PL/SQL :: Foreign Key - Primary Key Relation

May 2, 2012

I am trying to establish PK-FK relationship between 2 tables. Table 1 has VACC_ID as a primary key. Table 2 has VACC_ID and SCH_ID as the primary keys. I am trying to add VACC_ID from Table 2 as a FK to Table 1. This is the code I used

ALTER TABLE Table_1
ADD CONSTRAINT FK_VACC_ID
FOREIGN KEY (VACC_ID)
REFERENCES Table_2(VACC_ID)

I also tried to add it without writing the query and editing the Table 1. By default, I am getting 2 columns(both VACC_ID) in the local column being referenced to the 2 primary keys(VACC_ID and SCH_ID) of table 2. I also uploaded the screeshot of the error, when I run the query.

View 4 Replies

SQL & PL/SQL :: Primary And Foreign Key On Same Column And Foreign Key Index

Aug 25, 2010

Create table Car
(Car_cd VARCHAR2(5),
Car_Desc VARCHAR2(50)
);
alter table Car
add constraint Car_PK primary key (Car_CD);
INSERT INTO Car (Car_Cd, Car_Desc) VALUES ('CORLA','COROLLA');
Commit;
[code]....

The requirement necessitates a new table to map car to manufacturer. This mapping table may later be expanded to contain other attributes Engine, MPG, etc to hold details specific to a car.But this is in future.

Create Table Car_Mapping_Details
(Car_Cd VARCHAR2(5),
Manufacturer_Cd VARCHAR2(5));

--Primary Key Constraint
ALTER TABLE Car_Mapping_Details
ADD CONSTRAINT Car_Mapping_Details_pk PRIMARY KEY (Car_Cd );

--Not able to create this as Car_cd is already a PK in this table and therefore has Unique Index
ALTER TABLE Car_Mapping_Details ADD CONSTRAINT Car_Mapping_Details_fk1
FOREIGN KEY (Car_Cd)REFERENCES Car (Car_Cd);
[code]....

But in this case the Car_Mapping_Details.Car_cd is itself is a primary key and therefore has Unique index.Although I was able to create foreign key constraint on Car_mapping_details.car_cd column (which is also Primary Key), I was not able to create Foreign Key Index on this column. It gives me Quote:ORA-01408: such column list already indexed.In other words, not creating foreign index for foreign key column lead to table-level lock? Or will the Unique Index in that primary key column be sufficient to avoid table-level lock?

View 2 Replies View Related

SQL & PL/SQL :: Primary Key / Foreign Key Joins

Sep 29, 2010

I am working on a new project in OBIEE. I am asked to do the data modeling in the database using oracle sql developer. I have to create the joins based on the requirements. I have the tables created already. But the primary keys for few tables are not defined for few tables. PK-FK joins are also not done properly.

My questions are
(1) If I have to define the primary key for the existing tables can I do that using the alter table command or should I create the table all over again and then define it?
(2) If I have to make the changes in the existing PK-FK joins how do i go about doing that?

View 11 Replies View Related

SQL & PL/SQL :: Foreign Key References Composite Primary Key

Jul 4, 2013

I have to create six tables as part of my assignment. I have created 5 tables. I am having trouble with the 6th. How can I reference a foreign key to a composite primary key? I have colored red the areas that I think are giving me trouble which is in the 6th table, the Registration Table.

Create Table Student
(
StudentID number (6,0) NOT NULL,
Student_Name varchar2 (20) NOT NULL,
Constraint Student_PK Primary Key (StudentID)
);

Create Table Faculty
(
FacultyID number (5,0) NOT NULL,
Faculty_Name varchar2 (20) NOT NULL,
Constraint Faculty_PK Primary Key (FacultyID)
);

Create Table Course
(
CourseID varchar2 (10) NOT NULL,
Course_Name varchar2 (20) NOT NULL,
Constraint Course_PK Primary Key (CourseID)
);

Create Table Qualified
(
FacultyID number (5,0) NOT NULL,
CourseID varchar2 (10) NOT NULL,
Date_Qualified varchar2 (10) NOT NULL,
Constraint Qualified_PK Primary Key (FacultyID, CourseID),
Constraint Qualified_FK1 Foreign Key (FacultyID) references Faculty (FacultyID),
Constraint Qualified_FK2 Foreign Key (CourseID) references Course (CourseID)
);

Create Table Section
(
Section_No number (5,0) NOT NULL,
Semester varchar2 (7) NOT NULL,
CourseID varchar2 (12) NOT NULL,
Constraint Section_PK Primary Key (Section_No, Semester, CourseID),
Constraint Section_FK Foreign Key (CourseID) references Course (CourseID)
);

Create Table Registration
(
StudentID number (6,0) NOT NULL,
Section_No number (5,0) NOT NULL,
Semester varchar2 (7) NOT NULL,
Constraint Registration_PK Primary Key (StudentID, Section_No, Semester),
Constraint Registration_FK1 Foreign Key (StudentID) references Student (StudentID),
Constraint Registration_FK2 Foreign Key (Section_No) references Section (Section_No, Semester, CourseID),
Constraint Registration_FK3 Foreign Key (Semester) references Section (Section_No, Semester, CourseID)
);

This is the error I am receiving:

Error starting at line 1 in command:
Create Table Registration
(
StudentID number (6,0) NOT NULL,
Section_No number (5,0) NOT NULL,
Semester varchar2 (7) NOT NULL,
Constraint Registration_PK Primary Key (StudentID, Section_No, Semester),
Constraint Registration_FK1 Foreign Key (StudentID) references Student (StudentID),
Constraint Registration_FK2 Foreign Key (Section_No) references Section (Section_No, Semester, CourseID),
Constraint Registration_FK3 Foreign Key (Semester) references Section (Section_No, Semester, CourseID)
)

Error at Command Line:8 Column:104
Error report:
SQL Error: ORA-02256: number of referencing columns must match referenced columns
02256. 00000 - "number of referencing columns must match referenced columns"
*Cause: The number of columns in the foreign-key referencing list is not equal to the number of columns in the referenced list.
*Action: Make sure that the referencing columns match the referenced columns.

View 5 Replies View Related

PL/SQL :: Possible To Maintain Primary Key And Foreign Key Relationship

Nov 16, 2012

I have a requirement like below

I have 3 tables (Prospect,customer,user) and I have a contact_dtls table .

In prospect table Prospect_id is the primary like this customer_id in customer table and user_id in the user table

in the contact_dtls table I have a column contact_id and other cols.The contact_id can be from prospect_id,customer_id and user_id in other words we can tell the prospect can be a contact or the customer can be a contact and the user also can be a contact also.

Now the question is "is it possible to maintain primary key and foreign key relationship with this design that means the prospect_id,customer_id and user_id would be the foreign keys to the contact_dtls table.

View 3 Replies View Related

SQL & PL/SQL :: Add Composite Primary Key To Table That Has Self Referencing Foreign Key

Jan 21, 2012

I have an employee table which has a primary key and a self referencing foreign key, as shown here

create table employee (
id not null,
name not null,
department not null,
supervisor_id not null
,constraint constraint_1 primary key (id)
,constraint constraint_2 foreign key (supervisor_id) references employee (id));

Now if i make the primary key composite, as shown below -

create table employee (
id not null,
name not null,
department not null,
supervisor_id not null
,constraint constraint_1 primary key (id, name)
,constraint constraint_2 foreign key (supervisor_id) references employee (id));

Oracle is throwing the following error -

ORA-02270: no matching unique or primary key for this column-list

How can this error be fixed without changing the composite primary key?

View 10 Replies View Related

PL/SQL :: Data Dictionary To See Foreign And Primary Key Of Table

Feb 17, 2013

which data dictionary should i choose to see the foreign key and primary key of table

View 2 Replies View Related

Creating List Of Items And Using Foreign Keys As Primary

May 19, 2007

How can I create a list of items in a field, for instance lets say I have a table called car and one of the sub categories is parts. How can I make it so that parts can be any number of pre-defined entities? Or even table, for instance if I make a table called parts how can I use that in the car table in place of parts?

My second question is about using foreign keys as primary keys. If I am writing an email sql DB and I decided to use the members usrname as the primary key in the member table but then made another table that lists all the emails and decided to make the foreign key member username the primary key there.

Is that safe to do or should i create a sequence in which to identify a primary key for the email list table? Also what if I extend member to several other tables and use it as a primary key there too, seems like a kind of dangerous way to do things...

View 1 Replies View Related

SQL & PL/SQL :: How To Copy Constraints (primary / Foreign Key / Indexes) To A Table

Mar 11, 2011

I have created table as below

create table emp_temp as select * from emp;

the table is created, but the constraints are not copied. Is there any way to copy all the constraints.

View 3 Replies View Related

SQL & PL/SQL :: Create Primary Key On View And Use This View For Creating Foreign Key?

Oct 8, 2010

is it possible to create primary key on view and use this view for creating foreign key .

View 3 Replies View Related

SQL & PL/SQL :: Find Non-relation Table And Relation Table?

Feb 7, 2012

Suppose i have to two tables.. emp and dept. Deptno column Foriegn for the both table. how to find the non relation table and relation table.

View 8 Replies View Related

SQL & PL/SQL :: Relation Between Two Tables?

Dec 28, 2010

oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I have a main table : CLAIMS_MAIN, in which claim_id is a primary key I have another table : CLAIMS_TRANS , in which claim_id is not a unique key, I mean, there can be more than one records with the same claim_id.. but the column transaction_id is unique, but transaction_id column doesnt exist in the main claims table.

It is required to join both these tables on claim_id...

View 1 Replies View Related

SQL & PL/SQL :: Update Hierarchical Relation

Nov 13, 2011

I have a table which contains the connectivity details. It is telecom data. The table contains the relation between the point features(Ex: Terminal) and linear features(Ex: Cable)

Below is some sample data

drop table connectivity ;
create table connectivity
(
ftr_no number(5),
ftr_id number(5),

[Code]...

-- 2nd linear ftr

insert into connectivity values (30,10359,33, 9655, 2,NULL,NULL);
insert into connectivity values (30,10359,33, 9656, 1,NULL,NULL);
insert into connectivity values (30,10359,30, 10359,3,NULL,NULL);

[Code]...

In the connectivity table PARENT_FTR_NO and PARENT_FTR_ID needs to be updated based on these conditions.

1. Correspponding to each feature there are 3 entries in this table.

2. Each feature(Point orLinear) has 2 sides INcoming and OUTgoing

3. The possible values for the RELATION column are 1, 2 and 3 where

-> 1 indicates feature conncted at the OUTgoing end
->2 indicates features connected at the INcoming end
->3 indicates self connected

4. Always a linear feature is the parent of a point feature which are connected to the OUTgoing end of the linear feature (i.e where RELATION = 1)

There is another table which contains FEATURE_NO and FEATURE_TYPE.

create table ftr_catgry
(
ftr_no number(5),
ftr_type char(1)
);

[Code]...

FTR_NO F
---------- -
33 P
31 L
30 L

Where
'L' represents linear feature
'P' represents point feature

The relation should be updated as

Step 1. Start with a linear feature, for example FTR_NO = 31

SQL> select * from connectivity where ftr_no = 31 and ftr_id = 10354 and relation = 1;

FTR_NO FTR_ID CONNECTED_FTR_NO CONNECTED_FTR_ID RELATION PARENT_FTR_NO PARENT_FTR_ID
---------- ---------- ---------------- ---------------- ---------- ------------- -------------
31 10354 33 9651 1

The feature connected to the OUTend of the 10354 i 9651 with FTR_NO = 33 and it is a point feature so the parent for 9651 is 10354

Step 2.

SQL> select * from connectivity where ftr_no = 33 and ftr_id = 9651 and relation = 1;

FTR_NO FTR_ID CONNECTED_FTR_NO CONNECTED_FTR_ID RELATION PARENT_FTR_NO PARENT_FTR_ID
---------- ---------- ---------------- ---------------- ---------- ------------- -------------
33 9651 33 9652 1

9652 is connected to 9651 at the OUTend, since 9651 is point feature so the parent of 9651 which is a linear feature is also the parent for 9652

Likewise the all relations have to be updated

Expected result after update

SQL> select * from connectivity;

FTR_NO FTR_ID CONNECTED_FTR_NO CONNECTED_FTR_ID RELATION PARENT_FTR_NO PARENT_FTR_ID
---------- ---------- ---------------- ---------------- ---------- ------------- -------------
31 10354 33 9650 2
31 10354 33 9651 13110354
31 10354 33 10354 3
33 9651 31 10354 2
33 9651 33 9652 13110354
33 9651 33 9651 3
33 9652 33 9651 2

[Code]...

24 rows selected.

View 2 Replies View Related

SQL & PL/SQL :: Parent Child Relation

Sep 17, 2013

I have one requirment like below

Table1 have records like below

parent childflag
null.....A......Y
A........B......Y
B........C
C........D
D........E......Y

I want it should be like

parent child flag
null......A.....Y
A.........B.....Y
B.........E.....Y

I want to replace 'D' (parent of 'E') with any of the upper level which has Flag 'Y' (first level parent which satisfy falg 'Y')

View 7 Replies View Related

Server Administration :: Data File / Table Relation

Feb 25, 2011

I have 2 schemas in my database with over 500 tables in each.i am trying to know which tables actually belong to which datafile in which tablespace.

View 2 Replies View Related

Forms :: Master Child Relation For Non Control Blocks

Jul 8, 2011

I am working on form builder 6i. I have a header block A(Multi record block), child block B. Both are non control blocks. My requirement is, if i put the cursor in any header record, i want to auto populate corresponding child record like in master child relation.

which trigger i need to write logic to auto populate the child record.

View 4 Replies View Related

Forms :: Master / Detail Relation / Why Populate-details Trigger Not Being Fired

Mar 14, 2013

On a form I have 2 blocks, shown on different tabs. Relation created by forms wizard. The relation works great when navigating through the blocks, the details for the master are shown fine.

Example for this form is master block is a type of food. Detail block is list of ingredients.When creating a new type of food, if it's = CAKE we insert records based on a select on another table into the ingredients table.Else, the user has to manually create the ingredients.

Upon commit of the form on master block (must be saved to enable detail block tab), the ingredients are not displayed but are there. If I execute query by hitting F8, the records are present.

Why is the populate-details trigger not being fired or what am I missing?

View 4 Replies View Related

Server Administration :: Any Relation Of Tablespace Disk Space And Actual Data Present In Tables

Aug 16, 2012

We are using Oracle 10g and have 10 tablespaces defined for our Database which have 108 tables. Size of 108 tables is around 251 MB as seen during importing the dump. While creating these 10 tablespaces I used below parameters for allocation of space

SIZE 1M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE 1M;

which set the initial space for 10 tablespaces to around 1032Kb each. Now my Question is after importing the dump , how the disk space for 10 tablespaces increases to 398 MB in total ?

Is there any relation of Tablespace disk space and Actual Data present in the tables ?

View 18 Replies View Related

SQL & PL/SQL :: Add Automatic Value In Foreign Key?

Nov 7, 2011

if have table primary key in this table empno and another table foreign key is empno and when add in the primary key 5522 how can

5522 in the empno "foreign key" automatic

View 5 Replies View Related

SQL & PL/SQL :: Replacing A Foreign Key With Another

Jul 26, 2011

I have an already populated table that refers to a wrong foreign key in another table. I have table abc that has the column fk_id. this column currently refers to column x_id1 in the table abc while it should refer to column x_id2 in xyz. I am trying to replace the data but I guess I miss something in the logic or the right way of doing it:

following is my trial:

create table abc
(
pk_id number(4) primary key,
abc_name varchar2(10),
fk_id number(4)
);

create table xyz
(
x_id1 number(4),
x_id2 number(4),
x_name varchar2(10)
);

commit;
insert all
into abc values (10,'ab1',1)
into abc values (11,'ab2',2)
into abc values (12,'ab3',5)
into abc values (13,'ab4',7)
into abc values (14,'ab5',9)
into xyz values (1,1,'d1')
into xyz values (2,2,'d2')
into xyz values (5,3,'d3')
into xyz values (7,4,'d4')
into xyz values (9,5,'d5')
select * from dual;
commit;

--this following select returns 5 rows

select * from abc, xyz where abc.fk_id = xyz.x_id1;

-- the following update only updates 3 rows and sets the other 2
-- to null!

update abc set fk_id = (select x_id2 from xyz where xyz.x_id1 = abc.fk_id);
commit;

select * from abc;

View 3 Replies View Related

SQL & PL/SQL :: Not Finding The Foreign Key?

Dec 4, 2011

I'm not really sure why oracle is not finding my Foreing Key, I'm creating an easy set of table for a company and I'm declaring all Primary keys and foreing keys as necessary and this is my

CREATE TABLE Items
(ItemID NUMBER(5) NOT NULL,
Description VARCHAR2(30),
ListPrice NUMBER(9,2) NOT NULL,
QtyOnHand Number(5),
ReorderPoint NUMBER(3),
ReorderQty NUMBER(3),
OnBackOrder Number(3),
CONSTRAINT ItemsPK

[Code] ............

There's three sets of inserts the first two successfully inserts the data NO problem but when i try to insert the data on SupplierItems comes as:

ERROR at line 1:
ORA-02291: integrity constraint (jcarter.SUPPLIERITEMSFK2) violated - parent key not found

I looked on the forum and it appears this happens when someone is doing a Insert ALL but this is not my case..

View 2 Replies View Related

SQL & PL/SQL :: Foreign Key Constraint

Feb 3, 2012

I have a parent table called dept and one child table emp which is referenced on deptno and now both have data . Now i want to truncate the dept table and i have truncated emp table after it when i am trying to truncate dept table then oracle throws a error that is-

"ORA-02266: unique/primary keys in table referenced by enabled foreign keys" ,

but when i tried to delete some records 1 by 1 then these have delete but truncate is not still working. why? if i have 10 millon records.

View 4 Replies View Related

PL/SQL :: Foreign Key Index

Mar 13, 2013

You should almost always index foreign keys because they are frequently used in joins. In addition, if you intend to delete or update unique or primary keys on the parent table, you should index the foreign keys to improve the locking of child records.what I don't understand are, I should create index on foreign key column in parent table or child table or both ?

View 1 Replies View Related

During Primary (old Standby) To Standby (old Primary) Switchover Hang?

Apr 4, 2013

I am using RAC to RAC data guard environment. Os hp-unix. during primary(old standby) to standby(old primary) switchover I am ussing:

SQL>ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;

I have 11.2.0.1 environment with standby and when performing switch over - it was hanging for more than 2 hours and i had to cancel it. This is what all i see in the database alert.log:

Errors in file /u02/app/oracle/diag/rdbms/drpdb/drpdb1/trace/drpdb1_m000_15160.trc:
ORA-01155: the database is being opened, closed, mounted or dismounted
Thu Apr 04 17:44:03 2013
Errors in file /u02/app/oracle/diag/rdbms/drpdb/drpdb1/trace/drpdb1_m000_15780.trc:
ORA-01155: the database is being opened, closed, mounted or dismounted
Thu Apr 04 17:59:03 2013
Errors in file /u02/app/oracle/diag/rdbms/drpdb/drpdb1/trace/drpdb1_m001_16373.trc:

[code]....

ORA-01155: the database is being opened, closed, mounted or dismounted.

View 1 Replies View Related

SQL & PL/SQL :: Foreign Key View In Oracle

Sep 6, 2011

Is there any view in oracle which gives the foreign key mapping.

E.g.

Table DEPT has DEPTNO as PK.
Table Emp has DEPTNO as FK to DEPT.DEPTNO.

I can get the information for DEPT table from R_CONSTRAINT_NAME column of all_constraints table.My requirement is to get the the which column of Emp table refers to DEPT.DEPTNO column .

View 3 Replies View Related

SQL & PL/SQL :: Locking And Foreign Key Indexes

Sep 1, 2013

Say we have an employee(id_emp) table with a primary key on id_emp. We have also some history tables emp_stuff with columns say (id_emp, dat_event, some_stuff) with primary key id_emp, dat_event.

This means that we have a unique index on (id_emp,dat_event). We also have a foreign key id_emp that references employee(id_emp). When we update id_emp on employee, we still have a lock on emp_stuff. According to this (end of the page) :

Quote:So, in short, with releases prior to Oracle Database 11g Release 1, you will want an index on the foreign key of the child table if you do any of the following:

Update the parent table primary key
Delete from the parent table
Merge into the parent table

So is id_emp in emp_stuff considered as indexed (through the unique index of the primary key) or do we have to add an explicit index
like this CREATE INDEX emp_stuff ON emp_stuff(id_emp) to avoide child table locks?

View 33 Replies View Related

SQL & PL/SQL :: Fetch Information For A Foreign Key

Jul 13, 2010

the view from where we can fetch information for a foreign key viz. reference table, referenced columns etc.

View 3 Replies View Related

SQL & PL/SQL :: Why Are Foreign Key Not Deferral By Default

Apr 18, 2013

In my point of view, deferral FK (DFK) are stronger than non-deferral FK (NDFK). In other words, every NDFK can be moved to DFK. Is there a performance issue ?

View 2 Replies View Related

SQL & PL/SQL :: Interchange The Value Which Has Foreign Key Dependency

Mar 29, 2011

I need to interchange the value which has foreign key depednecy,

For ex: I have a table dept

deptno| dname|dseq|
10 | CS | 1
20 | SC | 2

and in emp table

eno | ename | deptno |
1 |Raj | 10 |
2 |Ram | 10 |
3 |Vip | 10 |
4 |Muk | 10 |
5 |Sn | 20 |
6 |Snj | 20 |
7 |Asj | 20 |

there is a forign key define :

ALTER TABLE emp ADD (
CONSTRAINT rop_fk
FOREIGN KEY (deptno)
REFERENCES dept (deptno));

Now I want to change the deptno from 10 to 20 and 20 to 10 in dept table . So the ouput will be like

deptno| dname|dseq|
20 | CS | 1
10 | SC | 2

when i am deleting that row I am getting error "ORA-02292: integrity constraint (ROP_FK) violated - child record found".IF i am trying to update i am getting unique key contractin failure.

(Even I cannot update the rest of the column values in dept table as it is also having foreign key dependency) i need to put a sql script in production so that it will interchange.