Limitations are the guidelines implemented on information segments on table. These are utilized to keep invalid information from being gone into the data set. This guarantees the exactness and unwavering quality of the information in the data set.
Requirements could be section level or table level. Section level requirements are applied distinctly to one segment though table level imperatives are applied to the entire table. Characterizing an information type for a segment is a limitation in itself. For instance, a segment of type DATE obliges the section to legitimate dates.
Coming up next are regularly utilized requirements accessible in PostgreSQL.
- NOT NULL Constraint − Ensures that a section can't have NULL worth.
- Interesting Constraint − Ensures that all qualities in a section are extraordinary.
- Essential Key − Uniquely distinguishes each column/record in an information base table.
- Unfamiliar Key − Constrains information dependent on segments in different tables.
- CHECK Constraint − The CHECK imperative guarantees that all qualities in a segment fulfill certain conditions.
- Rejection Constraint − The EXCLUDE requirement guarantees that if any two lines are thought about on the predefined column(s) or expression(s) utilizing the predetermined operator(s), not these examinations will bring TRUE back.
NOT NULL Constraint
Naturally, a segment can hold NULL qualities. In the event that you don't need a section to have a NULL worth, at that point you need to characterize such limitation on this segment indicating that NULL is presently not took into consideration that segment. A NOT NULL limitation is constantly composed as a segment requirement.
A NULL isn't equivalent to no information; rather, it addresses obscure information.
Example
For instance, the accompanying PostgreSQL explanation makes another table called COMPANY1 and adds five segments, three of which, ID and NAME and AGE, indicate not to acknowledge NULL qualities −
CREATE TABLE COMPANY1(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
UNIQUE Constraint
The UNIQUE Constraint keeps two records from having indistinguishable qualities in a specific segment. In the COMPANY table, for instance, you should keep at least two individuals from having indistinguishable age.
Example
For instance, the accompanying PostgreSQL proclamation makes another table called COMPANY3 and adds five segments. Here, AGE segment is set to UNIQUE, so you can't have two records with same age −
CREATE TABLE COMPANY3(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL UNIQUE,
ADDRESS CHAR(50),
SALARY REAL DEFAULT 50000.00
);
PRIMARY KEY Constraint
The PRIMARY KEY limitation extraordinarily recognizes each record in a data set table. There can be more UNIQUE segments, however just a single essential key in a table. Essential keys are significant when planning the information base tables. Essential keys are one of a kind ids.
We use them to allude to table columns. Essential keys become unfamiliar keys in different tables, while making relations among tables. Because of a 'longstanding coding oversight', essential keys can be NULL in SQLite. This isn't the situation with different information bases
An essential key is a field in a table, which interestingly recognizes each line/record in a data set table. Essential keys should contain extraordinary qualities. An essential key segment can't have NULL qualities.
A table can have just a single essential key, which may comprise of single or different fields. At the point when various fields are utilized as an essential key, they are known as a composite key.
On the off chance that a table has an essential key characterized on any field(s), at that point you can't have two records having a similar estimation of that field(s).
Example
You as of now have seen different models above where we have made COMAPNY4 table with ID as essential key −
CREATE TABLE COMPANY4(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
FOREIGN KEY Constraint
An unfamiliar key requirement determines that the qualities in a section (or a gathering of segments) should coordinate the qualities showing up in some line of another table. We say this keeps up the referential uprightness between two related tables. They are called unfamiliar keys in light of the fact that the requirements are unfamiliar; that is, outside the table. Unfamiliar keys are now and then called a referring to key.
Example
For instance, the accompanying PostgreSQL explanation makes another table called COMPANY5 and adds five sections.
CREATE TABLE COMPANY6(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);
For instance, the accompanying PostgreSQL articulation makes another table called DEPARTMENT1, which adds three sections. The segment EMP_ID is the unfamiliar key and references the ID field of the table COMPANY6.
CREATE TABLE DEPARTMENT1(
ID INT PRIMARY KEY NOT NULL,
DEPT CHAR(50) NOT NULL,
EMP_ID INT references COMPANY6(ID)
);
CHECK Constraint
The CHECK Constraint empowers a condition to check the worth being gone into a record. In the event that the condition assesses to bogus, the record abuses the limitation and isn't gone into the table.
Example
For instance, the accompanying PostgreSQL articulation makes another table called COMPANY5 and adds five segments. Here, we add a CHECK with SALARY section, so you can't have any SALARY as Zero.
CREATE TABLE COMPANY5(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL CHECK(SALARY > 0)
);
EXCLUSION Constraint
Avoidance imperatives guarantee that if any two lines are thought about on the predefined segments or articulations utilizing the predetermined administrators, at any rate one of these administrator correlations will restore bogus or invalid.
Example
For instance, the accompanying PostgreSQL proclamation makes another table called COMPANY7 and adds five segments. Here, we add an EXCLUDE imperative −
CREATE TABLE COMPANY7(
ID INT PRIMARY KEY NOT NULL,
NAME TEXT,
AGE INT ,
ADDRESS CHAR(50),
SALARY REAL,
EXCLUDE USING gist
(NAME WITH =,
AGE WITH <>)
);
Here, USING significance is the sort of record to construct and use for requirement.
You need to execute the order CREATE EXTENSION btree_gist, once per information base. This will introduce the btree_gist augmentation, which characterizes the avoidance imperatives on plain scalar information types.
As we have upheld the age must be same, let us see this by embeddings records to the table −
INSERT INTO COMPANY7 VALUES(1, 'Paul', 32, 'California', 20000.00 );
INSERT INTO COMPANY7 VALUES(2, 'Paul', 32, 'Texas', 20000.00 );
INSERT INTO COMPANY7 VALUES(3, 'Paul', 42, 'California', 20000.00 );
For the initial two INSERT explanations, the records are added to the COMPANY7 table. For the third INSERT articulation, the accompanying blunder is shown −
ERROR: conflicting key value violates exclusion constraint "company7_name_age_excl"
DETAIL: Key (name, age)=(Paul, 42) conflicts with existing key (name, age)=(Paul, 32).
Dropping Constraints
To eliminate a limitation you need to know its name. On the off chance that the name is known, it is not difficult to drop. Else, you need to discover the framework produced name. The psql order \d table name can be useful here. The overall language structure is −
ALTER TABLE table_name DROP CONSTRAINT some_name;