Re: [DOCS] CONSTRAINT on ARRAY ELEMENTS
Hey Nikolaos, 2012/11/3 Nikolaos Ikonomopoulos > > CREATE TABLE employ_presence > ( > p_id character(6) not null, > p_month character(3) NOT NULL, > *statuscode* integer array[7], > CONSTRAINT unq_employ_presence UNIQUE (p_id, p_month), > CONSTRAINT chk_employ_month CHECK (p_month = ANY (ARRAY['Jan'::bpchar, > 'Feb'::bpchar, 'Mar'::bpchar, 'Apr'::bpchar, 'May'::bpchar, 'Jun'::bpchar, > 'Jul'::bpchar, 'Aug'::bpchar, 'Sep'::bpchar, 'Oct'::bpchar, 'Nov'::bpchar, > 'Dec'::bpchar])) > ); > > > > How can add a CONSTRAINT on *statuscode* array elements to accept values > between 0 to 5 > > 0 = Employ present > 1 = Employ Patient > 2 = day off > 3 = Regular vacation > 4 = external job assignment > 5 = external job assignment abroad > 6 to 9 for future use > > Thanks. > I recommend you to create the table "status" and create foreign key constraint in the table "employ_presence" instead. This will do exactly what are you want. -- // Dmitriy.
Re: [DOCS] CONSTRAINT on ARRAY ELEMENTS
On Sat, 2012-11-03 at 09:43 +, Nikolaos Ikonomopoulos wrote: > > CREATE TABLE employ_presence > ( > p_id character(6) not null, > p_month character(3) NOT NULL, > statuscode integer array[7], > CONSTRAINT unq_employ_presence UNIQUE (p_id, p_month), > CONSTRAINT chk_employ_month CHECK (p_month = ANY > (ARRAY['Jan'::bpchar, 'Feb'::bpchar, 'Mar'::bpchar, 'Apr'::bpchar, > 'May'::bpchar, 'Jun'::bpchar, 'Jul'::bpchar, 'Aug'::bpchar, > 'Sep'::bpchar, 'Oct'::bpchar, 'Nov'::bpchar, 'Dec'::bpchar])) > ); > > > > How can add a CONSTRAINT on statuscode array elements to accept values > between 0 to 5 You can try: CHECK (statuscode <@ ARRAY[1,2,3,4,5]) Regards, Jeff Davis -- Sent via pgsql-docs mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-docs
