Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2005
    Posts
    127

    Unanswered: SET data type in Oracle???????

    In MySQL i ve a special data type called SET that can be used in table definitions. It allows the user to define a set of values for the column like,

    Code:
     col_set SET('value1','value2','value3','value4')
    here the column col_set can have zero or more values defined in the set..

    some body suggest me any equivalent data types or work arounds similar to this type in Oracle ,....

    thanks
    Sn

  2. #2
    Join Date
    Jun 2005
    Posts
    85

    Wink

    Refer here for SET datatype equivalent in Oracle :

    http://www.utexas.edu/its/unix/refer...a97249/ch3.htm

    Search google before you post, Try atleast once before you post.

    What is that "special" about SET datatype as you have said. Its also a datatype supported in mysql. You have to "WORK" around to find the equivalent

    MiraJ

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Use a CHECK constraint:

    col_set VARCHAR2(6) CHECK (col_set IN ('value1','value2','value3','value4'))

    Actually it is good practice to name constraints:

    col_set VARCHAR2(6) CONSTRAINT col_set_chk CHECK (col_set IN ('value1','value2','value3','value4'))

  4. #4
    Join Date
    Jun 2005
    Posts
    85
    MySQL allows 'value1,value2' OR 'value1,value2,value3' as values for the column !

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by MiraJasmin
    MySQL allows 'value1,value2' OR 'value1,value2,value3' as values for the column !
    Oh. In that case (thankfully) Oracle provides nothing so daft! One would have to write triggers to enforce that rule. Or perhaps consider using nested tables (yuk) or varrays (yuk).

    Then again, one could remember what "normalisation" means and create another table to hold the many values associated with the row

  6. #6
    Join Date
    Apr 2005
    Posts
    127
    Dear Mira, and Andrews

    Thanks for the help..

    Sn

    Mira,

    I did searched for the equivalent and came across the suggested page, it says there is no direct equivalent for the set data type. Hence here i requested atleast for a work around if not the equivalent.

    The SET datatype allows more than one value to the column as you ve posted in your reply. i ve not come across such a feature in any other data base and whats wrong in claiming it as a special data type ..

    Any way thanks for your kind information.

  7. #7
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    It does sound a lot like a collection type (nested table or varray), though I agree with Tony that these are better suited to programming and views than to actual tables.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by andrewst
    Then again, one could remember what "normalisation" means and create another table to hold the many values associated with the row
    totally agree

    SET is evil, and should never be used in the first place (then you wouldn't have these conversion problems to other databases)

    the solution, as tony suggests, is to define a many-to-many table which will hold the (possibly multiple) pairs of foreign keys, one key referring back to this data table, and the other key referring to a third "values" table which lists the allowable values (the same values declared in the SET)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    What's a many-to-many table?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by WilliamR
    What's a many-to-many table?
    employees
    101 todd
    102 fred
    103 biff
    104 phil

    skills
    3 html
    4 css
    5 sql
    6 php

    employeeskills
    101 3
    101 4
    103 4
    103 5
    104 5

    employeeskills is a many-to-many table

    each employee has zero or more (many) skills
    each skill has zero or more (many) employees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •