CHECK Constraints Can Refer to Multiple Columns To sum it up, for the NOT NULL constraints, we must declare them inline. However, when running the code below, we can see that it does not work: CREATE TABLE employee (ĬONSTRAINT nn_emp_ln NOT NULL (last_name) This means that you can’t declare it as out of line.Įxecute the following code: CREATE TABLE employee ( There’s only one constraint type that can be declared as an inline constraint. NOT NULL Constraints Can Only Be Declared Inline Especially, when we have cases determining if primary key or foreign key are being used. Such names as pk_emp or ck_emp_lnlen would be more descriptive than the generic EMP1290894FH name.Īlso, when reviewing execution plans, the constraint name is often used in the output, which makes it easier to work out how the plan is being executed. When you get errors in SQL statements, PL/SQL code, or application code, it is a good idea to use the constraint name and know what it refers to or at least make a guess. Usually, this name does not provide any useful information. Without specifying the name, Oracle automatically generates a name for the constraint that it does for all inline constraints. Having named constraints can be helpful in several situations. So, why do we need to assign a name to a constraint? It might seem that it’s just unnecessary typing, however, it is not. We have specified the following names for a few constraints: When creating out-of-line constraints, we can specify a name. While this might seem a waste of time, it can be helpful.Ĭonsider this on a particular example: CREATE TABLE employee (ĬONSTRAINT fk_emp_deptid FOREIGN KEY (dept_id) REFERENCES department (dept_id),ĬONSTRAINT ck_emp_lnlen CHECK (LENGTH(last_name) > 3) Out-of-Line Constraints Can Have Names Specified Now, let us analyze the difference between these two types of constraints, other than where they are declared. This concept works in the same way regardless of the constraint type. We add it at the end of the CREATE TABLE statement.įor example, we have the following script: CREATE TABLE employee (Īs you can see, we set the PRIMARY KEY constraint, called pk_emp, to the emp_id column at the end of the statement. What is an Out-of-Line Constraint?Īn out-of-line constraint is the constraint declared on a separate line for the column. The concept is the same regardless of the constraint type. Thus, we have created a primary key constraint on this column by adding the keywords. In this example, the words PRIMARY KEY after the emp_id column indicate that emp_id is the primary key. In this article, I am going to explore these constraints and the advantages they have, as well as explain which one I recommend and why.Īn inline constraint is a constraint you declare on the same line as the column when creating a table. In SQL, there are two ways of creating constraints on a table: inline and out of line. Constraints on tables and columns allow you to enforce the data quality.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |