In the table, we can use column restrictions on how to keep the data in general, whether to enter a null value in the relevant column in the table, what to do if an empty value is entered, and in cases where we do not want the same data to be entered more than once.<\/p>\n
It allows to enter a null value in the relevant column. If no constraint is entered while creating a column, default also takes the null parameter.<\/p>\n
create table personnel( adres text NULL)\r\n<\/pre>\nNOT NULL:<\/h3>\n
It is not allowed to enter a null value in the relevant column.<\/p>\n
create table personnel( TC_No integer NOT NULL)\r\n<\/pre>\nUNIQUE:<\/h3>\n
As the name suggests, the column also helps us to keep unique values, so you cannot enter the same value twice in the column with UNIQUE added, but it should be noted here that the NULL value may repeat more than once. This is because it sees NULL values as unique.<\/p>\n
create table Location( city UNIQUE)<\/pre>\nCHECK:<\/h3>\n
While adding or updating data, it checks the data according to the value you write after the CHECK parameter.<\/p>\n
CREATE TABLE personnel(birth_date date CHECK (birth_date>'1900-01-01')<\/pre>\nPRIMARY KEY:<\/h3>\n
It is a combination of NOT NULL and UNIQUE constraints. Clustered index is created automatically in columns with primary key added.<\/p>\n
create table personnel( id serial PRIMARY KEY)\r\n<\/pre>\nREFERENCES:<\/h3>\n
Restricts the data type of a different column in a different table.<\/p>\n
CREATE TABLE public.personnel_birth_date\r\n(\r\n id integer,\r\n birth_date date,\r\n p_id integer,\r\n CONSTRAINT personnelfk FOREIGN KEY (p_id)\r\n REFERENCES public.personnel (id) --MATCH SIMPLE\r\n ON UPDATE NO ACTION\r\n ON DELETE NO ACTION\r\n NOT VALID\r\n);<\/pre>\nI recommend you to make examples yourself using the above features.<\/p>\n
EXCLUDE:<\/h3>\n
This parameter is used for comparisons between two lines.<\/p>\n
CREATE TABLE personnel(\r\n ID INT PRIMARY KEY NOT NULL,\r\n name CHAR(50),\r\n age INT ,\r\n address CHAR(50),\r\n salary REAL,\r\n EXCLUDE USING gist\r\n (name WITH =,\r\n age WITH <>)\r\n);<\/pre>\nSince we have entered the condition of being the same age in the table above, an error will return when trying to insert a different age belonging to the same name.<\/p>\n
INSERT INTO personel VALUES(1, 'faruk', 29, 'Ankara', 1000.00 );\r\n\r\nINSERT INTO personel VALUES(2, 'faruk', 29, 'Afyon', 2000.00 );\r\n\r\nINSERT INTO personel VALUES(3, 'faruk', 50, 'Konya', 200.00 );<\/pre>\nWhen we want to add the last insert, the error will return as follows.<\/p>\n
ERROR:<\/strong> ERROR: mismatched record violates \u201cpersonnel_name_age_excl\u201d exclusion restriction DETAIL: key (name, \u201cage\u201d)=(faruk , 50) does not match existing (name, \u201cage\u201d)=(faruk , 29).<\/p>\n
NOTE:<\/strong> You must install the btree_gist extension before using the exclude command.<\/p>\n
<\/div>\n