{"id":95670,"date":"2023-02-02T21:13:29","date_gmt":"2023-02-02T21:13:29","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=95670"},"modified":"2024-04-16T19:16:09","modified_gmt":"2024-04-16T19:16:09","slug":"postgresql-constraints-learning-postgresql-with-grant","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/postgresql\/postgresql-constraints-learning-postgresql-with-grant\/","title":{"rendered":"PostgreSQL Constraints: Learning PostgreSQL with Grant"},"content":{"rendered":"<p><em><strong>This is part of an ongoing series of post by Grant as he takes his vast knowledge of SQL Server and applies it to adding PostgreSQL and shares it with you so you can skip learn from his triumphs and mistakes. For more you can go to the <a href=\"https:\/\/www.red-gate.com\/simple-talk\/collections\/series-learning-postgresql-with-grant\/\">Learning PostgreSQL with Grant<\/a> series home page<\/strong><\/em><\/p>\n\n<p>PostgreSQL supports constraints much like any other database management system. When you need to ensure certain behaviors of the data, you can put these constraints to work. I\u2019ve already used several of these in creating my sample database (available articles <a href=\"https:\/\/github.com\/ScaryDBA\/LearningPostgre\">publicly on GitHub<\/a>, in the <code>CreateDatabase.sql<\/code> file). I\u2019ll explain those as we go. The constraints supported by PostgreSQL are:<\/p>\n<ul>\n<li>Not-Null Constraints<\/li>\n<li>Unique Constraints<\/li>\n<li>Primary Key<\/li>\n<li>Foreign Key<\/li>\n<li>Check Constraints<\/li>\n<li>Exclusion Constraints<\/li>\n<\/ul>\n<h2>Not-Null Constraints<\/h2>\n<p>One way we can ensure that our data is correct is by requiring it in the first place. If you create a table like this:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">create table public.example\r\n(ID int,\r\nSomeValue varchar(50));<\/pre>\n<p>Because you didn\u2019t define whether or not the columns can, or cannot, accept <code>NULL<\/code> values, then these will accept <code>NULL<\/code> values. The thing about <code>NULL<\/code> is that it\u2019s really useful. Sometimes, people simply don\u2019t have the information to give you, so allowing <code>NULL<\/code> is a good solution. Other times though, you absolutely need that value to be added to the rows of your table.<\/p>\n<p>Let\u2019s take the <code>radio.radios<\/code> table from the <code>hamshackradio<\/code> database as an example:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE TABLE IF NOT EXISTS radio.radios\r\n\t(radio_id int CONSTRAINT pkradios \r\n                  PRIMARY KEY GENERATED ALWAYS AS IDENTITY,\r\n\tradio_name varchar(100) NOT NULL,\r\n\tmanufacturer_id int NOT NULL,\r\n\tpicture BYTEA NULL,\r\n\tconnectortype_id int NOT NULL,\r\n\tdigitalmode_id int NULL\r\n);<\/pre>\n<p>Here I\u2019ve used the clause <code>NOT NULL<\/code> on multiple columns to ensure that they must have information provided. In short, a radio must have a manufacturer (through the <code>manufacturer_id<\/code> column) and a name (through the <code>radio_name<\/code> column). Defining them with <code>NOT NULL<\/code> ensures that they must be filled out. You\u2019ll also note that I use the <code>NULL<\/code> definition on the picture column. I could simply let the default take charge. For clarity, and to show intention, I use <code>NULL<\/code> instead.<\/p>\n<p>So now, if I tried to insert data into the <code>radio.radios<\/code> table like this:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">insert into radio.radios (manufacturer_id)\r\nvalues (2);<\/pre>\n<p>I\u2019m going to get a very specific error:<\/p>\n<p><code>SQL Error [23502]: ERROR: null value in column \"radio_name\" of relation \"radios\" violates not-null constraint<\/code><\/p>\n<p><code>Detail: Failing row contains (4, null, 2, null, null, null).<\/code><\/p>\n<p>It\u2019s letting me know that I must provide a value for the <code>radio_name<\/code> column. You should note that I left out another <code>NOT NULL<\/code> column, <code>connectortype_id<\/code>. It was not included in the error since no additional checks were made after the first column, <code>radio_name,<\/code> failed it\u2019s <code>NOT NULL<\/code> constraint check. If I were to just add the <code>radio_name<\/code> to the <code>INSERT<\/code> statement above, I\u2019d get a new error. In short, you have to supply all columns that are marked as <code>NOT NULL<\/code>, unless those columns have default value defined (we\u2019ll get to defaults in another article).<\/p>\n<p>If I wanted to list all the constraints on a table, I could run a query like this:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">select\r\n\tc.conname,\r\n\tccu.table_schema,\r\n\tccu.table_name,\r\n\tccu.column_name,\r\n\tc.contype,\r\n\tpg_get_constraintdef(c.oid)\r\nfrom\r\n\tpg_constraint as c\r\njoin pg_namespace as ns on\r\n\tns.oid = c.connamespace\r\njoin pg_class as cl on\r\n\tc.conrelid = cl.oid\r\nleft join information_schema.constraint_column_usage as ccu\r\n                   on\r\n\tc.conname = ccu.constraint_name\r\n\tand ns.nspname = ccu.constraint_schema\r\nwhere\r\n\tccu.table_name = 'radios';<\/pre>\n<p>Running this query results in the following results:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1036\" height=\"105\" class=\"wp-image-95671\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/01\/word-image-95670-1.png\" \/><\/p>\n<p>None of the <code>NOT NULL<\/code> constraints are listed. While these are considered constraints, they are only defined at the column level and don\u2019t have any other definition outside of that. If you want to see which columns allow <code>NULL<\/code> values, you can use the following query:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">select column_name\r\nfrom  information_schema.columns\r\nwhere table_catalog = 'hamshackradio'\r\n  and table_schema = 'radio'\r\n  and table_name = 'radios'\r\n  and is_nullable = 'YES';<\/pre>\n<p>This returns the <code>picture<\/code> and <code>digitalmode_id<\/code> columns, which, referring back to the DDL of the table, you will see matches.<\/p>\n<h2>Unique Constraints<\/h2>\n<p>Sometimes a column or set of columns in a table must be unique, that is, not allowing any duplicates. PostgreSQL provides a whole slew of mechanisms for defining a column, or columns, as unique within a table.<\/p>\n<p>First, you can simply define a unique column like this:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE TABLE IF NOT EXISTS public.uniqueval1\r\n\t(id int not null,\r\n\tmyuniquevalue varchar(50) unique);<\/pre>\n<p>Now, only unique values will be allowed for the column <code>myuniquevalue<\/code>. However, an interesting thing has happened. Let\u2019s look at the indexes on this table:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk \">select  i.indexname,\r\n\ti.indexdef\r\nfrom\r\n\tpg_indexes as i\r\nwhere\r\n\ti.tablename = 'uniqueval1';<\/pre>\n<p>Running this query results in the following:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"1600\" height=\"106\" class=\"wp-image-95672\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/01\/word-image-95670-2.png\" \/><\/p>\n<p>The way that PostgreSQL satisfies the unique criteria for the column is by creating a unique b-tree index. This is familiar to me as a SQL Server user. The question then becomes, why create a unique constraint at all? Why not just create a unique index? Well, you can truly do it either way. However, the documentation that using the unique constraint implies to the database might be important.<\/p>\n<p>My rule of thumb is that a <code>UNIQUE<\/code> constraint is there to enforce that the values in a table&#8217;s column are different from one another. A unique index is there for performance. This helps to signal to people tuning your database\u2019s queries that the index could be removed without harm, but the constraint is part of the core business rules for the database.<\/p>\n<p>The advantage to the queries that are executed is going to be the same either way. However, it will be marked and listed as a constraint as well as an index:<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"490\" height=\"268\" class=\"wp-image-95673\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2023\/01\/graphical-user-interface-text-application-descr-2.png\" alt=\"Graphical user interface, text, application\n\nDescription automatically generated\" \/><\/p>\n<p>You can also do the same thing using a slightly different syntax:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">create table if not exists public.uniqueval2\r\n\t(id int not null,\r\n\tmyuniquevalue varchar(50) not null,\r\n\tunique(myuniquevalue));<\/pre>\n<p>I have a problem with both these default approaches. It results in a default constraint name, and one in which I have no control. This affects the ability to work with your database within a source control system, so I\u2019d change the syntax to this:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">create table if not exists public.uniqueval3\r\n\t(id int not null,\r\n\tmyuniquevalue varchar(50) \r\n             constraint uniqueval3_inameit unique not null);<\/pre>\n<p>or<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">create table if not exists public.uniqueval4\r\n\t(id int not null,\r\n\tmyuniquevalue varchar(50),\r\n\tconstraint inameit unique(myuniquevalue));<\/pre>\n<p>In this manner I get to control the constraint names. If more than one column defines the constraint, you must use that second syntax with the definition as a column constraint.<\/p>\n<p>You can also add a unique constraint to a table using <code>ALTER<\/code>:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">--create a table without a constraint\r\ncreate table if not exists public.uniqueval5\r\n\t(id int not null,\r\n\tmyuniquevalue varchar(50) null);\r\n\r\n--now add the constraint\r\nalter table public.uniqueval5\r\n   add constraint mynewconstraint unique(id, myuniquevalue);<\/pre>\n<p>One point that is very important to understand here is how <code>NULL<\/code> values are handled in PostgreSQL, which may be different from other RDBMS you have worked with. A <code>NULL<\/code> value is, by definition, unknown. Since two unknown values do not equal each other, you can have, by default, multiple <code>NULL<\/code> values in your \u201cunique\u201d set of rows. Let\u2019s see this in action:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk \">create table if not exists public.nullnotunique\r\n\t(id int not null,\r\n\tnonuniqueval varchar(50) constraint notunique unique null\r\n);\r\n\r\ninsert into public.nullnotunique(id,nonuniqueval)\r\nvalues (1, null), (2,null), (3, 'one');<\/pre>\n<p>This will run without error because the <code>NULL<\/code> values are not duplicates per the definition. You can change this behavior if you are using PostgreSQL 15 or later by using unique nulls not distinct:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">create table if not exists public.nullunique\r\n\t(id int not null,\r\n\tuniqueval varchar(50)\r\n constraint nowunique unique nulls not distinct null);\r\n\r\ninsert into public.nullunique (id,uniqueval)\r\nvalues (1, null), (2, null), (3, 'one');<\/pre>\n<p>Now when this query runs, you\u2019ll immediately get an error:<\/p>\n<p><code>SQL Error [23505]: ERROR: duplicate key value violates unique constraint \"nowunique\"<\/code><\/p>\n<p><code>Detail: Key (uniqueval)=(null) already exists.<\/code><\/p>\n<p>With the table definition changed using NULLS NOT DISTINCT, you now get one <code>NULL<\/code> row, and any subsequent <code>NULL<\/code> values are recognized as duplicates. You can specify the default behavior using <code>NULLS DISTINCT<\/code>.<\/p>\n<p>For earlier versions, you might need to try other less straightforward solutions. For example, in the 14.5 version server I have for testing, I could add a computed column that takes the <code>NULL<\/code> value and makes it not NULL value.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">\tComputedUniqueeval varchar(50) GENERATED ALWAYS AS \r\n                  (case when uniqueval is null \r\n                          then 'impossible value' \r\n                   else uniqueval END) STORED\r\n);<\/pre>\n<p>Then add a filtered index on the computed column:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE UNIQUE INDEX uniqueval_notnull_idx \r\n       ON public.nullunique (ComputedUniqueeval)\r\n       WHERE uniqueval IS NULL;<\/pre>\n<p>Of course, this is wasted space for the duplicated column and the extra index, plus the need to find an impossible value for <code>uniqueval<\/code> (and probably add a constraint to make sure that it is truly impossible.) However, almost always, implementation cost and complexity are going to be less important than data quality.<\/p>\n<h2>Primary Key Constraints<\/h2>\n<p>A primary key constraint is effectively the same as a unique constraint, with a few minor, but important differences. It\u2019s going to be enforced by a unique index in the same manner and can be references by a foreign key constraint (which will be covered later in this article.) However, the primary key constraint is typically the columns used in a foreign key relationship, and the columns of a primary key may not allow <code>NULL<\/code> values. (Even if you define a primary key column as <code>NULL<\/code> it will be changed to <code>NOT NULL<\/code> as long as no data already exists.)<\/p>\n<p>While you could dispense with the need for a primary key and simply define your key as a unique constraint or index, clarity is so important when it comes to code. Defining your structures appropriately to properly communicate how your database is designed and how it should be used. More about this in the foreign key section.<\/p>\n<p>The syntax to create a primary key is very similar to creating a unique constraint:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">create table if not exists public.pkexample\r\n(id int primary key not null,\r\nsomevalue varchar(50));<\/pre>\n<p>As before though, this will result in a default name being created, so it\u2019s better to do something like this:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE TABLE IF NOT EXISTS radio.radios\r\n\t(radio_id int CONSTRAINT pkradios PRIMARY KEY \r\n                               GENERATED ALWAYS AS IDENTITY,\r\n\tradio_name varchar(100) NOT NULL,\r\n\tmanufacturer_id int NOT NULL,\r\n\tpicture BYTEA NULL,\r\n\tconnectortype_id int NOT NULL,\r\n\tdigitalmode_id int NULL);<\/pre>\n<p>This will create a primary key named <code>pkradios<\/code>. You\u2019ll also note I used an <code>IDENTITY<\/code> value here to ensure that this is a self-generating value as data gets added to the table.<\/p>\n<p>As with the unique constraint examples above, you can define the primary key as a column definition. You must use that syntax if you have more than one column. You can also <code>ALTER<\/code> a table to add a primary key constraint. Other than that, the behavior of a primary key is basically identical to the unique constraint. I would still always use the primary key definition, if for no other reason, as a means of documentation.<\/p>\n<p>One very interesting thing about creating primary key constraints is that if the values you are defining as the <code>PRIMARY KEY<\/code> allow <code>NULL<\/code> values, the PostgreSQL engine will attempt to fix that for you. For example, consider the following table:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">create table if not exists public.badpkexample\r\n(id int null,\r\nsomevalue varchar(50));<\/pre>\n<p>Check the results of the following metadata query:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">select is_nullable\r\nfrom   information_schema.columns\r\nwhere table_schema = 'public'\r\nand   table_name = 'badpkexample'\r\nand   column_name = 'id';<\/pre>\n<p>This will return <code>YES<\/code>. Meaning the column allows <code>NULL<\/code> values. After you add the following constraint:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">alter table public.badpkexample\r\n  add constraint PKbadpkexample\r\n     primary key (id);<\/pre>\n<p>Query the metadata again and it will return NO. It has changed the column to no longer allow <code>NULL<\/code> values. If there was already data in the id column that contained <code>NULL<\/code> values, you would receive the following error message:<\/p>\n<p><code>ERROR:  column \"id\" of relation \"badpkexample\" contains null values<\/code><\/p>\n<p><code>SQL state: 23502<\/code><\/p>\n<h2>Foreign Key Constraints<\/h2>\n<p>One of the key concepts behind a relational database is to relate one table to another in order to create meaning. Foreign keys define and control the referential integrity from one table to another. In my sample database, I have a table for radios and one for radio manufacturers. The foreign key definition between the two looks like this:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">ALTER TABLE radio.radios\r\nADD foreign key (manufacturer_id) \r\n            references radio.manufacturers;<\/pre>\n<p>The primary key in <code>radio.manufacturers<\/code> consists of the <code>manufacturer<\/code>_id column. I have that same column with a matching data type in <code>radio.radios<\/code>. That allows me to create the foreign key between the two tables. You\u2019ll note that while both tables are involved, we\u2019re only altering the one table to add the constraint.<\/p>\n<p>With the foreign key in place, I can only add <code>manufacturer_id<\/code> values to the <code>radio.radios<\/code> table where the <code>radios.manufacturers<\/code> contains that value in the primary key. Further, because of the constraint in place, I can\u2019t delete the row with that value in the <code>radio.manufacturers<\/code> table (more on that below). This is how the foreign key constraint helps to ensure data integrity.<\/p>\n<p>The way I defined the constraint in the text, it\u2019s going to generate a default name. I can control the name by modifying the code.<\/p>\n<pre class=\"\">alter table radio.radios drop constraint radios_manufacturer_id_fkey;\r\n\r\nalter table radio.radios \r\n   add constraint radios_fk_manufacturer \r\n       foreign key (manufacturer_id) \r\n           references radio.manufacturers;<\/pre>\n<p>I can also add a foreign key as part of the table definition:<\/p>\n<pre class=\"\"><code>create table if not exists radio.radios\r\n   (radio_id int constraint pkradios primary key\r\n                         generated always as identity,\r\n\tradio_name varchar(100) not null,\r\n\tmanufacturer_id int not null,\r\n\tpicture BYTEA null,\r\n\tconnectortype_id int not null,\r\n\tdigitalmode_id int null,\r\n\tconstraint radios_fk_manufacturer3\r\n       foreign key (manufacturer_id)\r\n           references radio.manufacturers \r\n);<\/code><\/pre>\n<p>The default behavior is for the foreign key to be checked when a row is deleted in the referenced table. If values exist, the delete is stopped. You can take direct control over this behavior and change it. For example, you can change when inside transaction process the check is made. Normally it\u2019s made later in the process, but you can set it to happen first by using the <code>ON DELETE RESTRICT<\/code> option:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">create table if not exists radio.radios\r\n(\r\n   radio_id int constraint pkradios primary key \r\n                             generated always as identity,\r\n   radio_name varchar(100) not null,\r\n   manufacturer_id int not null,\r\n   picture BYTEA null,\r\n   connectortype_id int not null,\r\n   digitalmode_id int null,\r\n   constraint radios_fk_manufacturer3 foreign key \r\n                                     (manufacturer_id) \r\n   references radio.manufacturers ON DELETE RESTRICT\r\n);<\/pre>\n<p>That changes the default behavior. You can also command the default behavior by using the syntax <code>ON DELETE NO ACTION<\/code>, but I think that\u2019s both wordy and unclear. Better to just use the default syntax. Let me take a moment to walk you through this diagram outlining this <code>DELETE<\/code> behavior:<\/p>\n<p>So the default behavior is to find the rows to be deleted, delete them, then validate that there are no matching rows in any related tables. This is how SQL Server works as well. However, you can change where that check occurs by issuing the <code>DELETE RESTRICT<\/code> to the definition. In this case, before the transaction processing starts, you get a check to validate that no foreign key violation will occur.<\/p>\n<p>All this may feel like semantics, but, there is an additional behavior that you can add to your transactions within PostgreSQL. There are quite few details to this behavior, but I\u2019ll summarize it here. You can directly affect when some constraints, specifically <code>UNIQUE<\/code>, <code>PRIMARY KEY<\/code>, foreign key (<code>REFERENCES<\/code>) and <code>EXCLUDE<\/code>, are evaluated in an individual transaction. The default for these constraints is as outlined in each section. But you can defer all of them until later in the transaction. However, some foreign key constraints may be more likely to be violated. Those you can control through this setting, forcing them to be evaluated earlier in a transaction, saving unnecessary processing and rollbacks. Conversely, you can make all constraint checks immediate. By the way, <code>NULL<\/code> and <code>CHECK<\/code> constraints are always immediate.<\/p>\n<p>We can also make it so that when a row in the radio.manufacturers table gets deleted, all associated radios also get deleted:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">create table if not exists radio.radios\r\n\t(radio_id int constraint pkradios primary key \r\n                                generated always as identity,\r\n\tradio_name varchar(100) not null,\r\n\tmanufacturer_id int not null,\r\n\tpicture BYTEA null,\r\n\tconnectortype_id int not null,\r\n\tdigitalmode_id int null,\r\n\tconstraint radios_fk_manufacturer foreign key \r\n            (manufacturer_id) \r\n      references radio.manufacturers on delete cascade \r\n\t);<\/pre>\n<p>This is what <code>CASCADE<\/code> will do. However, there are implications here that are important. The radio.radios table is also referenced as a foreign key. If we start trying to delete rows from the radios table, it will fail because of those other foreign key constraints. Unless we also make all them <code>CASCADE<\/code> as well. However, at that point, you can get some pretty drastic behavioral issues within your database as locks are held while all the various tables go through their cascading deletes. Generally, this isn\u2019t considered a good way to set things up.<\/p>\n<p>For the most part, databases I have created have all used identifier values that cannot be changed, so I have rarely used the following settings, but PostgreSQL has some additional options for O<code>N DELETE<\/code> that could be helpful in some cases.<\/p>\n<p>You can define <code>ON DELETE SET NULL<\/code>. This will change the column from whatever value it had, to a <code>NULL<\/code> value. Of course, the column must allow <code>NULL<\/code> values. You can also define <code>ON DELETE SET DEFAULT<\/code>. Then, whatever the default value of the column is (we have yet to cover defaults), it will be substituted for the deleted value. Once again though, that default value must meet fundamental referential integrity requirements, meaning, it has to be a value in the primary key of the <code>radio.manufacturers<\/code> table in our example.<\/p>\n<p>Note: You can define a foreign key constraint that references a <code>UNIQUE<\/code> constraint, but there is one major caveat. <code>NULL<\/code> values in a column will guarantee that a match is made. As I will demonstrate in the <strong>Check Constraints<\/strong> section, constraints fail on a false condition, not a <code>NULL<\/code> (which any <code>NULL<\/code> value compared to another will cause)<\/p>\n<h2>Check Constraints<\/h2>\n<p>The check constraint is used to allow you to define your own data integrity rules for values in a row. It could be anything from the values in a column can only contain numbers greater than 100 to calculations between columns. The key is that all checks are made upon the insertion of a row, or on any subsequent updates, and can only access columns and values in the same row.<\/p>\n<p>To take an example, in the United States the lowest possible frequency I can use as an amateur radio operator is 135.7kHz and the highest is 1300MHz or 1300000kHz. So, in my radio.bands table where I\u2019m tracking frequencies, I could define a constraint for the frequency_start_khz column like this:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">alter table radio.bands \r\nadd constraint minfrequency \r\n    check (frequency_start_khz &gt;= 135.7);<\/pre>\n<p>Now I can\u2019t accidentally add a value that is actually below my possible operating range.<\/p>\n<p>To see an example of columns interacting, I should make sure that the frequency_start_khz is always below the frequency_end_khz that define the range of the band:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">ALTER TABLE radio.bands\r\nADD CONSTRAINT startlessthanend \r\n     CHECK (frequency_start_khz &lt; frequency_end_khz);<\/pre>\n<p>And I wanted to, I could make these into a single check:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">alter table radio.bands \r\nadd constraint allinone \r\n     check (frequency_start_khz &lt; frequency_end_khz \r\n            and frequency_start_khz &gt; 135.7);<\/pre>\n<p>It\u2019s really a question of deciding how you want to use your checks. I\u2019d probably keep them separate because it makes for easier understanding of what each constraint is doing.<\/p>\n<p>As with the other constraint examples throughout the chapter, you can also make the constraint a part of the table definition:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk \">CREATE TABLE IF NOT EXISTS radio.bands\r\n(\r\n band_id int CONSTRAINT pkbands PRIMARY KEY \r\n                         GENERATED ALWAYS AS IDENTITY,\r\n band_name varchar(100) NOT NULL,\r\n frequency_start_khz numeric(9,2) NOT null \r\n     CONSTRAINT minfrequency check (frequency_start_khz &gt;135.7),\r\n frequency_end_khz numeric(9,2) NOT NULL,\r\n country_id int NOT NULL\r\n);<\/pre>\n<p>As before, if I didn\u2019t include a constraint name, one will be generated for me.<\/p>\n<p>One thing to note about <code>NULL<\/code> values and constraints. Constraints operate slightly different from <code>WHERE<\/code> clauses. In a <code>WHERE<\/code> clause, a row is returned when a <code>TRUE<\/code> output comes. <code>1=1<\/code> returns <code>TRUE<\/code>, <code>1=2<\/code> returns <code>FALSE<\/code>, but <code>1=NULL<\/code> is <code>UNKNOWN<\/code>. Only the <code>TRUE<\/code> value will return results. In constraints, the data fails the constraint only on a <code>FALSE<\/code> comparison.<\/p>\n<p>For example, consider the following object.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">create table public.nullconstraintcheck\r\n(\r\n      id    int not null,\r\n      value int NULL\r\n); <\/pre>\n<p>Then you add a <code>CHECK<\/code> constraint requiring the column value to be <code>1<\/code>.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">alter table public.nullconstraintcheck\r\n  add constraint valueEquals1 \r\n     check (value = 1);<\/pre>\n<p>It seems pretty clear which of these two following <code>INSERT<\/code> statement queries will work and which will fail.:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">insert into public.nullconstraintcheck(id, value)\r\nvalues (1,1); \r\ninsert into public.nullconstraintcheck(id, value)\r\nvalues (2,2);<\/pre>\n<p>The second will fail with the following error message:<\/p>\n<p><code>ERROR:  new row for relation \"nullconstraintcheck\" violates check constraint \"valueequals1\"<\/code><\/p>\n<p><code>DETAIL:  Failing row contains (2,2).<\/code><\/p>\n<p>Note that if you run them both in the same batch, no rows will be added to the table. The following statement, however, will succeed because <code>NULL = 1<\/code> is <code>UNKNOWN<\/code>, not <code>FALSE<\/code>.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">insert into public.nullconstraintcheck(id, value)\r\nvalues (3,NULL);<\/pre>\n<p>If you need a constraint to consider NULL values in the criteria, you need to explicitly include an <code>IS NULL<\/code> expression or something similar. If you wanted to eliminate <code>NULL<\/code> values in some conditions, you need to explicitly state <code>IS NOT NULL<\/code>. For example, I will change the constraint to allow <code>NULL<\/code> values when the value of <code>id<\/code> is less than <code>5<\/code>, but not allow <code>NULL<\/code> values <code>5<\/code> and up.<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">alter table public.nullconstraintcheck\r\n     drop constraint valueEquals1;\r\nalter table public.nullconstraintcheck\r\n  add constraint valueEquals1 \r\n      check ((value is not null and value = 1)\r\n             OR (id &lt; 5 and value = 1));<\/pre>\n<p>Now you can see when you execute the following statements:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">insert into public.nullconstraintcheck(id,value)\r\nvalues (4,NULL);\r\ninsert into public.nullconstraintcheck(id,value)\r\nvalues (5,NULL);<\/pre>\n<p>The first will succeed, but the second one will not.<\/p>\n<p><code>ERROR:  new row for relation \"nullconstraintcheck\" violates check constraint \"valueequals1\"<\/code><\/p>\n<p><code>DETAIL:  Failing row contains (5, null).<\/code><\/p>\n<p>I very much suggest that you test constraints thoroughly, especially when any referenced column allows <code>NULL<\/code> values.<\/p>\n<h2>Exclusion Constraints<\/h2>\n<p>An exclusion constraint is a bit like a unique constraint, but it\u2019s different. Basically an exclusion constraint guarantees that if any two rows are compared, using the expressions defined in the constraint, that not all comparisons will return <code>TRUE<\/code>, at least one has to return <code>FALSE<\/code> or <code>NULL<\/code>. The idea is to get to a place where you can define more complex constraints across multiple columns in a table.<\/p>\n<p>Let\u2019s note right up front, if the columns you\u2019re comparing are using only equality operators, then the exclusion constraint is exactly the same as a unique constraint. So, the idea here is that when you have a more complex comparison than straight equality, but you still want unique data based on the comparison, whatever it may be, you\u2019ll be using an exclusion constraint.<\/p>\n<p>Before you try to create exclusion constraints, be aware that there limits on the data types that can be used. If you\u2019re interested in dates, text, stuff like that as things that may limit a row, which is very standard, you need to run one command first:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE EXTENSION btree_gist;<\/pre>\n<p>This allows you to put constraints on standard scalar data types as opposed to arrays, geometry, and other more complex types. In this series of articles, I have tried to stick to what is strictly \u201cin the box\u201d in terms of PostgreSQL functionality, meaning, no extensions, and this does violate that goal. See, there are simply tons of extensions. Some, like <code>btree_gist<\/code>, are free and fairly fundamental to some behaviors. Others cost money, but add amazing behaviors. I\u2019m currently trying to learn the core of PostgreSQL, not all possible extensions. However, constraints on standard data types is very likely, so I\u2019ve gone ahead and broke my own rule in this instance.<\/p>\n<p>With that in place, if we take the logging.logs table:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">CREATE TABLE IF NOT EXISTS logging.logs\r\n(log_id int CONSTRAINT pklogs PRIMARY KEY \r\n                          GENERATED ALWAYS AS IDENTITY,\r\nlog_date timestamptz  NOT NULL,\r\nlog_callsign text,\r\nlog_location point NOT NULL);<\/pre>\n<p>I\u2019m going to define a constraint where the log_date, log_callsign can be the same, as long as the log_location changes. This would be useful for a contest like Parks on the Air where you can talk to the same people, but your location has to change, meaning, you\u2019re in a different park. That constraint would be defined like this:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">alter table logging.logs\r\nadd constraint uniquecontact exclude\r\nusing gist (log_date with =,\r\n\tlog_callsign  with =,\r\nlog_location with ~=);<\/pre>\n<p>I can then add some data to the table:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk\">INSERT INTO logging.logs\r\n(log_date,log_callsign,log_location)\r\nVALUES\r\n('12\/21\/2022','KC1KCE','35.952, -96.152'),\r\n('12\/21\/2022','KC1KCE','35.957, -96.127');<\/pre>\n<p>And that\u2019s going to work fine. Even though the log_date and the log_callsign are the same, the log_location is different. If I then try to add this:<\/p>\n<pre class=\"lang:tsql theme:ssms2012-simple-talk \">INSERT INTO logging.logs\r\n(log_date,log_callsign,log_location)\r\nVALUES\r\n('12\/21\/2022','KC1KCE','35.952, -96.152');<\/pre>\n<p>I will get the following error:<\/p>\n<p><code>SQL Error [23P01]: ERROR: conflicting key value violates exclusion constraint \"uniquecontact\"<\/code><\/p>\n<p><code>Detail: Key (log_date, log_callsign, log_location)=(2022-12-21 00:00:00-06, KC1KCE, (35.952,-96.152)) conflicts with existing key (log_date, log_callsign, log_location)=(2022-12-21 00:00:00-06, KC1KCE, (35.952,-96.152)).<\/code><\/p>\n<p>While you can get into some complex stuff with this constraint, it\u2019s worth noting that the exclusion constraint isn\u2019t the greatest performing constraint. Where possible, it\u2019s recommended to use unique constraints if you can since they will perform better. However, when you have specific requirements, the exclusion constraint can make things better.<\/p>\n<h2>Conclusion<\/h2>\n<p>Those are all the constraint types within PostgreSQL. You should absolutely take advantage of these to ensure that your data is as clean as possible. Functionally, if you\u2019re used to another RDBMS like SQL Server, most of these constraints are going to be very familiar, as are the behaviors of the constraints. A few things like the ability to control how <code>NULL<\/code> values are resolved in a unique constraint, or the very concept of exclusion constraints will be new. Overall though, I\u2019m impressed with the functionality that is possible with the constraints in PostgreSQL.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>One of the most important aspects of data management is the ability to ensure that the data in your database is well defined and consistent. Some aspects of that are ensured through the relational data structures you design. Another piece of control is using the correct data type. Then, we get to constraints. A constraint is a way to validate data prior to adding it to your database. This is one more tool in the toolbox that helps you maintain good data.&hellip;<\/p>\n","protected":false},"author":221792,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[53,143534],"tags":[158977,158976,159066],"coauthors":[6785],"class_list":["post-95670","post","type-post","status-publish","format-standard","hentry","category-featured","category-postgresql","tag-learningpostgresqlwithgrant","tag-planetpostgresqlgrantfritchey","tag-postgresql-101-webinar-sidebar"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95670","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/221792"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=95670"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95670\/revisions"}],"predecessor-version":[{"id":102161,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/95670\/revisions\/102161"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=95670"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=95670"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=95670"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=95670"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}