add constraint feature

dattydatdat · #1 (**permalink**) 10-29-11, 07:26

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

shammat · #2 (**permalink**) 10-29-11, 09:57

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

dattydatdat · #3 (**permalink**) 10-29-11, 10:22

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!!

Internet Services

Web Development

Digital Marketing

Consumer Tech