Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2007
    Posts
    29

    Unanswered: Unique constraint in sybase

    Hi Experts,
    Can U help me to understand about the unique constraint in sybase? How it differs from oracle? How to use unique constraint?

    Thanks in advance,
    shiya

  2. #2
    Join Date
    Mar 2007
    Posts
    29
    Hi Experts,
    Please help me regarding the Unique constraint in Sybase.
    How it differs from oracle? How to implement it?

  3. #3
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    I don't know how it is different from Oracle...

    The UNIQUE constraint is implemented via a unique index (Oracle does the same thing, AFAIK), which will abort any attempt to insert duplicate values.

    I don't really see much more to say about it...

    Michael

  4. #4
    Join Date
    Mar 2007
    Posts
    29
    Thank U Michael.

    Now I would like to know what is the difference between the primary key and unique key?

    I think the main difference could be 'Primary key does not allow null values' but the 'unique key allow null values'.

    If so, in sybase, will the column defined as unique key has the not null constraint?

    I have tried it with an example but it is not imposing the not null constraint.
    Please help me with an example.

    Thanks in advance,
    Shiya

  5. #5
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    Well, conceptually a PK should never allow NULL values, so it would make sense.

    On the other hand, a UNIQUE constraint will only allow a single row with NULL, so it really isn't all that different.

    And of course both the UNIQUE and PK constraints are implemented via a UNIQUE, so it really comes down to the same thing.

    Michael

  6. #6
    Join Date
    Mar 2007
    Posts
    29
    Hi Michael,
    Thank U for your reply.

    Regards,
    Shiya

  7. #7
    Join Date
    Apr 2007
    Posts
    8
    all the above is correct. the one null insert acts as a unique identifier for that row coz if u try to insert another nullable row u get the same error message as you would entering a row with the same value
    eg unique constraint on emp_id
    emp_id, emp_name
    1 'anderson'
    1 'roberts' ---> attempt to insert duplicate row error
    null 'peter'
    null 'adams' ---> attempt to insert duplicate row error

    one difference between a unique constraint and primary key is the way optimiser will cost the query. assessing a nullable columns makes the optimiser less optimistic

  8. #8
    Join Date
    Apr 2007
    Posts
    8
    to impose the not null constraint you have to use the create table/alter table command
    Example:
    if the table (test) and column (emp_id) already exist and the respective column allows null, do the following
    alter table test modify emp_id not null

    then create unique constraint on column

Posting Permissions

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