Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Unanswered: add constraint feature

    hi,

    I was wondering if it is possible to add a constraint to a column so that all entries in that column start with the letter S. The data type is char(4).

    I've tried using the following, and although it seems to work, when I then insert values into my table it comes back as an error because of said constraint.

    alter table staff add constraint staff_ID_start check (staff_ID like 'S%');

    if it helps, the error I get when attempting to insert a char(4) beginning with S, is this "Check constraint violation STAFF_ID_START table: STAFF"

    thanks!

    Dat

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Works for me:
    Code:
    psql (9.1.1)
    Type "help" for help.
    
    postgres=> create table staff (staff_id char(4) not null);
    CREATE TABLE
    postgres=> alter table staff add constraint staff_ID_start check (staff_ID like 'S%');
    ALTER TABLE
    postgres=>
    postgres=> insert into staff (staff_id) values ('STAF');
    INSERT 0 1
    postgres=> insert into staff (staff_id) values ('XXXX');
    ERROR:  new row for relation "staff" violates check constraint "staff_id_start"
    postgres=>
    You should show us the full SQL statement you are running (similar to what I did: copy & paste your session from psql - and please use [code] tags to make the SQL readable

  3. #3
    Join Date
    Oct 2011
    Posts
    2
    hmmm yeh I didn't think there was actually anything wrong with my SQL. I think it has something to do with the program that I'm using. I'm using OpenOffice Database for this assignment at uni, and to be honest, it's really sh!t.

    *sigh* oh well, it's good to know I was actually doing it right. Stupid OpenOffice..

    Thanks!!

Posting Permissions

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