PL/SQL :: Data Dictionary To See Foreign And Primary Key Of Table
Feb 17, 2013which data dictionary should i choose to see the foreign key and primary key of table
View 2 Replies
ADVERTISEMENT
SQL & PL/SQL :: Add Composite Primary Key To Table That Has Self Referencing Foreign Key
Jan 21, 2012I 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?
SQL & PL/SQL :: Primary And Foreign Key On Same Column And Foreign Key Index
Aug 25, 2010Create 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?
SQL & PL/SQL :: How To Copy Constraints (primary / Foreign Key / Indexes) To A Table
Mar 11, 2011I 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.
SQL & PL/SQL :: Foreign Key - Primary Key Relation
May 2, 2012I 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.
SQL & PL/SQL :: Primary Key / Foreign Key Joins
Sep 29, 2010I 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?
SQL & PL/SQL :: Foreign Key References Composite Primary Key
Jul 4, 2013I 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.
PL/SQL :: Possible To Maintain Primary Key And Foreign Key Relationship
Nov 16, 2012I 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.
Data Dictionary - Find All References Of A Table?
Sep 14, 2011I wanna to DROP a table called EMPLOYEES, but when I execute the DROP TABLE EMPLOYEES, I get a ERROR saying that I cant do it because this table do reference to another table(s).
I tried to use the DBA_CONS_COLUMNS and DBA_CONSTRAINTS data tables, but its not enough to find it.
How can I find these references?
SQL & PL/SQL :: Table Is Missing In User_segments Data Dictionary Table
May 20, 2012In my database I found some anomalies like
SQL> select SEGMENT_NAME, SEGMENT_TYPE, BLOCKS, EXTENTS, BYTES/1024
2 from user_segments
3 where SEGMENT_TYPE='TABLE'
4 ORDER BY SEGMENT_NAMe;
[code]....
The countries table is missing in user_segments data dictionary view.But I can queries the countries using select statement.
SQL> SELECT * FROM COUNTRIES;
CO COUNTRY_NAME REGION_ID
-- ---------------------------------------- ----------
AR Argentina 2
AU Australia 3
BE Belgium 1
BR Brazil 2
[code]...
why the country table is missing in user_segments data dictionary table.
Creating List Of Items And Using Foreign Keys As Primary
May 19, 2007How 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...
SQL & PL/SQL :: Create Primary Key On View And Use This View For Creating Foreign Key?
Oct 8, 2010is it possible to create primary key on view and use this view for creating foreign key .
View 3 Replies View RelatedSQL & PL/SQL :: Data Dictionary View Not Available
Nov 1, 2011Question 1) I have read the following statement in a PL/SQL book.
Quote:To check whether an existing procedure is compiled for native execution or not, you can query the following data dictionary views:
[USER | ALL | DBA]_STORED_SETTINGS
[USER | ALL | DBA ]_PLSQL_OBJECTS
However, I when i query the view USER_PLSQL_OBJECTS I get the following error message:
Quote:ORA-00942: table or view does not exist
Question 2) I have read the PLSQL_WARNING can be set to DEFERRED at the system level. However, I am unable to defer it. tell me how to apply defer caluse to following statement:
Quote:ALTER SYSTEM SET PLSQL_WARNINGS ='DISABLE:ALL'
SQL & PL/SQL :: Can Modify Oracle Data Dictionary
Feb 6, 2011can i modify oracle data dictionary
View 6 Replies View RelatedSQL & PL/SQL :: DML Sentences Role In Data Dictionary?
May 21, 2012Changing Data dictionary by DML Sentences (e.g Insert, Delete, Update,...), Will be possible? Or just by DDL Sentences?
View -1 Replies View RelatedList Only Date Columns In Data Dictionary / Views
Sep 6, 2012Oracle version : 11.2.0.3
Some DBA_views are huge with lots of columns ; a DESC command's output becomes less readable ; example DESC command's output of V$SESSION view.
Lets say I want to see only the date columns in a DBA_view or V$view ; How can I do that in a DESC like output or similair ?
Performance Tuning :: Data Dictionary Cache - Dc_objects
Jul 4, 2011In order to improve the performance of our live server, I am trying to do an exhaustive comparison with our test environment which is quite quick in spite of the fact that we port the data from Live every month.
There are no obviously slow queries appearing in the the top SQLs of AWR, we have optimised such things already. Right now it is about general uplift rather than SQL based tuning.
I picked up random SQLs and I noticed a marked differences in the execution time. Typically they are 3 to 4 times and there are cases much more than that.
1. I observed that, while the explain plan of the queries are same, trace of the queries give a different picture. I have observed that the recursive calls, consistent gets and sorts(memory) are quite high on Live.
2. I have no solid reasons to say this but my instincts tell me that the recursive calls is the major contributing factor. It is sometimes 2000+ for an SQL.
3. On googling more on that, it finally made me compare the data dictionary on the AWR report of test and Live.
The dc_objects caught my eyes. In that 4 hour AWR, there were about 10 million get requests and the pct miss was ~10. For similar load, the test server had 5 million gets with 0.08 PCT miss for 4 hours.
Server Administration :: Possible To Write Triggers On Data Dictionary Tables And Views
Sep 30, 2011Is it possible to create trigger on the various tables and views exists (i.e. dynamic performance views) in data dictionary, when ever any DML operations performs by Oracle it self?
View 6 Replies View RelatedSQL & PL/SQL :: Get Foreign Key Constraints And Related Table For Give Table
Mar 18, 2010the query, to get the foreign key constraints and related table fro give table.
View 2 Replies View RelatedHow To Make System Table Space Extent Management Dictionary
Dec 21, 2010I want to create system table space's extent management dictionary with the syntax:
CODEcreate database
logfile
group 1 ('/u01/app/oradata/anand/redo1a.log') size 100M,
group 2 ('/u01/app/oradata/anand/redo2a.log') size 100M,
group 3 ('/u01/app/oradata/anand/redo3a.log') size 100M
datafile '/u01/app/oradata/anand/system.dbf' size 400M extent management dictionary
sysaux datafile '/u01/app/oradata/anand/sysaux.dbf' size 300M
default temporary tablespace temp tempfile '/u01/app/oradata/anand/temp.dbf' size 50M
but it is giving error
ERROR at line 6:
ORA-25141: invalid EXTENT MANAGEMENT clause
how can I make system tablespace's extent management dictionary?
PL/SQL :: How Many Foreign Key Can Be Created On Table
May 3, 2013in a table how many foreign keys can we create.
View 10 Replies View RelatedForms :: Table B Has Foreign Key Reference To Table A
Jan 11, 2012There are 2 tables : Table A and Table B.Table B has foreign key reference to Table A.There are 2 forms in the application based on table A (form 1) and table B (form 2).
Now when we open form 2, the functionality is such that it acquires a lock on table B for the selected record during the search criteria. Lock is acquired by using "select 1 from table_B where column = :column for update no wait".So when the form 2 is opened by any other user/session and same record is tried to be selected, then an exception is raised to the user that the current record is being edited by some other user and does not allow him to edit that record.
Now imagine if User has opened screen 2 (One record in Table B has been locked). With this lock existing, we open form 1, and click a button which performs a COMMIT_FORM. At this point the form hangs. On checking the locked objects, there is a lock on both table A and table B. When the Form 2 is closed, then the Form 1 which was hanging a while ago starts responding.
When the foreign key relationship is dropped and the above scenario is tried, there is no issue encountered. Form 1 works fine even if form 2 is open.We are not supposed to drop the Foreign key relationship as well.
SQL & PL/SQL :: Implement Foreign Key On Column Of Table From 2 Tables
May 29, 2011I need to implement the foreign key on a column of a table from 2 tables. My requirement is in bellow.
drop table t1;
create table t1 (slno number, acc_no number);
drop table t2;
create table t2 (acc_no number primary key, acc_name varchar2(100));
drop table t3;
create table t3 (acc_no1 number primary key, acc_name1 varchar2(100));
[code]...
It is provided that the values of acc_no in t2 and acc_no1 in t3 are unique.Now it required that while inserting into t1 , the system will check either t2 or t3 tables.
PL/SQL :: Foreign Key Can Be Implemented In One Table And Cannot Be Duplicated To Work
Oct 17, 2013I have now created my tables and now I want to populate them with my data but I get errors below is my full sql command. From last time I learned that one foreign key can be implemented in one table and cannot be duplicated to work around this I changed my foreign key as you can see to "fkone" . but why am I getting errors when trying to add information I mean I have not gotten errors in the first two tables so what am I "doing wrong/ not understanding"
CREATE TABLE CUSTOMER (CUSTOMERNAME varchar (25),STREET varchar (25),CUSTOMERCITY varchar (25),CONSTRAINT CUSTOMER_pk PRIMARY KEY (CUSTOMERNAME)); CREATE TABLE DEPOSIT (CUSTOMERNAME varchar (25),BRANCHNAME varchar (25),ACCOUNTNUMBER int,BALANCE decimal (7,2),CONSTRAINT DEPOSIT_pk PRIMARY KEY (ACCOUNTNUMBER)); CREATE TABLE LOAN(CUSTOMERNAME varchar (25),BRANCHNAME varchar (25),LOANNUMBER int,AMOUNT decimal (7,2),CONSTRAINT LOAN_pk PRIMARY KEY (LOANNUMBER)); CREATE TABLE BRANCH (BRANCHNAME varchar (25),BRANCHCITY varchar (25),ASSETS decimal (7,2) ,CONSTRAINT BRANCH_pk PRIMARY KEY (BRANCHNAME)); ALTER TABLE DEPOSITadd
[code]....
PL/SQL :: How To Check Where Table Field Was Used As Foreign Key In Database
May 9, 2013i have a field in my table office i got field office_code ,this field is been used in diffirent tables as foreign key is there a sql i can wirte to see all tables who have used this field as foreign key.
View 12 Replies View RelatedPL/SQL :: Foreign Key - Creating Table Based On Some Integrity Constraints - Not Working?
Dec 20, 2012Am creating a table based on some integrity constraints, but it's not working.
CREATE TABLE member
(
member_id NUMBER(10),
last_name VARCHAR2(25) NOT NULL,
first_name VARCHAR2(25),
[code],,,
Error:
Error report:
SQL Error: ORA-02270: no matching unique or primary key for this column-list
02270. 00000 - "no matching unique or primary key for this column-list"
*Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table.
*Action: Find the correct column names using the ALL_CONS_COLUMNScatalog view
Data Guard :: Unable To Get Data Of Primary In Standby Database (dataguard)
Jan 16, 2013i have configured physical standby in my local system, to check logshipping i created a table at primary db, wen i tried to check in standby, it says table does not exist..below are primary & standby alert entries..
Primary alert log
Fatal NI connect error 12514, connecting to:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=172.16.0.98)(PORT=1522))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=STAND)(SERVER=dedicat ed)(CID=(PROGRAM=d:oracle11gappadministratorproduct11.1.0db_1inORACLE.EXE)(HOST=A960M)(USER=SYSTEM))(SERVER=dedicated)))
VERSION INFORMATION:
TNS for 64-bit Windows: Version 11.1.0.6.0 - Production
[code]....
Data Guard :: Unable To Get Data Of Primary In Standby Database
Jan 16, 2013I configured dataguard in my local system.
1) scn differs wrt primary in standby (i checked, 1day difference), how to make scn same?
2)i created a table in primary, its not refelecting in standby, (below i ve pasted alertlog entries)
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.
Errors in file d:oracle11gappadministratordiag
dbmsstandstand racestand_dbw0_6916.trc:
ORA-01157: cannot identify/lock data file 2 - see DBWR trace file
ORA-01110: data file 2: 'D:ORACLE11GAPPADMINISTRATORORADATASTANDSYSAUX01.DBF'
[code]....
3)wen i try to open standby database in read only mode gives below error..
ERROR at line 1:
ORA-16004: backup database requires recovery
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1:
'D:ORACLE11GAPPADMINISTRATORORADATASTANDSYSTEM01.DBF'
SQL & PL/SQL :: Primary Constraint On Table Affecting Procedure To Insert Rest Of Rows In Table?
Jun 12, 2012primary key constraint on transaction_dtl_bk is affecting the insertion of next correct rows.
CREATE OR REPLACE PROCEDURE NP_DB.san_po_nt_wnpg_1 (
dt DATE
)
IS
v_sql_error VARCHAR2 (100); -- added by sanjiv
v_sqlcode VARCHAR2 (100); ---- added by sanjiv added by sanjiv
[code]...
All Columns Of A Table Are Part Of Primary Key Column Of That Table?
Feb 22, 2011if a table contains two columns and both are part of the primary key of that table (Kind of obvoius).
should i opt for a index organized tbale in this case ?Or should i opt for another running sequential colum which would serve as a primary key of this table and define the actual two columns of the system as unique keys.
there is a drawback if a most of the tables of a database contain composite primary keys?