1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Select with constraint names

Discussion in 'SQL PL/SQL' started by Peter Roman, Jan 18, 2017.

  1. Peter Roman

    Peter Roman Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Portugal
    In a query to an Oracle database, referencing 2 tables, can I use primary/foreign keys that relate like 2 tables (with WHERE clause).
    The question I ask is:
    - If there is a CONSTRAINT NAME that identifies these keys, can I use it instead of referring all the keys?
    - Is there a problem with your use?

    example:
    Code (SQL):
    SELECT *
    FROM table1, table2
    WHERE table1.field1 = table2.field2
    AND table1.field3= table2.field4;

    fictional / wrong example:
    Code (SQL):
    SELECT *
    FROM table1, table2
    WHERE constraint_name = 'nameLink';
    Thanks.
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    748
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    A description of constraints of contains in the database dictionary
    For example: ALL_CONSTRAINTS

    N.B. You can't use by yourself the constraints for join operation.
     
    Last edited: Jan 18, 2017
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,564
    Likes Received:
    362
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No. You name constraints so YOU know what they reference, not so Oracle can let you use them to write queries. SQL doesn't know one constraint from another; the OPTIMIZER knows this and uses the associated indexes to provide an efficient execution plan.

    No, there isn't a problem with my use; did you mean THEIR use? If that is what you meant then yes, there is a problem if you try to write a query and use the constraint name directly in the WHERE clause like your example shows:

    Code (SQL):
    SELECT *
    FROM table1, table2
    WHERE constraint_name = 'nameLink';
    Such code will generate errors such as 'invalid column'. Again the constraint names are there for YOUR benefit, not Oracle's.
     
  4. Peter Roman

    Peter Roman Starter

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    Portugal
    Thanks for your help.