Limitation UNIQUE V SQL allows you to identify each record in the table.
If a column constraint fits UNIQUE in a field when creating a table, the database will reject any attempt to enter into that field for one of the rows a value that is already present in another row. This restriction can only apply to fields that have been declared non-empty ( NOT NULL), since it doesn't make sense to let one table row have a value NULL and then exclude other lines with NULL values as duplicates.
SQL Server/Oracle/Access
Table creation example SQL with limitation UNIQUE:
CREATE TABLE Student
(Kod_stud integer NOT NULL UNIQUE,
Fam char (30) NOT NULL UNIQUE,
Addresses char (50),
When the Fam field is declared unique, two Smirnov Marys can be entered different ways- for example, Smirnova Maria and Smirnova M. Columns (not primary keys) whose values require uniqueness are called candidate keys or unique keys. You can define a group of fields as unique using the table constraint command - UNIQUE. Declaring a group of fields unique is different from declaring individual fields unique, because it is a combination of values, not just an individual value, which must be unique. The uniqueness of the group lies in the fact that pairs of rows with column values “a”, “b” and “b”, “a” were considered separately from one another.
If the database determines that each specialty belongs to one and only one department, then each combination of department code (Kod_f) and specialty code (Kod_spec) in the Spec table must be unique. For example:
CREATE TABLE Spec
(Kod_spec integer NOT NULL,
Kod_f integer NOT NULL,
Nazv_spec char (50) NOT NULL,
UNIQUE(Kod_spec, Kod_f));
Both fields in the UNIQUE table constraint still use the column constraint - NOT NULL. If used limitation column UNIQUE for the Kod_spec field, this limitation tables would be optional. If the Kod_spec field value is different for each row, then there cannot be two rows with an identical combination of Kod_spec and Kod_f field values. Limitation tables UNIQUE most useful when individual fields do not need to be unique.
MySQL UNIQUE
Example of creating a Persons table in MySQL with limitation UNIQUE:
CREATE TABLE Persons (
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE(P_Id)
);
Remove UNIQUE constraint
If after creation restrictions UNIQUE and in the case when UNIQUE constraint doesn't make sense UNIQUE can be deleted. To do this use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE table_name DROP CONSTRAINT uc_PersonID;
ALTER TABLE table_name DROP INDEX uc_PersonID;
A UNIQUE constraint uniquely identifies each record in a database table.
A unique and PRIMARY KEY constraint column or set of columns provides a unique guarantee.
A PRIMARY KEY constraint automatically has a uniqueness constraint defined.
Note that each table can have multiple unique constraints, but each table can only have one primary key constraint.
CREATE TABLE SQL UNIQUE constraint when
The following SQL, when the "Persons" table was created, to create a unique constraint on the "p_id" column:
CREATE TABLE Persons
P_Id int NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
To name unique constraints UNIQUE constraints and define multiple columns, use the following SQL syntax:
CREATE TABLE Persons
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)
SQL UNIQUE constraint ALTER TABLE when
When the table has been created, create a unique constraint on the "p_id" column, please use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD UNIQUE (P_Id)
To name unique constraints UNIQUE constraints and define multiple columns, use the following SQL syntax.
Last update: 07/09/2017
When creating columns in T-SQL, we can use a number of attributes, some of which are constraints. Let's look at these attributes.
PRIMARY KEY
You can use the PRIMARY KEY expression to make a column a primary key.
CREATE TABLE Customers (Id INT PRIMARY KEY, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20))
A primary key uniquely identifies a row in a table. Columns of type int do not have to be the primary key; they can be any other type.
Setting a primary key at the table level:
CREATE TABLE Customers (Id INT, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20), PRIMARY KEY(Id))
The primary key can be a compound key. Such a key may be required if we have two columns at once that must uniquely identify a row in the table. For example:
CREATE TABLE OrderLines (OrderId INT, ProductId INT, Quantity INT, Price MONEY, PRIMARY KEY(OrderId, ProductId))
Here, the OrderId and ProductId fields together act as a composite primary key. That is, there cannot be two rows in the OrderLines table where both of these fields simultaneously have the same values.
IDENTITY
The IDENTITY attribute allows you to make a column an identifier. This attribute can be assigned to columns of numeric types INT, SMALLINT, BIGINT, TYNIINT, DECIMAL, and NUMERIC. When adding new data to SQL table Server will increment the value of this column by one last entry. As a rule, the identifier is the same column that is the primary key, although in principle this is not necessary.
CREATE TABLE Customers (Id INT PRIMARY KEY IDENTITY, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20))
You can also use full form attribute:
IDENTITY(seed, increment)
Here the seed parameter indicates the initial value from which the countdown will begin. And the increment parameter determines how much the next value will be increased. By default, the attribute uses the following values:
IDENTITY(1, 1)
That is, the countdown starts from 1. And subsequent values increase by one. But we can override this behavior. For example:
Id INT IDENTITY (2, 3)
In this case, the countdown will start from 2, and the value of each subsequent entry will increase by 3. That is, the first line will have the value 2, the second - 5, the third - 8, etc.
Also note that only one column in a table should have this attribute.
UNIQUE
If we want a column to have only unique values, then we can define a UNIQUE attribute for it.
CREATE TABLE Customers (Id INT PRIMARY KEY IDENTITY, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE)
In this case, the columns that represent email address and telephone will have unique values. And we will not be able to add two rows to the table whose values for these columns will be the same.
We can also define this attribute at the table level:
CREATE TABLE Customers (Id INT PRIMARY KEY IDENTITY, Age INT, FirstName NVARCHAR(20), LastName NVARCHAR(20), Email VARCHAR(30), Phone VARCHAR(20), UNIQUE(Email, Phone))
NULL and NOT NULL
To indicate whether a column can be nullable, you can give it the NULL or NOT NULL attribute when defining the column. Unless this attribute is explicitly used, the column will default to NULL. The exception is when the column acts as a primary key, in which case the default value of the column is NOT NULL.
CREATE TABLE Customers (Id INT PRIMARY KEY IDENTITY, Age INT, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE)
DEFAULT
The DEFAULT attribute specifies the default value for the column. If no value is provided for a column when you add data, the default value will be used for it.
CREATE TABLE Customers (Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE);
Here the Age column has a default value of 18.
CHECK
The CHECK keyword sets a limit on the range of values that can be stored in a column. To do this, after the word CHECK, a condition is indicated in parentheses that the column or several columns must meet. For example, the age of clients cannot be less than 0 or greater than 100:
CREATE TABLE Customers (Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18 CHECK(Age >0 AND Age< 100), FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE CHECK(Email !=""), Phone VARCHAR(20) UNIQUE CHECK(Phone !=""));
It also specifies that the Email and Phone columns cannot have an empty string as a value (an empty string is not equivalent to a NULL value).
The AND keyword is used to connect conditions. Conditions can be specified in the form of comparison operations greater than (>), less than (<), не равно (!=).
You can also use CHECK to create a constraint for a table as a whole:
CREATE TABLE Customers (Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE, CHECK((Age > 0 AND Age<100) AND (Email !="") AND (Phone !="")))
CONSTRAINT operator. Set the name of the restrictions.
By using keyword CONSTRAINT allows you to specify a name for the constraints. PRIMARY KEY, UNIQUE, DEFAULT, CHECK can be used as restrictions.
Constraint names can be specified at the column level. They are specified after CONSTRAINT before the attributes:
CREATE TABLE Customers (Id INT CONSTRAINT PK_Customer_Id PRIMARY KEY IDENTITY, Age INT CONSTRAINT DF_Customer_Age DEFAULT 18 CONSTRAINT CK_Customer_Age CHECK(Age >0 AND Age< 100), FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) CONSTRAINT UQ_Customer_Email UNIQUE, Phone VARCHAR(20) CONSTRAINT UQ_Customer_Phone UNIQUE)
Restrictions can have arbitrary names, but, as a rule, the following prefixes are used:
"PK_" - for PRIMARY KEY
"FK_" - for FOREIGN KEY
"CK_" - for CHECK
"UQ_" - for UNIQUE
"DF_" - for DEFAULT
In principle, it is not necessary to specify constraint names; when you set the appropriate attributes, SQL Server automatically determines their names. But, knowing the name of the constraint, we can access it, for example, to delete it.
And you can also set all constraint names through table attributes:
CREATE TABLE Customers (Id INT IDENTITY, Age INT CONSTRAINT DF_Customer_Age DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30), Phone VARCHAR(20), CONSTRAINT PK_Customer_Id PRIMARY KEY (Id) , CONSTRAINT CK_Customer_Age CHECK(Age >0 AND Age< 100), CONSTRAINT UQ_Customer_Email UNIQUE (Email), CONSTRAINT UQ_Customer_Phone UNIQUE (Phone))
IN CHAPTER 17, you learned how tables are created. Now in more detail from this point we will show you how you can set restrictions in tables. Constraints are part of a table definition that limits the values you can enter into columns. Up to this point in the book, the only restrictions on the values you could enter were that the data type and size of the values you entered must be compatible with the columns in which the values would be placed (as specified in the CREATE TABLE command or ALTER TABLE command). Limitations give you much greater opportunities and you will soon see this. You will also learn how to define default values in this chapter. The default is a value that is automatically inserted into any table column when a value for that column is not present in the INSERT command for that table. NULL is the most widely used default value, but this chapter will show you how to define other default values.
TABLE LIMITATIONS
When you create a table (or when you modify it), you can place a limit on the values that can be entered into the field. If you do this, SQL will reject any values that violate the criteria you have defined. There are two main types of constraints - a column constraint and a table constraint. The difference between the two is that a column constraint applies only to individual columns, while a table constraint applies to groups of one or more columns.ANNOUNCEMENT OF RESTRICTIONS
You insert a column constraint at the end of the column name, after the data type and before the comma. A table constraint is placed at the end of the table name, after the last column name but before the final parenthesis. The following is the syntax for the CREATE TABLE command, extended to include a constraint: CREATE TABLE< table name > (< column name > < column constraint >, < column name > < data type > < column constraint > ... < table constraint > (< column name > [, < column name >])...); (For brevity, we have omitted the size argument, which is sometimes used with the data type.) The gender of the data in parentheses after the table constraint is the gender to which that constraint is applied. A column constraint naturally applies to the columns whose names it follows. The rest of this chapter will describe Various types restrictions and their use.USING CONSTRAINTS TO EXCLUDE NULL POINTERS
You can use the CREATE TABLE command to prevent a field from allowing NULL pointers in it using the NOT NULL constraint. This limitation applies only to diverse columns.You may recall that NULL is a special designation that marks a field as empty. NULL can be useful when there are cases where you want to be guaranteed against them. Obviously, primary keys should never be empty, as this will undermine their functionality. In addition, genders such as names are required in most cases, certain values. For example, you might want to have a name for each customer in the Customers table. If you place the NOT NULL keywords immediately after the data type (including size) of a column, any attempt to place a NULL value in that field will be rejected. Otherwise, SQL understands that NULL is allowed.
For example, let's improve our Salespeople table definition by not allowing NULL values to be placed in the snum or sname columns: CREATE TABLE Salespeople(Snum integer NOT, Sname char(10) NOT, city char(10), comm decimal); It is important to remember that any column with a NOT NULL constraint must be set to a value in every INSERT clause that affects the table. In the absence of NULL, SQL may have no values to set to these columns, unless the default value described earlier in this chapter has already been assigned.
If your system supports using ALTER TABLE to add new columns to an existing table, you can probably place a column constraint, such as NOT NULL, on these new columns. However, if you specify a NOT NULL value for the new column, the current table must be empty.
MAKE SURE THE VALUES ARE UNIQUE
In Chapter 17, we discussed using unique indexes to force gender to have a different value for each row. This practice is left over from the old days when SQL supported the UNIQUE constraint. Uniqueness is a property of the data in a table, and so it is more logical to name it as a constraint on that data, rather than simply as a logical difference property that binds a data object (index).Undoubtedly, unique indexes are one of the simplest and most effective methods of enforcing uniqueness. For this reason, some implementations of the UNIQUE constraint use unique indexes; that is, they create the index without telling you about it. The fact remains that the likelihood of database confusion is quite small if you enforce uniqueness along with a constraint.
UNIQUENESS AS A COLUMN CONSTRAINT
From time to time, you want to make sure that all the values entered into a column are different from each other. For example, primary keys show this quite clearly. If you place a UNIQUE column constraint on a field when you create a table, the database will reject any attempt to enter into that field for one of the rows a value that is already present in another row. This restriction can only apply to fields that have been declared as NOT NULL, since it makes no sense to allow one row of a table to have a NULL value and then exclude other rows with NULL values as duplicates. There is a further improvement to our Salespeople table creation command: CREATE TABLE Salespeople (Snum integer NOT NULL UNIQUE, Sname char (10) NOT NULL UNIQUE, city char (10), comm decimal); When you declare the sname field to be unique, make sure that the two Mary Smiths are entered in different ways - for example, Mary Smith and M. Smith. At the same time, this is not so necessary from a functional point of view - because the snum field as a primary key will still ensure that the two rows are different - which is easier for people using data in tables than remembering that these Smiths are not identical. Columns (not primary keys) whose values require uniqueness are called candidate keys or unique keys.UNIQUENESS AS A TABLE CONSTRAINT
You can also define a group of fields as unique using the table constraint command - UNIQUE. Declaring a group of fields unique is different from declaring individual fields unique, since it is a combination of values, and not just an individual value that must be unique. Group uniqueness is a representation of order, such that pairs of rows with column values "a", "b" and "b", "a" are considered separately from each other. Our database is designed so that each customer is assigned to one and only one seller. This means that each combination of customer number (cnum) and seller number (snum) in the Customer table must be unique. You can verify this by creating a table of Customers in this way: CREATE TABLE Customers (cnum integer NOT NULL, cname char (10) NOT NULL, city char (10), rating integer, snum integer NOT NULL, UNIQUE (cnum, snum )); Note that both genders in the UNIQUE table constraint still use the NOT NULL column constraint. If we were to use a UNIQUE column constraint on the cnum field, such a table constraint would not be necessary. If the cnum field values are different for each row, then there cannot be two rows with an identical combination of cnum and snum field values. The same thing happens if we declare the snum field to be unique, although this will not match our example, since the seller will be assigned to multiple customers. Therefore, the table constraint is UNIQUE, most useful when you don't want to force individual genders to be unique.Let's say, for example, that we developed a table to keep track of all orders every day for each seller. Each row of such a table represents the sum of numbers of any order, and not just an individual order. In this case, we could eliminate some possible mistakes making sure that there is no more than one row for each day for a given seller, or that each combination of the snum and odate fields is unique. Here's how, for example, we could create a table named Salestotal: CREATE TABLE Salestotal (cnum integer NOT NULL, odate date NULL, totamt decimal, UNIQUE (snum, odate)); There is also a command that you will use to put the current data into this table: INSERT INTO Salestotal SELECT snum, odate, SUM (amt) FROM Orders GROUP BY snum, odate;
PRIMARY KEY CONSTRAINTS
Before this, we thought of primary keys solely as logical concepts. While we know what a primary key is and how it should be used in any table, we don't know if SQL "knows" about it. Therefore, we used a UNIQUE constraint or unique indexes on the primary keys to enforce uniqueness on them. In earlier versions SQL language, it was necessary and could be done this way. However, SQL now supports primary keys directly with the PRIMARE KEY constraint. This limitation may or may not be available on your system.PRIMARY KEY can constrain tables or their columns. This constraint works the same as a UNIQUE constraint, except that only one primary key (for any number of columns) can be defined for a given table. There is also a difference between primary keys and column uniqueness in the way they are used with foreign keys, which will be covered in Chapter 19. The syntax and definition of their uniqueness are the same as for the UNIQUE constraint.
Primary keys cannot allow null values. This means that, like the fields in a UNIQUE constraint, any field used in a PRIMARY KEY constraint must already be declared NOT NULL . There is an improved option for creating our Sellers table: CREATE TABLE Salestotal (snum integer NOT NULL PRIMARY KEY, sname char(10) NOT NULL UNIQUE, city char(10), comm decimal); As you can see, uniqueness (UNIQUE) of fields can be declared for the same table. It is best to place the PRIMARY KEY constraint on the field (or fields) that will form your unique row identifier, and keep the UNIQUE constraint on fields that need to be logically unique (such as phone numbers or the sname field) rather than to identify rows.
PRIMARY KEYS FOR MORE THAN ONE FIELD
A PRIMARY KEY constraint can also be applied to multiple fields that have a unique combination of values. Let's say your primary key is im, and you have the first im and the last im stored in two different fields (so you can organize the data using either one). Obviously, neither the first nor the last can be forced to be unique on their own, but we can make each of these two combinations unique. We can apply the PRIMARY KEY table constraint on pairs: CREATE TABLE Namefield (firstname char (10) NOT NULL, lastname char (10) NOT NULL city char (10), PRIMARY KEY (firstname, lastname)); One problem with this approach is that we can force uniqueness to appear - for example, by introducing Mary Smith and M. Smith. This can be confusing because your employees may not know which one is which. Typically, a more reliable way to define a numeric field that can distinguish one row from another is to have a primary key, and apply a UNIQUE constraint on the two field names.CHECKING FIELD VALUES
Of course, there are any number of restrictions that you can set on the data entered into your tables to see, for example, whether the data is in the appropriate range or the correct format, which SQL naturally cannot know in advance. For this reason, SQL provides you with a CHECK constraint, which allows you to set a condition that a value entered into a table must satisfy before it is accepted. A CHECK constraint consists of the CHECK keyword followed by a predicate clause that uses the specified field. Any attempt to modify or insert a gender value that would make this predicate invalid will be rejected.Let's look at the Sellers table again. The commission column is expressed as a decimal number and can therefore be multiplied directly by the purchase amount to produce the dollar amount of the seller's commission with a dollar sign on the right. Someone may use the concept of percentage, but you may not even know about it. If a person mistakenly enters 14 instead of .14 to indicate his commission percentage, it will be interpreted as 14.0, which is a legal decimal value, and will be accepted normally by the system. To prevent this error, we can impose a column constraint - CHECK to ensure that the entered value is less than 1. CREATE TABLE Salespeople (snum integer NOT NULL PRIMARY KEY, sname char(10) NOT NULL UNIQUE, city char(10), comm decimal CHECK (comm< 1));
USING -CHECK TO PREDETERMINE A VALID INPUT VALUE
We can also use a CHECK constraint to prevent certain values from being entered into a field, thus preventing an error. For example, let's say the only cities in which we had sales offices were London, Barcelona, San Jose, and NY. If you know all the salespeople working in each of these departments, there is no need to allow other values to be entered. If not, using a constraint can prevent typos and other errors. CREATE TABLE Salespeople (snum integer NOT NULL UNIQUE, sname char(10) NOT NULL UNIQUE, city char(10) CHECK, (city IN ("London", "New York", "San Jose", "Barselona")), comm decimal CHECK (comm< 1)); Конечно, если вы собираетесь сделать это, вы должны быть уверены что ваша компания не открыла уже новых других ведомств сбыта. Большинство программ баз данных поддерживают ALTER command TABLE (see Chapter 17) which allows you to change the definition of a table even while it is in use. However, changing or removing restrictions is not always possible for these commands, even where it seems to be supported. If you were using a system that cannot remove constraints, you will have to CREATE a new table and pass information from the old table to it whenever you want to change a constraint. Of course, you will not want to do this often, and over time you will stop doing it altogether.Let's create a table of Orders: CREATE TABLE Orders (onum integer NOT NULL UNIQUE, amt decimal, odate date NOT NULL, cnum integer NOT NULL, snum integer NOT NULL); As we discussed in Chapter 2, the DATE type is widely supported but is not part of the ANSI standard. What should we do if we use a database that follows ANSI and does not recognize the DATE type? If we declare the odate field to be any type of number, we cannot use the backslash (/) or dash (-) as a separator. Since the numbers being printed are ASCII characters, we can declare the type of the date field to be CHAR. The main problem is that we will have to use single quotes whenever we refer to the odate field value in the query. No more simple solution This problem is where the DATE type has become so popular. To illustrate, let's declare the odate field to be a CHAR type. We can at least impose our format on it with the CHECK constraint: CREATE TABLE Orders (onum integer NOT NULL UNIQUE, amt decimal, odate char (10) NOT NULL CHECK (odate LIKE "--/--/--- -"), cnum NOT NULL, snum NOT NULL); Additionally, if you want, you can impose a constraint to ensure that the characters entered are numbers, and that they are within the values of our range.
CONDITION CHECKING BASED ON MULTIPLE FIELDS
You can also use CHECK as a table constraint. This is useful in cases where you want to include more than one row in a condition. Let's assume that commissions of .15 and above will only be allowed for a seller from Barcelona. You can specify this with the following CHECK table constraint: CREATE TABLE Salespeople (snum integer NOT NULL UNIQUE, sname char(10) NOT NULL UNIQUE, city char(10), comm decimal, CHECK (comm< .15 OR city = "Barcelona")); Как вы можете видеть, два различных пол должны быть проверены чтобы определить, верен предикат или нет. Имейте в виду, что это - два разных пол одной и той же строки. Хотя вы можете использовать многочисленные поля, SQL не может проверить более одной строки одновременно. Вы не можете например использовать ограничение CHECK чтобы удостовериться что все комиссионные в данном городе одинаковы. Чтобы сделать это, SQL должен всякий раз просматривая другие строки таблицы, когда вы модифицируете или вставляете строку, видеть, что значение комиссионных указано для текущего города. SQL этого делать не умеет.In fact, you could use a complex CHECK limit for the above if you knew in advance what the fees should be in different cities. For example, you could set a constraint like this: CHECK ((comm = .15 AND clty = "London") OR (comm = .14 AND city = "Barcelona") OR (comm = 11 AND city = "San Jose") ..) You get the idea. Rather than impose such a set of restrictions, you could simply use a view with a WITH CHECK OPTION clause that has all these conditions in its predicate (see Chapter 20 for both the view and WITH CHECK OPTION). Users can access a table view instead of the table itself. One advantage of this will be that the procedure for changing the restriction will not be as painful or time consuming. The WITH CHECK OPTION view is a good substitute for the CHECK constraint, as will be shown in Chapter 21.
SETTING DEFAULT VALUES
When you insert a row into a table without specifying values in it for each field, SQL must have a default value to include it in the specific field, or the command will be rejected. The most common default value is NULL. This is the default value for any column that has not been given a NOT NULL constraint or that has had another default assignment.The DEFAULT value is specified in the CREATE TABLE command in the same way as a column constraint, although, technically speaking, the DEFAULT value is not a restrictive property - it does not limit the values you can enter, but simply determines what can happen if you do not enter any of them. Let's say you work in a New York office and the vast majority of your salespeople live in New York. You can specify New York as the default city gender value for your Salespeople table: CREATE TABLE Salespeople (snum integer NOT NULL UNIQUE, sname char(10) NOT NULL UNIQUE, city char(10) DEFAULT = "New York", comm decimal CHECK (comm< 1); Конечно, вводить значение Нью Йорк в таблицу каждый раз когда назначается новый продавец, не такая уж необходимость, и можно просто пренебречь им (не ввод его) даже если оно должно иметь некоторое значение. Значение по умолчанию такого типа, более предпочтительно, чем, например, длинный конторский номер указывающий на ваше собственное ведомство, в таблице Порядков. Длинные числовые значения - более расположены к ошибке, поэтому если подавляющее большинство (или все) ваших порядков должны иметь ваш собственный конторский номер, желательно устанавливать для них значение по умолчанию.
Another way to use a default value is to use it as an alternative to NULL. Since NULL is (in fact) false under any comparison other than IS NULL, it can be eliminated using most predicates. Sometimes, you need to see the empty values of your fields without processing them in any particular way. You can set a default value, such as zero or space, which is functionally smaller in value than the unset value - NULL. The difference between them and a regular NULL is that SQL will treat them just like any other value.
Let's assume that customers are not initially assigned estimates. Every six months, you increase the rating of all your customers who have a below-average rating, including those who have not previously had any rating assignment. If you want to select all of these customers as a group, the following query will exclude all customers with rating = NULL: SELECT * FROM Customers WHERE rating< = 100; Однако, если вы назначили значение по умолчанию = 000, в поле rating, заказчики без оценок будут выбраны наряду с другими. Приоритет каждого метода - зависит от ситуации. Если вы будете делать запрос с помощью пол оценки, то захотите ли Вы включить строки без значений, или исключите их? Друга характеристика значений по умолчанию этого типа, позволит объявить Вам поле оценки - как NOT NULL. Если вы используете его по умолчанию, чтобы избежать значений = NULL, то это - вероятно хороша защита от ошибок.
You can also use UNIQUE or PRIMARY KEY constraints on this field. If you do this, you mean that only one row at a time can have a default value. Any row that contains a default value will need to be modified before another row with a default value can be inserted. This is not how you normally use default values, so UNIQUE and PRIMARY KEY constraints (especially the latter) are not usually set on rows with default values.
SUMMARY
You now have several ways to control the values that can be entered into your tables. You can use a NOT NULL constraint to exclude NULLs, a UNIQUE constraint to force all values in a group of one or more columns to be different from each other, a PRIMARY KEY constraint to do basically the same thing as UNIQUE but with a different ending, and finally a CHECK constraint to define your own custom conditions so that values encountered before them can be entered. Alternatively, you can use a DEFAULT clause, which will automatically insert a default value into any field whose name is not specified in the INSERT, just as a NULL value is inserted when the DEFAULT clause is not set and there is no NOT NULL constraint. FOREIGN KEY or REFERENCES constraints, which you'll learn about in Chapter 19, are very similar to these, except that they link a group of one or more fields to another group, and thus immediately affect the values that can be entered into any of those groups. .WORKING WITH SQL
1. Create an Order table so that all onum field values, as well as all combinations of the cnum and snum fields, are different from each other, and so that NULL values are excluded from the date field.2. Create a Sales table so that the default commission is 10%, does not allow NULL values, so that the snum field is the primary key, and so that all names are in alphabetical order between A and M inclusive (keeping in mind that all names will be printed in upper case).
3. Create an Order table, being sure that the onum field is greater than the cnum field, and cnum is greater than snum. NULL values are not allowed in any of these three fields.
A unique constraint prevents two records from being created that have the same value in a column. In the CUSTOMERS table, for example, you might want to create two or more people having the same age.
Example
For example, the following SQL query creates a new table named CUSTOMERS and adds five columns. Here the AGE column is set to UNIQUE so you can't have two records with the same age.
CREATE TABLE CUSTOMERS(ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL UNIQUE, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID));
If the CUSTOMERS table is already created, then if you want to add a UNIQUE constraint on the AGE column, you must write a statement like the query that is specified in the code block below.
ALTER TABLE CUSTOMERS MODIFY AGE INT NOT NULL UNIQUE;You can also use the following syntax, which supports naming restrictions on multiple columns.
ALTER TABLE CUSTOMERS ADD CONSTRAINT myUniqueConstraint UNIQUE(AGE, SALARY);
Removing the UNIQUE constraint
To remove the UNIQUE constraint, use the following SQL query.
Loading...
