SQL & PL/SQL :: Primary Key On A View
Dec 20, 2011
While making a view.
1. Is it advisable to have primary key in a view.
2. Why can't we have a primary key in a view, what are its advantages. I mean one cannot search a view by index.
3. Is it because of hibernate, but again i have used distinct keyword in a view declaration which means i cannot insert via view.
Here is my code for view to make a primary key in a view.
CREATE OR REPLACE FORCE VIEW VU_NAME
PRIMARY_KEY, NAME_ID, ADDRESS_ID
)
[Code]....
alter view VU_NAME add constraint vemp_pk primary key (PRIMARY_KEY) disable
View 3 Replies
ADVERTISEMENT
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 29, 2012
I created a materialized view with REFRESH FAST WITH PRIMARY KEY, which I assume generated its own PK for the materialized view (well, something certainly did, and it wasn't me); however, I did not include USING INDEX TABLESPACE <tablespace>, so the PK was generated in the same tablespace as the materialized view itself. The "problem" is, this particular database has a separate tablespace into which all of the indexes are supposed to go.
Is there any way, short of dropping and recreating the materialized view (which took 24 hours in the first place), to move the system-generated PK to a different tablespace?
For that matter, is there a reason why I wouldn't want to do this?
View 3 Replies
View Related
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
Jan 17, 2013
can we change the existing materialized view to normal view? if yes how?
View 2 Replies
View Related
Dec 11, 2012
I'm trying to create a Materialized View on a remote database from a simple view. The reason is, the data owners don't want to grant explicit tables privileges to external subscribers.
A new schema is created to publish data in the form of a view. I've created mlogs on the master tables, and granted them to the subscriber, but it's still complaining about a missing primary key on the view. A primary key does exist in the master table.
Is there another work around for this situation without having to work inside the data sources' environment?
View 5 Replies
View Related
Apr 25, 2012
We wrote one data load process to load GZ files into Database.during process we will change client facing view definitions to backup table so that we can work on base tables.
This view definition changes are related to FROM and WHERE clause (not columns/type). during load process, client/user may connect to current server and accessing these views. My question is what will be the reflection of changing view definition while user is accessing view?
I created a scenario-
STEP1: Created a view-
create or replace view view_01 as
select object_name from dba_objects union all
select object_name from dba_objects union all
select object_name from dba_objects union all
[code]....
View definition is replaced by new definition while select is executing on that view. select returned number of records as per view definition one.
View 6 Replies
View Related
Jul 28, 2010
Are oracle view have Dynamic view function?
View 8 Replies
View Related
May 2, 2012
I have a materialized view "pro_mview",I am trying to refresh the MVIEW by using the following statement.
EXEC DBMS_MVIEW.REFRESH('pro_mview','C');
But I am getting the below error.
*
Error at line 1:
ORA-12008: error in materialized view refresh path
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2256
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2462
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2431
ORA-06512: at line 1
I am able to fetch the data from that materialized view(pro_mview).
View 3 Replies
View Related
Apr 26, 2011
difference between view and materialized view?
View 1 Replies
View Related
Aug 12, 2013
what would be the difference between a view and a materialized view? whether DML possible on a view? i think error occurs if DML tried on a view which is a combination of two or more tables, whether DML possible on a materialized view?
View 7 Replies
View Related
Jul 13, 2012
I have a table called Order_Mstr
order_id order_Desc
-------- ----------
1 Order1
2 Order2
3 Order3
4 Order4
Child tables referring Order_id are:
Order_child1
Order_child2
Order_child3
Order_child4
Order_child5
Order_child6
.
.
.
Order_child50
I have to update Order_id with some 8 digit number generated by some algorithm. And i have to update that value in child tables as well. In how many ways i can do this task. for example..
1) Disabling the constraint and updating Master value and child value.
View 5 Replies
View Related
Jul 10, 2012
primary key should be
W1
W2
W3 like this.
1,2,3 are sequence numbers.
View 2 Replies
View Related
Apr 17, 2013
what is the difference between these two and in where we use Primary key and where we choose Surrogate Key.How to understand the difference between these two keys.
View 7 Replies
View Related
Jun 26, 2012
understanding composite primary key.
I know what is composite key and how it stores the data.. but i am not able to find when i have to choose the columns to be composite keys.
View 10 Replies
View Related
Dec 1, 2011
Can we create view in another view
View 10 Replies
View Related
Oct 4, 2007
Whats the command to show the name of the primary key in a table using oracle sql plus?
View 1 Replies
View Related
Feb 19, 2011
I have set up a schema using a DDL script in Oracle 10g. I am linking this through to a Visual Basic 2008 fron end. The connection is fine. My database of is for a car hire company project.
I need to know how to create an auto increment for a primary key field, like Access does.
For example here is my table structure for my Rental_Payment table.
-- TABLE RENTAL_PAYMENT
CREATE TABLE RENTAL_PAYMENT (
PAYMENTNO NUMBER NOT NULL,
CARDNO NUMBER NOT NULL,
[Code]....
So for example the first record would appear as:
PAYMENTNO CARDNO AMOUNT CARD_TYPE
======== ===== ====== =======
01
I would then enter values and the next payment number would be 02 etc.
PAYMENTNO CARDNO AMOUNT CARD_TYPE
======== ===== ====== =======
01 123412 £40.00 VISA
02
Visual Basic will usually have a star (*) beside a new entry field so 02 will be
* 02
etc........
I have about creating a sequence. So how can I tie this to my table?
View 12 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
Jun 25, 2013
In my application I am facing peculiar error. For easy understanding I am considering emp table. In my package P1 I have two procedures Proc1,Proc2.
Proc1 : It is receiving employee details and for checking if employee already exists it is calling Proc2.
Proc2 : Here it checks if employee exists. If exists it deletes old record, commit and inserts new record in sequence.
But however in few instances Proc2 is returning unique constraint (CONSTRAINT_NAME) violated. I am very much confused why this error is coming.
View 12 Replies
View Related
Jul 12, 2012
What is the default key created when a primary key is created in a table ?
View 1 Replies
View Related
May 20, 2010
I have the following 3 columns in my VERSIONS table.
MAJOR
MINOR
MICRO
The MAJOR,MINOR and MICRO columns create a unique composite primary key. I need to query out the last version by doing the following 3 queries and combine them.
select max(major) as max_major from versions
select max(minor) as max_minor from versions where major = :max_major
select max(micro) as max_micro from versions where minor = max_minor and major = max_major
Is there any way I can query out max(major),max(minor),max(micro) in a single query.
View 7 Replies
View Related
Jan 24, 2013
we apply partitioning concept on a table which don't have any primary key ?
I just want to add one more field as primary key with some sequence generated values while partitioning ? Is it possible ?
View 7 Replies
View Related
Jul 13, 2010
On my system 2 oracles are installed for different applications one having version 8.0 & other 10.0.
The oracle 10.0 is required for Windchill application. When I am trying to access the sqlplus by windchill shell it access the Oracle 8.0 sqlplus. how to set one oracle as primary on system so i am able to access the oracle 10 sqlplus in windchill shell.
View 4 Replies
View Related
Oct 8, 2012
PM_KEY.. PCN....... JOBNO...PM_VERNO
20......... 137....... XX23..... 0
21......... 137....... XX24..... 1
22......... 137....... XX17..... 2
23......... 137....... XX81..... 3
22......... 137....... XX90..... 2
I have a dilemma......the constraint was disable somehow in my table of about 900,000 records which allowed the insertion on duplicate primary keys as well as duplicate records. I've managed to get rid of the duplicate records, but I haven't quite figured out how to update the primary key and version number. I've tried the following but
UPDATE TABLE
SET PM_PM_KEY=(MAX(PM_KEY)+1), PM_VERNO=(MAX(PM_VERNO)+1)
WHERE TBLDATE= MAX(TBLDATE)
ORDER BY TBLDATE ASC
GROUP BY PCN;
The query is failing with
Error at Command Line:2 Column:19
Error report:
SQL Error: ORA-00934: group function is not allowed here
00934. 00000 - "group function is not allowed here"
*Cause:
*Action:
View 6 Replies
View Related
Jul 5, 2011
what is the difference between primary key and natural key ?
View 3 Replies
View Related
Jan 19, 2010
I have a table called TRANS, and a primary key field tran_id. How would i check if there is a record matching tran_id 'DUP7927' ?
View 25 Replies
View Related
May 28, 2013
i need to set primary key for a column consists of the datatype date.
View 5 Replies
View Related
Dec 20, 2012
How can i retrieve which tables have composite primary keys, and only one of the primary key columns is a foreign key to another table?
Like:
CREATE TABLE club (
clubId NUMBER,
name VARCHAR(20) NOT NULL,
PRIMARY KEY (ClubId)
);
CREATE TABLE team (
teamid NUMBER(10),
clubid NUMBER(10),
teamname VARCHAR(10) NOT NULL,
PRIMARY KEY (teamid, clubid),
FOREIGN KEY (clubid) REFERENCES club (clubid));So in this case, the team table
View 4 Replies
View Related
Jul 31, 2010
I have a table say EMP with 2 fields ID and Name, with ID being the primary key. The name of the primary key constraint is ID_PK.Now my scenario is :
1. Rename the table EMP to EMP_TEMP;
2. Create an empty table EMP
3. If (data successfully loaded into EMP)
{
Drop EMP_TEMP;
}
else
{
Drop EMP;
Rename EMP_TEMP to EMP;
}
The problem here for me is the primary key constraint. When I am in 2, I cannot create the primary key constraint again since the old one is still in tact. If I go ahead to drop the original pk constraint and re-create a new one in 2, then in case of else part , I need to get the constraint again while renaming the original table.
View 6 Replies
View Related