Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2009
    Posts
    3

    Unanswered: Sybase Unique Constraint

    I have to a requirement to add a new column in an existing Sybase Table "employee"
    The column can have null values but the not null value have to be unique
    When trying to specify a column level UNIQUE constraint I get error
    as follows:
    failed. Default value cannot be NULL when adding a non-NULL column.

    I have tried following alter statement
    ALTER TABLE employee ADD sap_id varchar(32) DEFAULT NULL UNIQUE

    ALTER TABLE employee ADD sap_id varchar(32) UNIQUE

    but nothing works

    Can somebody help me please

  2. #2
    Join Date
    Jun 2009
    Location
    India
    Posts
    50
    please try this:

    ALTER TABLE employee ADD sap_id varchar(32) DEFAULT NULL
    SP_bindefault xyz, "employee.sap_id"

    Could you please be more clear on above. Do you want each column valuse to be unique with other row. OR you need a defailt value to be populated?

  3. #3
    Join Date
    Jul 2009
    Posts
    3
    I want the sap_id to be unique.
    employee table is an existing table with 10000 records.
    sap_id is a new column that is to be added in employee table.
    There are only 2000 employees with sap_id
    and rest do not have the sap_id so the value will be null in DB
    But the 2000 employees that have sap_id id the value should be unique.
    This is easily achievable in Oracle as Oracle allows us to define UNIQUE constraint on a column but Sybase is not allowing me to create a column level unique constraint.

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    ALTER TABLE employee ADD sap_id varchar(32) NULL DEFAULT NULL UNIQUE
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by puja.sharma
    I want the sap_id to be unique.
    employee table is an existing table with 10000 records.
    sap_id is a new column that is to be added in employee table.
    There are only 2000 employees with sap_id
    and rest do not have the sap_id so the value will be null in DB
    I would put the new column in a new table. Nullable unique constraints don't make much sense. If the values are unique then what is the point of populating the column with nulls for some values that don't exist?

    However, it is true that Oracle follows the SQL standard on nullable UNIQUE constraints whereas Sybase does not.

  6. #6
    Join Date
    Jul 2009
    Posts
    3
    Hi aflorin27 , the command does not work.

    dportas , the whole ideas is the sap_id will be populated only if the user has been given access to the SAP Application. All employees are not allowed to access this application.

  7. #7
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    How about creating a new table for employees with sap access
    with columns sapid and empid
    Now you can create a unique key on sapid
    and have a foreign key from your employee table

  8. #8
    Join Date
    Dec 2007
    Location
    London, UK
    Posts
    741
    Quote Originally Posted by puja.sharma
    dportas , the whole ideas is the sap_id will be populated only if the user has been given access to the SAP Application. All employees are not allowed to access this application.
    Exactly. So you actually have TWO sub-classes of employee and you should add en extra table to represent that. Sap_id can be non-null and unique in that table:

    CREATE TABLE employee_sap
    (employee_id INT NOT NULL PRIMARY KEY
    REFERENCES employee (employee_id),
    sap_id VARCHAR(32) NOT NULL UNIQUE);

  9. #9
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Now I saw this:
    SyBooks Online

    where it says:
    If there is a duplicate key value or if more than one row contains a null value, the command fails, and Adaptive Server prints an error message giving the duplicate entry.

    So:
    - in Sybase, a UNIQUE constraint allows only ONE null value - so there is no way to do what you want to do (meaning ALTER TABLE and add a new unique column)
    - Yes, this is different from Oracle or DB2 (where the UNIQUE constraints is applicable only to not NULL values)
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

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