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
ADVERTISEMENT
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 :: 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 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 :: 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 :: 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 :: 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 View RelatedPL/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 RelatedSQL & 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.
PL/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
Server Administration :: Find When Database Role Created And Who Has Created?
Feb 14, 2013Is there a way to find when was a database role created and who has created?
View 5 Replies View RelatedREFRESH GROUP Is Automatically Created When A Materialized View Is Created?
Aug 17, 2012tell me if a REFRESH GROUP is automatically created when a materialized view is created?
View 3 Replies View RelatedSQL & PL/SQL :: How To Know Index Is Created On Table Or Not
Jun 30, 2011how to know weather procedure is working or not in a database ..
how to know index is created on table or not.
SQL & PL/SQL :: Created Table With Date In Database
Feb 22, 2011Me working on Oracle application Express,there are lots of tables created in it, how will i get to know who created which table and on which date.
View 7 Replies View RelatedSQL & PL/SQL :: Make Partition Of Table Which Is Already Created
Sep 21, 2011I want to make partition of table which is already created and has huge amount of Data I have done indexing already
my structure are as follows
EMPLOYEE CHAR(10),
MONTH_YEAR NUMBER (6),
TYPE CHAR (1),
SALARY_CODE CHAR (5),
AMOUNT NUMBER (16,2)
Indexing on Employee,Month,Salary_Code
How can I do partitioning on this table.
SQL & PL/SQL :: How To Find Who Created The Table In Oracle
Feb 8, 2011somebody has created the table , who had created the table Note: not required the schema name,
View 4 Replies View RelatedSQL & PL/SQL :: Date Search - No Index Created On Table
Feb 25, 2010DT1 is a column of date datatype and there is no index created on the table
I want to add the below lines in the sql
TO_CHAR(DT1,'YYYY') BETWEEN '2005' AND '2009'
Which one should I use in where condition to query and why?
1. TO_CHAR(DT1,'YYYY') BETWEEN '2005' AND '2009'
2. DT1 BETWEEN '01/01/2005' AND '31/12/2009' (as NLS date format will not change)
3. DT1 BETWEEN to_date('01/01/2005','dd/mm/yyyy') AND ('31/12/2009' ,'dd/mm/yyyy')
Forms :: Use Created Table In Data Block?
May 1, 2013I have data that the user is modifying on the form and a data block that is supposed to reflect those change (summaries) So I created
TYPE VehicleInfo IS RECORD (Vehicle_Desc ctl_fleet_vehicles.Vehicle_Desc%Type,
Miles timecapture.miles%Type,
Hours TimeCapture.Equipment_hours%Type);
TYPE VehicleTab IS TABLE OF VehicleInfo
INDEX BY BINARY_INTEGER;
VehicleObj VehicleTab;
I fill this up with the correct data, but I would like to be able to use this as a table in the data block (called Vehicle_Summary) In the past I have piped tables into data blocks from the server, but I'm unsure how to use a table I created in forms in the Query Data Source Name of the data block.
PL/SQL :: Created A Table With CLOB As Datatype For One Of Columns
Aug 5, 2013<ORACLE VERSION : 11.2.0.2.0> i have created a table with CLOB as datatype for one of the columns, I am trying to store a string ( I am not sure about the length of the string) , when i am querying on my table for the CLOB column,instead of the actual string "(HUGECLOB)" is coming. How to get the actual string in case the problem is with the SIZE.
View 1 Replies View RelatedPL/SQL :: Created Partition And Parallel Degree 4 On Table A
Oct 10, 2012I have table A with size 120 Million and two more tables are of size 2 Million on Table B and less than 1 Million size on table C.I had created Partition and Parallel degree 4 on the table A. Created table B with Parallel degree 2 and Created table C with NOPARALLEL.
My query is using above tables with joins and inserting into table D using HINT /*+ APPEND NOLOGGING*/I had executed the explain on the above criteria the cost is showing 20767.
Later created Tables A,B and C with NOPARALLEL. Applied HINT on Table D /*+ APPEND NOLOGGING*/ and als applied HINT /*+ PARALLEL(A, 4) PARALLEL(B C, 2) on select query which uses to insert into Table D.
My question which is best practice on PARALLEL degree creation at table level or query level:
a) Creating table with Paralle (degree 4)
b) Applying HINT /*+ PARALLEL (TABLE A , 4) */ at query level
SQL & PL/SQL :: Employee Table In Scott Schema Created By Default
Oct 3, 2011I am using Oracle 11g Release 11.2.0.1.0
OS: Windows
I am using the Employee table in Scott schema which created by default.
10,20,30,40,50 are the department ids.
I would like to have the output like below. I am having lots of values in dep id and lots of employees in each dept ids
Emp name Dep ID
John 20
Mike 40
Ram 10
Guru 50
Kumar 30
Forms :: Created Block For Table On Tabular Style - Validating Item?
Oct 10, 2011I created a block for EMP table on tabular style. if in first line empno entered is 1then i don't want to allow the end user to enter 1 in second line. but when-validate-item is not working because first_record and next_record is not allowed. I need to validate before saving the data into emp table and immediately after empno is entered into empno item.
View 16 Replies View RelatedServer Administration :: Event Alert Created Successfully On Custom Table But Not Firing?
Mar 8, 2013I have created custom table in inventory and after register table & columns, i create event alert on it. event alert is not firing.
View 1 Replies View RelatedSQL & PL/SQL :: Exception In Procedure / Created One Procedure Based On One Table Item Master
Mar 6, 2010I have created one procedure based on one table item master which has a field called item stock or non stock based on this i will fetch data from one of two tables .If its a stock item data will be retrieved from wip_main_acnt table and if its non stock it will pick from ns_main_acnt.my procedure is working fine but all i need is i just want to put an exception that if data is not found in one of the table based on the item selected.I am confused which one to be used whether no_data_found or notfound%.
CREATE OR REPLACE PROCEDURE dflt_pr_acnt (
l_item_code IN VARCHAR2,
l_main_acnt_code OUT VARCHAR2
)
[code]....
SQL & PL/SQL :: Add Automatic Value In Foreign Key?
Nov 7, 2011if 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
SQL & PL/SQL :: Replacing A Foreign Key With Another
Jul 26, 2011I 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;
SQL & PL/SQL :: Not Finding The Foreign Key?
Dec 4, 2011I'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..
SQL & PL/SQL :: Foreign Key Constraint
Feb 3, 2012I 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.
PL/SQL :: Foreign Key Index
Mar 13, 2013You 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
