SQL & PL/SQL :: Difference Between Primary And Natural Key

Jul 5, 2011

what is the difference between primary key and natural key ?

View 3 Replies


ADVERTISEMENT

SQL & PL/SQL :: Difference Between Primary Key And Unique / Not Null

Dec 10, 2010

what is difference between primary key and unique+not null

View 12 Replies View Related

Natural Join With ON Clause?

Apr 22, 2012

CODESQL> select * from em1;

EMPID NAME SALARY
---------- ---------- ----------
1060 sam 4000
1061 rose 3700
1062 sona 4800

SQL> select * from dept;

EMPID NAME DEPT_NAME
---------- ---------- --------------
1060 sam INFO TECH
1061 rose BIO INFO
1063 chris COMP SCI
1064 maya MULTI MEDIA

I am TRYING to get output for on clause( NATURAL JOIN)

CODESQL> select x.empid,x.name,x.salary,y.dept_name from em1 x NATURAL JOIN dept y
2 on x.empid=y.empid;
on x.empid=y.empid
*
ERROR at line 2:
ORA-00933: SQL command not properly ended

[code]...

My questions are

** I think why NATURAL JOIN key word throws error.
** Second query succeed. i think it is inner join. am i right ??????
** If i execute query without alias why oracle throws error ???? Example shown below

I saw lot of examples like this
SQL> select empid,name,salary,dept_name from em1 natural join dept
2 on em1.empid=dept.empid;
on em1.empid=dept.empid
*
ERROR at line 2:

ORA-00933: SQL command not properly ended

View 1 Replies View Related

PL/SQL :: Calculate Difference By Which Day Count Of Difference Is Going?

Oct 17, 2012

Detail table will look like below:

Product_id issue_date action_date Force_date
1 10/10/2012 10/10/2012 10/10/2012
2 10/10/2012 10/10/2012 10/10/2012
3 10/10/2012 13/10/2012 15/10/2012
[code]....

Need the data like

Issue_date count_action_date count_Force_date (diff(action_date,force_date) 1 2 3 4 5 6(days since over)

10/10/2012 3 4 1 4 2 1 0 0

How to get the data like this? automatically how to get 123.... and how to calculate the difference by which day the count of difference is going?

View 3 Replies View Related

During Primary (old Standby) To Standby (old Primary) Switchover Hang?

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

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 View Related

PL/SQL :: Primary Key Value Updation

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

PL/SQL :: How To Create Primary Key Like W1

Jul 10, 2012

primary key should be

W1
W2
W3 like this.

1,2,3 are sequence numbers.

View 2 Replies View Related

PL/SQL :: Primary Vs Surrogate Key

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

PL/SQL :: Composite Primary Key?

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

Finding Primary Key In A Table?

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

SQL & PL/SQL :: Auto Number For Primary Key

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

SQL & PL/SQL :: Foreign Key - Primary Key Relation

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

SQL & PL/SQL :: Primary Key Violation Error

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

SQL & PL/SQL :: Index Related To Primary Key

Jul 12, 2012

What is the default key created when a primary key is created in a table ?

View 1 Replies View Related

SQL & PL/SQL :: Querying From A Primary Composite Key

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

SQL & PL/SQL :: Table Partitioning With No Primary Key

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

Windows :: Set One Oracle As Primary Out Of 2?

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

SQL & PL/SQL :: Updating A Duplicate Primary Key

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

SQL & PL/SQL :: How To Check If Primary Key Exists

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

PL/SQL :: How To Set Primary Key For A Date Column

May 28, 2013

i need to set primary key for a column consists of the datatype date.

View 5 Replies View Related

PL/SQL :: Retrieve Primary Key Information

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

SQL & PL/SQL :: Name Of Primary Key Constraint Is ID_PK

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

SQL & PL/SQL :: Primary Key / Foreign Key Joins

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

PL/SQL :: Design Tables Without Primary Key?

Aug 13, 2012

If you were asked to design a schema, would you create a table (however irrelevant) without a primary Key ? Say for example, a 'non-business' table like an Error logging table which logs exceptions thrown from stored procedures, would you create this table without a primary key ?

View 7 Replies View Related

Add Partial Primary Key Into Oracle Table?

Oct 17, 2006

how to add a primary key into the oracle table but how do i add a partial primary key?

View 2 Replies View Related

Dataguard Setup Primary And Standby

Dec 2, 2011

I have a dataguard setup, a primary and a standby.my instance is testdb.i have set all necessary dataguard parameters and everything seemed to work well initially with logs being moved from primary to secondary and being applied there.but all of a sudden i see that the primary pushes logs to the standby and at the standby end these logs are available but not applied.

when i try to execute the: alter database recover managed standby database cancel; it hangs for a long time and never returns to sql prompt.

i bascially want to stop and restart the MRP process to apply the logs but cannot cancel the managed recover mode.Is it advisable to kill the mrp process at os level using "kill -9 "?

View 7 Replies View Related

Primary / Standby Database Not Synced?

Jul 10, 2013

we are getting below error on alert log file in logical standby database from May 2013 how can find time of sequence creation and how can restore these sequence redo files and sysnc databases primary and standby

Snipped of alert log:
Wed Jun 10 15:29:40 2013
Fetching gap sequence in thread 1, gap sequence 62079-62178
Wed Jun 10 15:29:50 2013
Fetching gap sequence in thread 1, gap sequence 62079-62178
Wed Jun 10 15:30:01 2013
Fetching gap sequence in thread 1, gap sequence 62079-62178

View 8 Replies View Related

Forms :: How To Deal With Primary Key While Using For Loop In 6i

Jun 3, 2013

I am having template table as

template_name varchar2(100) -- primaykey
ex_col varchar2(100)
tab_colvarchar2(100)

Now on the form 6i i've two columns with following data

Column AColumn2 B
-------------------------------------
route a_route
time a_time
id a_id

and a text field where the name of the template will be entered.

e.g.

text filed - testing

In template table i want to insert above values, so template table will look like this

template_name ex_col tab_col
-----------------------------------------------------
testing route a_route
time a_time
id a_id

since tempate_name is primary key, i how should i construct for loop for insert.

View 25 Replies View Related

SQL & PL/SQL :: Audit Table With Primary Keys?

Feb 19, 2013

I need to copy the changed and deleted data in an other table. I have searched this site ,asktom and other sites also. I found the following solution from asktom website. But it gives me the changed columns data only and i need the primary key with changed data and deleted rows also.

DROP TABLE emp;
CREATE TABLE emp AS (SELECT * FROM scott.emp);
CREATE TABLE audit_table

[Code].....

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved