How can i constrain an attribute in a table to only allow the value to be between 1-10? This is the statement so far.. Have no idea how to make the OfficeNumber only accept values in that interval
CREATE TABLE OfficeStaff(
EID INT PRIMARY KEY,
OfficeNumber INT NOT NULL
);
Use a check constraint:
CREATE TABLE OfficeStaff (
EID INT PRIMARY KEY,
OfficeNumber INT NOT NULL,
CHECK (OfficeNumber BETWEEN 1 AND 10)
);
Note, though, that there is another, perhaps better approach. You should have an OfficeNumbers table with the valid office numbers. Then you can use a foreign key relationship to enforce the numbering, without having to hard-code the numbers.
You can use domains for this purpose:
create domain mydomain as integer check(value between 1 and 10)
create table mytable(id serial primary key, md mydomain not null)
-- this two will succeed
insert into mytable(md) values(1)
insert into mytable(md) values(2)
-- that one will fail
insert into mytable(md) values(12)
ERROR: value for domain mydomain violates check constraint "mydomain_check"
********** Error **********
ERROR: value for domain mydomain violates check constraint "mydomain_check"
More information can be found here: http://www.postgresql.org/docs/9.1/static/sql-createdomain.html
I added a check similar to:
CHECK (OfficeNumber BETWEEN 1 AND 10)
When I exported my schema, PostgresSQL (I am using v. 12.20) converted my check to:
CONSTRAINT OfficeStaff_OfficeNumber_check CHECK (((OfficeNumber >= 1) AND (OfficeNumber <= 10)))
You can add constraints for the definition. In this case you can add
CHECK (OfficeNumber BETWEEN 1 AND 10)
