i 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 RepliesI have table EMP & DEPT.
Can you give me sample "check" constraint so that when I enter an EMP (with status='active') without matching DEPT entry, the process will fail?
It will allow to enter EMP with status='inactive' only even if no matching DEPT entry. So foreign_key will not apply.
I've successfully created the below two tables (disregard my use of "char" variable type; beyond the scope of my question):
create table Test_req (
Req_ID varchar2(4) not null,
Req_Comment varchar2(50) null
);
create table Test_n_cycle (
Req_ID varchar2(4) not null,
[code].....
Then I successfully performed the two alter statements:
Alter table test_n_cycle
add constraint test_n_cycle_pk primary key (req_id, test_id);
Alter table test_req
add constraint test_req_pk primary key (req_id);
Then tried this freign key reference statement
Alter table test_n_cycle
add constraint
foreign key (req_id) references test_req (req_id)
Error report:
SQL Error: ORA-00902: invalid datatype
00902. 00000 - "invalid datatype"
Is this because req_id is also defined in the Primary key definitions on these two tables, thus making the field not valid for referential constraint definition?
By default the DBMS_STATS package runs once every 24 hours to collect statistics for database objects and Oracle collects new statistics when enough of the data (about 10%) has changed.
My question here is how to check the table has changed 10% in database?
What is the trigger which should be used to check certain field value after posting the query.
Example:
I have executed the query and the records are fetched. There is one field I want to check if it is null then it should be enabled, else, keep it disabled.
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?
the query, to get the foreign key constraints and related table fro give table.
View 2 Replies View Relatedin a table how many foreign keys can we create.
View 10 Replies View RelatedThere 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.
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?
I 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.
I 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]....
which data dictionary should i choose to see the foreign key and primary key of table
View 2 Replies View RelatedI 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.
Am 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
I have a table called Customer_Type with following fields
Customer_type ,Active_date, Inactive_date
regular,11/01/2011
daily,11/04/2011
monthly,11/05/2011/11/11/2011
Tbale 2:Customer
Customer_name,Customer_type,Customer_Inactive_date
John,regular,
James,monthly,
Jake,daily,
Jill,monthly
What i wnat is to update the Customer_inactive_date with the Incative_date field from Customer_type based on their Customer_type... So james and Jill would have their rows updated in this scneario ..How can i achive this in pl/Sql
I have teh code using merge function..I want something in traditional old fashion..
The sql statements are below
CREATE TABLE CUSTOMER_TYPE
(
type_code VARCHAR2(10),
[Code]....
I want to put one check box to check all the check boxes.how can i do this?
View 5 Replies View RelatedoracleDatabase version:11gR2
Os:solaris
how to check locks on database objects .it might be any lock? any select sql query to check database object levlel locks?what basis locks will occur?
We can use the clause "CREATE CLUSTER mycluster.." to create a cluster.
How to check if the cluster exist in my database, so I don't have to create the cluster again.
i did the following steps for resolving.
1 alter database backup controlfile to trace;
2 startup nomount;
3 recover database using backup controlfile;//in this step generate error msg "database not mounted"
4 alter database open// this is also happend above error
ORA-01122: database file 1 failed verification check
ORA-01110: data file 1: '/u01/app/oracle/oradata/DB11G/system01.dbf'
ORA-01207: file is more recent than control file - old control file
How to check the open http connections in the database as I am getting.
ORA-29270: too many open HTTP requests
I am running Oracle Database 10g R2 on windows 2003, I want to create a batch file to check if the database is idle or not, and if it is idle shut it down and start it up.
View 13 Replies View RelatedI have a script like this:
------------------------------------------------------------
DROP TABLE CON_TEST CASCADE CONSTRAINTS ;
CREATE TABLE CON_TEST (
IDI NUMBER(10,0),
USERID VARCHAR2(10),
PWD VARCHAR2(10),
NOTE VARCHAR(100)
)
----------------------
But i think if table CON_TEST doen't exist, an error message will appear. I know that in SQL Server we can check if table exists or not. So, i wonder if we can do that in Oracle?
By the way, is there any way to run a file script that contents TABLES, STORED, ... on a developed PC connect to oracle db server? (in case, i'm developing on PC, using Net Service Name to conect to Oracle DB Server)
I'm working on a feature that needs to check every table for data. At the moment I am roundtripping every time and running the query like this:
SELECT 1 FROM dual WHERE EXISTS(SELECT 'x' FROM table_name)
Is there a better way that allows me to get the result for all tables in one trip? I've tried a correlated subquery like this:
SELECT table_name, (SELECT 1 FROM dual WHERE EXISTS(SELECT 'x' FROM table_name) FROM all_tables
but I think that doesn't work because Oracle looks for a table called table_name rather than the value from the outer query.
How to check table properties? (desc only shows if it is null)If I do desc emp, it only shows the Employee description. But Doesnt tell if table is having primary key or not?
View 2 Replies View Relatedi want a query/function/procedure to check recently updated columns/tables in a database...
View -1 Replies View RelatedHow to check whether database is dedicated or shared ?
View 1 Replies View RelatedActually in our environment , sysadmins are upgrading java versions on Unix (HP & Solaris) Machines and they have asked the query whether oracle database uses Java or not?
If it uses then all the databases need to be shut down for their planned activities and if not no need to shutdown the databases.
provide me the query to check our database is running using spfile or running using pfile.
View 2 Replies View RelatedI created a table but I want to add the Unique check to it as I forgot to apply it to the table when I created it.Is it possible to make the field Unique after having created the table or do I have to drop the table and re-create it?
View 3 Replies View Related