There are six main constraints that are commonly used in SQL Server that we will describe deeply with examples within this article and the next one. These constraints are:.
Let us start discussing each one of these SQL Server constraints with a brief description and practical demo. By default, the columns are able to hold NULL values. This table contains only two columns, ID and Name. If the null-ability is not specified while defining the column, it will accept the NULL value by default:. The last insert operation will fail, as we only provide the INSERT statement with a value for the Name column, without providing value for the ID column that is mandatory and cannot be assigned NULL value, as shown in the error message below:.
Checking the inserted data, you will see that only two records are inserted and the missing value for the Name column in the second INSERT statement will be NULL, which is the default value, as shown in the result below:. You will see that the command will fail, as it will check the existing values of the Name column for NULL values first before creating the constraint, as shown in the error message below:. If you try to create the Constraints in SQL again, it will be created successfully as shown below:.
Suppose that you want to add name to the composite primary key of the users table, without creating a secondary index of the existing primary key. Instead, there is just one index for the primary key constraint. New in v Explicit index partitioning is not required. This example assumes you have a simulated multi-region database running on your local machine following the steps described in Low Latency Reads and Writes in a Multi-Region Cluster.
First, add a column and its unique constraint. We'll use email since that is something that should be unique per user. You will see that the implicit region column that was added when the table was converted to regional by row is now indexed:. The output below which is edited for length will verify that the unique index was automatically partitioned for you.
To ensure that the uniqueness constraint is enforced properly across regions when rows are inserted, or the email column of an existing row is updated, the database needs to do the following additional work when indexes are partitioned as shown above:. Note that there is a performance benefit for queries that select a single row e. If 'anemailaddress gmail. This feature, whereby the SQL engine will avoid sending requests to nodes in other regions when it can read a value from a unique column that is stored locally, is known as locality optimized search.
To illustrate the different behavior of explicitly vs. Create a multi-region database and an employees table. CockroachDB needs to do additional work to enforce global uniqueness for the id and email columns, which are implicitly partitioned.
That would cause a subsequent database dump and reload to fail. The recommended way to handle such a change is to drop the constraint using ALTER TABLE , adjust the function definition, and re-add the constraint, thereby rechecking it against all table rows. A not-null constraint simply specifies that a column must not assume the null value. A syntax example:. A not-null constraint is always written as a column constraint.
The drawback is that you cannot give explicit names to not-null constraints created this way. Of course, a column can have more than one constraint. Just write the constraints one after another:.
The order doesn't matter. It does not necessarily determine in which order the constraints are checked. This does not mean that the column must be null, which would surely be useless. Instead, this simply selects the default behavior that the column might be null. It was only added to PostgreSQL to be compatible with some other database systems. Some users, however, like it because it makes it easy to toggle the constraint in a script file.
For example, you could start with:. Unique constraints ensure that the data contained in a column, or a group of columns, is unique among all the rows in the table. To define a unique constraint for a group of columns, write it as a table constraint with the column names separated by commas:. This specifies that the combination of values in the indicated columns is unique across the whole table, though any one of the columns need not be and ordinarily isn't unique.
Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint.
A uniqueness restriction covering only some rows cannot be written as a unique constraint, but it is possible to enforce such a restriction by creating a unique partial index. In general, a unique constraint is violated if there is more than one row in the table where the values of all of the columns included in the constraint are equal. However, two null values are never considered equal in this comparison.
That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. So be careful when developing applications that are intended to be portable. A primary key constraint indicates that a column, or group of columns, can be used as a unique identifier for rows in the table. This requires that the values be both unique and not null. So, the following two table definitions accept the same data:.
Adding a primary key will automatically create a unique B-tree index on the column or group of columns listed in the primary key, and will force the column s to be marked NOT NULL. A table can have at most one primary key. There can be any number of unique and not-null constraints, which are functionally almost the same thing, but only one can be identified as the primary key.
Relational database theory dictates that every table must have a primary key. This rule is not enforced by PostgreSQL , but it is usually best to follow it. Primary keys are useful both for documentation purposes and for client applications. For example, a GUI application that allows modifying row values probably needs to know the primary key of a table to be able to identify rows uniquely. There are also various ways in which the database system makes use of a primary key if one has been declared; for example, the primary key defines the default target column s for foreign keys referencing its table.
A foreign key constraint specifies that the values in a column or a group of columns must match the values appearing in some row of another table. We say this maintains the referential integrity between two related tables.
Let's also assume you have a table storing orders of those products. We want to ensure that the orders table only contains orders of products that actually exist. So we define a foreign key constraint in the orders table that references the products table:.
We say that in this situation the orders table is the referencing table and the products table is the referenced table. Similarly, there are referencing and referenced columns.
0コメント