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
ADVERTISEMENT
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
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
View Related
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
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
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
Feb 17, 2013
which data dictionary should i choose to see the foreign key and primary key of table
View 2 Replies
View Related
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
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
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
Aug 4, 2012
I have one table as per the attachment, with id(Primary key),level,name and Srno.Now I want to maintain Sr. if user transfers level 2 of one block (level 1) to another block.is it possible through any oracle function.
View 8 Replies
View Related
Jun 15, 2012
I do need to mantain a table with the las 13 of a month, for example:
ID Month_ID MONTH_MAT
1 201206 201106
2 201206 201107
3 201206 201108
4 201206 201109
5 201206 201110
[Code]....
Kind of a year-to-date table but this one is 13 months one.
I have been trying to create the code from scratch
I have thought of using a Function:
Query: SELECT TRUNC(ADD_MONTHS(SYSDATE, -13),'MM') FROM dual;
Result: 01-MAY-11
View 6 Replies
View Related
Aug 29, 2012
We need to maintain the day 0 value of special character "ÿÿ" for application. If i use the special character directly in sql file , it is getting inserted as junk character "?"
If i use workaround by using chr(255) function i am getting below behavior.
select chr(255) from dual
---------------------------
ÿ
However below query gives null value.
select chr(255)||chr(255) from dual
View 5 Replies
View Related
Oct 17, 2012
What is the good practice to maintain RMAN catalog ?
RMAN backup and recovery is fine but to delete old backups it takes a lot of time.
Even if I delete backups manually,RMAN takes a lot of time to delete expired backup command.
What can be done to increase the speed of delete obsolete backup and delete expired backup operations ?
View 2 Replies
View Related
Feb 18, 2011
I was considering a solution to maintain a replicated copy of a database in a remote office. However we are using SE One edition of oracle, so native support for dataguard is not available. There definitely should be some scripting solutions for this task, but I can't find any to date.
View 7 Replies
View Related
Jan 11, 2013
There is a scenario: I dropped all objects of 3 users and dropped other 2 users and then I checked the space from dba_segments it reflects decreased space which is perfectly fine. Problem is that when I checked the space of physical datafile on disk it remains same. How can I restore or shrink or regained the space after dropping objects and users to maintain my storage requirements.
View 1 Replies
View Related
Oct 18, 2012
I want to know how many managers have employees under him?
I mean to say one manager may have multiple employees from employees table.
View 7 Replies
View Related
Mar 7, 2013
I have one RDBMS concept question.if i have 2 tables with 0 to many realtionship. can i create referential integrity constraint between them?
In other words how can i tell whether the records in table A(child table) are not orphan records and have 0 to many relationship with table B(parent table) ? Right not there is no constraint defined on both tables.
View 3 Replies
View Related
Apr 29, 2011
I've a table (RelationshipX) with two columns with following values. The table represents the relationships. When I run the following query, It will give me all the combination of relationships...however, I need to get 15 unique as defined below.
Col1Col2
1106011060
1106011640
1106011142
1106011095
1106013029
1106014058
I run the following query to get below of all the combinaiton (note, I am opting out those six rows which matches to each other) select a.Col2 as Col1, b.Col2 as Col2 from RelationshipX a, RelationshipX b
where a.Col1 = 11060
and a.Col1=b.Col1
and a.Col2 <> b.Col2
order by Col1, Col2
[code]....
HOW can I modify my SQl so I get only 15 unique relationship records. (For example two UNDERLINE rows are technically same, and there are total 15 of them)???.
View 1 Replies
View Related
May 13, 2011
Just wanted to pose a question Can a (Unique and Not nullable)Alternate key of the parent table be part of child Table in an Identifying relationship i.e part of primary key of a child table????
Is there a rule that a primary keys of a parent table can be used to build a identifying relationship with its child?
View 1 Replies
View Related
Apr 27, 2010
I have a table which has 2 one to many relationships like a tree structure in the same table, is there a way that I can show the same on the form and be able to do the insert and update on the same table?
View 10 Replies
View Related
Mar 6, 2011
Quote: Originally Posted by Silverj2k11
ALTER TABLE CONTRACT
ADD FOREIGN KEY (client_id)
REFERENCES client(client_id);
Didn't work! I am now losing my mind.
ORA-00904: "CLIENT": invalid identifier You do have a table named CLIENT?
View 7 Replies
View Related
Oct 11, 2013
I have been told to create a query which should give out the same output as the UI of an exception management application . However , many referenced tables have one to many relationship . I created a query using a function which was giving the required output . But the performance of the query was not good as for each row . Is there a method to create a select using joins to show one to many relationship in a table .
View 4 Replies
View Related
Feb 6, 2012
Would there be any problem / error when we import the dump file (which has parent table) on target database ?
View 11 Replies
View Related
Feb 24, 2007
the relationship on my script.What I want to do is make it possible to assign many courses to a student.But I have read a few books on foreign keys and primary keys, and I still dont get how to go on doing this....
This is how the table creation with the keys looks right now...
CREATE TABLE CAMPUS (
CAMPUSID NUMBER(4) NOT NULL,
NAME VARCHAR2(80) NOT NULL,
CONSTRAINT CAMPUS_PK PRIMARY KEY (CAMPUSID));
[code]...
View 3 Replies
View Related
Aug 8, 2011
I have a table called FAMILY which holda the data like , for father_gen_1 --->father_gen_2 --> father_gen_3 -- son.
father_gen_1 father_gen_2 father_gen_3 son
ABC XYZ GEF MNC
ABC lmn kth
I want to get the output which is a three column heirarical table and holds the parent child relationship Like following:-
id name parent_id
1 ABC NULL
2 XYZ 1
3 GEF 2
4 MNC 3
5 LMN 1
6 KTH 5
View 7 Replies
View Related
Mar 5, 2010
i'm trying to display the hierarchical relationship between the tables (parents-child-subchild).
[b]table structure[/b]
DEPT
|PK-DEPT_ID
|
EMP
|pk-EMP_ID
|fK emp_DEPT_fK((FK column is dept_id references dept table dept_id column))
[code]....
but by using below query i am not getting complete heirarichy.
SELECT LEVEL,
Table_Name,
Constraint_Name,
R_Constraint_Name ,
SYS_CONNECT_BY_PATH(Table_Name, '/') Path
[code]....
View 2 Replies
View Related
May 22, 2012
I have a DB that has one parent table with many child tables. I would like to delete all records(child - parent) based upon a particular "net_id". I have three versions that "seem" to work BUT I'd like to use "version2" because I want to go to the parent one time(for the parent delete I just made it simple for me) only and be able to delete the child record(s) from the child table(s). I want to achieve this w/o using triggers...
My question: Is version2 an "ok" way to delete child records or should I try another method(version1 or 3)??
create table PARENT
(
event_id NUMBER,
event_title VARCHAR2(50),
net_id VARCHAR2(16)
);
--truncate table parent;
insert into parent
(event_id, event_title, net_id)
values
(1, 'title1', 'CURLY');
[code]...
View 10 Replies
View Related
Jun 28, 2012
I was just wondering how is the relationship between MEMORY_TARGET and SGA_MAX_SIZE parameters, if exists any at all.I set my memory parameters as:
# Enable AMM
MEMORY_MAX_SIZE=10G
MEMORY_TARGET=4G
# Disable ASMM
SGA_TARGET=0
SGA_MAX_SIZE=0
So I bounce my instances and when I checked the parameter SGA_MAX_SIZE, Oracle automatically set a value of 2456M to it:
sga_max_size big integer 2456M
sga_target big integer 0
Reading MOS ID 443746.1, there is no clear mention about this relationship. It seems that Oracle automatically set SGA_MAX_SIZE as 60% of MEMORY_TARGET.
In am running Oracle 11gR2 (11.2.0.2.3) under Red Hat Linux.
View 10 Replies
View Related
Feb 21, 2012
I have a form that contains a tab canvas. On the second canvas page I have two data blocks with a master detail relationship.The first block (master) is driven by a table (table_one) with the following fields:
sf_id (PK)
sf_code - VARCHAR2(40)
sf_desc - VARCHAR2(250)
The second block (detail) is a repeating block driven by a table (table_two) with the following fields:
error_code - VARCHAR2(40
error_code_desc - VARCHAR2(250)
is_major_error - VARCHAR2(1)
error_type - VARCHAR2(1)
error_is_active - VARCHAR2(1)
update_date - DATE
sf_id (FK - table_one.sf_id)
The second block only shows error_code and error_code_desc. The point of this canvas page is to add/remove master records and associate detail records with a master. I never want to actually insert or remove records from table two; merely updating the sf_id column in table two.What I am trying to achieve is the following:
1. Create a master record with detail records at the same time.
2. Delete a master record
3. Add and remove detail records
An insert into the detail block is nothing more than associating a master record with a detail record by updating the sf_id column in table two. A delete from the detail block is nothing more than setting the sf_id column to null in table two. I am not actually adding or removing records in table_two.
I have tried overriding the commit on the second block to perform updates instead of Oracle actually trying to insert NEW records into table_two.
View 2 Replies
View Related
