I am working under a database that already has content, so I just need to add a role column in the user table, this column should only allow 3 values: [alumno,academico,administrador]
.
When I create a table I usually only do the following:
CREATE TABLE public.users
(
id integer NOT NULL DEFAULT nextval('users_id_seq'),
username character varying(255) NOT NULL,
password character varying(255) NOT NULL,
persona_id integer NOT NULL,
estado character varying(255) NOT NULL DEFAULT 'enable'::character varying,
//la siguiente linea es la ue deseo agregar.
rol character varying(255) NOT NULL DEFAULT 'estudiante'::character varying,
CONSTRAINT users_pkey PRIMARY KEY (id),
CONSTRAINT users_persona_id_foreign FOREIGN KEY (persona_id)
REFERENCES public.personas (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT users_username_unique UNIQUE (username),
CONSTRAINT users_estado_check CHECK (estado::text = ANY (ARRAY['enable'::character varying::text, 'disable'::character varying::text])),
//aqui limito sus valores
CONSTRAINT users_rol_check CHECK (rol::text = ANY (ARRAY['estudiante'::character varying::text, 'academico'::character varying::text, 'administrador'::character varying::text]))
)
WITH (
OIDS=FALSE
);
Of course the current table does not have the role column, which is what I want to add, try it in the following way, but it does not work for me:
ALTER TABLE users ADD rol VARCHAR(20) NULL,
CONSTRAINT users_rol_check CHECK (rol::text = ANY (ARRAY['estudiante'::character varying::text, 'academico'::character varying::text, 'administrador'::character varying::text]))
Of course, the line of ALTER TABLE users ADD rol VARCHAR(20) NULL;
works perfect, so the question would be how to add the following line:
CONSTRAINT users_rol_check CHECK (rol::text = ANY (ARRAY['estudiante'::character varying::text, 'academico'::character varying::text, 'administrador'::character varying::text]))