What is the difference between a column constraint and a table constraint?

Constraints can be specified for individual columns as part of the column specification [column-level constraints] or for groups of columns as part of the table definition [table-level constraints].

The constraint has the following syntax:

[CONSTRAINT constraint_name] constraint

Defines a name for the constraint. If the name is omitted, the DBMS Server assigns one. The constraint name is used when dropping the constraint using the ALTER TABLE statement.

Note:  We recommend defining a name when creating a constraint; otherwise system catalogs must be queried to determine the system-defined name.

Is either a column-level constraint [column_constraint] or table-level constraint [table_constraint].

Table constraints are validations which can be applied on a group of columns of the table. For exp – CHECK, UNIQUE etc.

Column level constraints are validations which can be applied on individual columns of the tables. For exp – NOT NULL, DEFAULT etc.

My select statement is not working as expected, So, to overcome from such issues what are the steps needed to be taken care?

2 Answers   CG,

Hi Guys, I have a situation where I need to access the column values from rowtype variable. However, the column names are dynamic. below is sample code: declare Cursor c1 is select * from emp; Cursor c2 is select column_name from xyztable; v_c2 c2%rowtype; v_str varchar2 v_value varchar2[200]; begin for rec in c1 loop open c2;---this cursor has column names like EMPLOYEE_ID, FIRST_NAME, LAST_NAME etc. loop fetch c2 into v_c2; exit when c2%notfound; /* now lets say i want to access value of LAST_NAME from cursor c1, so I am writing below code, however it does not work as expected */ v_str:= 'rec.'|| v_c2.column_name; -- this will give me string like "rec.EMPLOYEE_ID" v_value:=v_str; end loop; end loop; end; / Plz help ASAP.Thanks.

2 Answers  

What action do you have to perform before retrieving data from the next result set of a stored procedure ?

are user defined requirements that define what values are valid for a column or table. You can think of them as additional restrictions to narrow in on acceptable values more strictly than data types allow.

Constraints allow you to define qualities that all entries must have, with the server itself enforcing the restrictions upon data entry or update. As an example, it might not make sense for a column representing boiling point of various substances to be lower than its freezing point. A constraint can enforce this type of requirement, even though types would not be able to.

ERROR 3819 [HY000]: Check constraint 'qualified_borrowers_chk_1' is violated.
3 cannot be used as a table constraint. However, you can approximate the results by using 
ERROR 3819 [HY000]: Check constraint 'qualified_borrowers_chk_1' is violated.
4 as the statement within a 
ERROR 3819 [HY000]: Check constraint 'qualified_borrowers_chk_1' is violated.
5 table constraint.
Let's look at how column and table constraints differ.
Column constraints
Column constraints are constraints attached to a single . They are used to determine whether a proposed value for a column is valid or not. Column constraints are evaluated after the input is validated against basic type requirements [like making sure a value is a whole number for 
ERROR 3819 [HY000]: Check constraint 'qualified_borrowers_chk_1' is violated.
6 columns].
Column constraints are great for expressing requirements that are limited to a single field. They attach the constraint condition directly to the column involved. For instance, we could model the 
ERROR 3819 [HY000]: Check constraint 'qualified_borrowers_chk_1' is violated.
7 restriction in a 
ERROR 3819 [HY000]: Check constraint 'qualified_borrowers_chk_1' is violated.
8 table by adding a constraint after the column name and data type:
CREATE TABLE person [
    . . .
    age INT CHECK [age >= 0],
    . . .
];

This snippet defines a

ERROR 3819 [HY000]: Check constraint 'qualified_borrowers_chk_1' is violated.
8 table with one of the columns being an 
ERROR 3819 [HY000]: Check constraint 'qualified_borrowers_chk_1' is violated.
6 called 
ERROR 3819 [HY000]: Check constraint 'qualified_borrowers_chk_1' is violated.
7. The 
ERROR 3819 [HY000]: Check constraint 'qualified_borrowers_chk_1' is violated.
7 must be greater than or equal to zero. Column constraints are easy to understand because they are added as additional requirements onto the column they affect.
Table constraints
The other type of constraint is called a table constraint.  constraints can express almost any restrictions that a column constraint can, but can additionally express restrictions that involve more than one column. Instead of being attached to a specific column, table constraints are defined as a separate component of the table and can reference any of the table's columns.
The column constraint we saw earlier could be expressed as a table constraint like this:
CREATE TABLE person [
    . . .
    age INT,
    . . .
    CHECK [age >= 0]
];
The same basic syntax is used, but the constraint is listed separately. To take advantage of the ability for table constraints to introduce compound restrictions, we can use the logical 
CONSTRAINT  
3 operator to join multiple conditions from different columns.
For example, in a banking database, a table called 
CONSTRAINT  
4 might need to check whether individuals have an existing account and the ability to offer collateral in order to qualify for a loan. It might make sense to include both of these in the same check:
CREATE TABLE qualified_borrowers [
    . . .
    account_number INT,
    acceptable_collateral BOOLEAN,
    . . .
    CHECK [account_number IS NOT NULL AND acceptable_collateral = true]
];
Here, we use the 
ERROR 3819 [HY000]: Check constraint 'qualified_borrowers_chk_1' is violated.
5 constraint again to check that the 
CONSTRAINT  
6 is not null and that the loan officer has marked the client as having acceptable collateral by checking the 
CONSTRAINT  
7 column. A table constraint is necessary since multiple columns are being checked.
Now is a good time to mention that although we'll mainly be using the 
CONSTRAINT  
8 SQL command in these examples to create a new table, you can also add constraints to an existing table with 
CONSTRAINT  
9. When using 
CONSTRAINT  
9, new constraints cause the values currently in the table to be checked against the new constraint. If the values violate the constraint, the constraint cannot be added.
Creating names for constraints
Default constraint names
When you create constraints using the syntax above, MySQL automatically chooses a reasonable, but vague, name. In the case of the 
CONSTRAINT  
4 table above, MySQL would name the constraint 
CREATE TABLE qualified_borrowers [
    . . .
    account_number INT,
    acceptable_collateral BOOLEAN,
    . . .
    CONSTRAINT loan_worthiness CHECK [account_number IS NOT NULL AND acceptable_collateral = true]
];
2:
INSERT INTO qualified_borrowers VALUES [123, false];
ERROR 3819 [HY000]: Check constraint 'qualified_borrowers_chk_1' is violated.
This name gives you information about the table and type of constraint when a constraint is violated. In cases where multiple constraints are present on a table, however, more descriptive names are helpful to help troubleshooting.

  

Custom constraint names
You can optionally specify the name for your constraints by preceding the constraint definition with the 
CREATE TABLE qualified_borrowers [
    . . .
    account_number INT,
    acceptable_collateral BOOLEAN,
    . . .
    CONSTRAINT loan_worthiness CHECK [account_number IS NOT NULL AND acceptable_collateral = true]
];
3 keyword followed by the name.
The basic syntax for adding a custom name is this:
CONSTRAINT  
For example, if you wanted to name the constraint in the 
CONSTRAINT  
4 table 
CREATE TABLE qualified_borrowers [
    . . .
    account_number INT,
    acceptable_collateral BOOLEAN,
    . . .
    CONSTRAINT loan_worthiness CHECK [account_number IS NOT NULL AND acceptable_collateral = true]
];
5, you could instead define the table like this:
CREATE TABLE qualified_borrowers [
    . . .
    account_number INT,
    acceptable_collateral BOOLEAN,
    . . .
    CONSTRAINT loan_worthiness CHECK [account_number IS NOT NULL AND acceptable_collateral = true]
];
Now, when we violate a constraint, we get our more descriptive label:
INSERT INTO qualified_borrowers VALUES [123, false];
ERROR 3819 [HY000]: Check constraint 'loan_worthiness' is violated.
You can name column constraints in the same way:
CREATE TABLE teenagers [
    . . .
    age INT CONSTRAINT is_teenager CHECK [age >= 13 AND age = 0]
];
1
CREATE TABLE person [
    . . .
    age INT,
    . . .
    CHECK [age >= 0]
];
2
Values that evaluate to false produce an error indicating that the constraint was not satisfied:
CREATE TABLE person [
    . . .
    age INT,
    . . .
    CHECK [age >= 0]
];
3
CREATE TABLE person [
    . . .
    age INT,
    . . .
    CHECK [age >= 0]
];
4
In this case, the film has satisfied every condition except for the number of votes required. MySQL rejects the submission since it does not pass the final table check constraint.
Not null constraints
The 
ERROR 3819 [HY000]: Check constraint 'qualified_borrowers_chk_1' is violated.
3 constraint is much more focused. It guarantees that values within a column are not null. While this is a simple constraint, it is used very frequently.
How to add not null constraints in MySQL
To mark a column as requiring a non-null value, add 
ERROR 3819 [HY000]: Check constraint 'qualified_borrowers_chk_1' is violated.
3 after the type declaration:
CREATE TABLE person [
    . . .
    age INT,
    . . .
    CHECK [age >= 0]
];
5
In the above example, we have a simple two column table mapping countries to their national capitals. Since both of these are required fields that would not make sense to leave blank, we add the 
ERROR 3819 [HY000]: Check constraint 'qualified_borrowers_chk_1' is violated.
3 constraint.
Inserting a null value now results in an error:
CREATE TABLE person [
    . . .
    age INT,
    . . .
    CHECK [age >= 0]
];
6
CREATE TABLE person [
    . . .
    age INT,
    . . .
    CHECK [age >= 0]
];
7
The 
ERROR 3819 [HY000]: Check constraint 'qualified_borrowers_chk_1' is violated.

  

3 constraint functions only as a column constraint [it cannot be used as a table constraint]. However, you can easily work around this by using 
ERROR 3819 [HY000]: Check constraint 'qualified_borrowers_chk_1' is violated.
4 within a table 
ERROR 3819 [HY000]: Check constraint 'qualified_borrowers_chk_1' is violated.
5 constraint.
For example, this offers equivalent guarantees using a table constraint:
CREATE TABLE person [
    . . .
    age INT,
    . . .
    CHECK [age >= 0]
];
8
RELATED ON PRISMA.IO
When working with Prisma Client, you can control whether each field is  to get equivalent functionality to the 
ERROR 3819 [HY000]: Check constraint 'qualified_borrowers_chk_1' is violated.
3 constraint in PostgreSQL.
Prisma is an open-source database toolkit for Typescript and Node.js that aims to make app developers more productive and confident when working with databases.
Unique constraints
The 
INSERT INTO qualified_borrowers VALUES [123, false];
6 constraint tells MySQL that each value within a column must not be repeated. This is useful in many different scenarios where having the same value in multiple records should be impossible.
For example, columns that deals with IDs of any kind should, by definition, have unique values. A social security number, a student or customer ID, or a product UPC [barcode number] would be useless if they were not able to differentiate between specific people or items.
A 
INSERT INTO qualified_borrowers VALUES [123, false];
6 constraint can be specified at the column level:
CREATE TABLE person [
    . . .
    age INT,
    . . .
    CHECK [age >= 0]
];
9
They can also be specified as table constraints:
CREATE TABLE qualified_borrowers [
    . . .
    account_number INT,
    acceptable_collateral BOOLEAN,
    . . .
    CHECK [account_number IS NOT NULL AND acceptable_collateral = true]
];
0
One of the advantages of using 
INSERT INTO qualified_borrowers VALUES [123, false];
6 table constraints is that it allows you to perform uniqueness checks on a combination of columns. This works by specifying two or more columns that MySQL should evaluate together. The values in individual columns may repeat but the combination of values specified must be unique.
As an example, let's look back at the 
INSERT INTO qualified_borrowers VALUES [123, false];
9 table we used before:
CREATE TABLE qualified_borrowers [
    . . .
    account_number INT,
    acceptable_collateral BOOLEAN,
    . . .
    CHECK [account_number IS NOT NULL AND acceptable_collateral = true]
];
1
If we wanted to make sure that we don't add multiple records for the same pair, we could add 
INSERT INTO qualified_borrowers VALUES [123, false];
6 constraints to the columns here:
CREATE TABLE qualified_borrowers [
    . . .
    account_number INT,
    acceptable_collateral BOOLEAN,
    . . .
    CHECK [account_number IS NOT NULL AND acceptable_collateral = true]
];
2
This would ensure that both the countries and capitals are only present once in each table. However, some countries have multiple capitals. This would mean we may have multiple entries with the same 
ERROR 3819 [HY000]: Check constraint 'loan_worthiness' is violated.
1 value. These wouldn't work with the current design:
CREATE TABLE qualified_borrowers [
    . . .
    account_number INT,
    acceptable_collateral BOOLEAN,
    . . .
    CHECK [account_number IS NOT NULL AND acceptable_collateral = true]
];
3
CREATE TABLE qualified_borrowers [
    . . .
    account_number INT,
    acceptable_collateral BOOLEAN,
    . . .
    CHECK [account_number IS NOT NULL AND acceptable_collateral = true]
];
4
If we still want to make sure we don't end up with duplicate entries while allowing for repeated values in individual columns, a unique check on the combination of 
ERROR 3819 [HY000]: Check constraint 'loan_worthiness' is violated.
1 and 
ERROR 3819 [HY000]: Check constraint 'loan_worthiness' is violated.
3 would suffice:
CREATE TABLE qualified_borrowers [
    . . .
    account_number INT,
    acceptable_collateral BOOLEAN,
    . . .
    CHECK [account_number IS NOT NULL AND acceptable_collateral = true]
];
5
Now, we can add both of Bolivia's capitals to the table without an error:

  

CREATE TABLE qualified_borrowers [
    . . .
    account_number INT,
    acceptable_collateral BOOLEAN,
    . . .
    CHECK [account_number IS NOT NULL AND acceptable_collateral = true]
];
3
CREATE TABLE qualified_borrowers [
    . . .
    account_number INT,
    acceptable_collateral BOOLEAN,
    . . .
    CHECK [account_number IS NOT NULL AND acceptable_collateral = true]
];
7
However, attempting to add the same combination twice is still caught by the constraint:
CREATE TABLE qualified_borrowers [
    . . .
    account_number INT,
    acceptable_collateral BOOLEAN,
    . . .
    CHECK [account_number IS NOT NULL AND acceptable_collateral = true]
];
8
CREATE TABLE qualified_borrowers [
    . . .
    account_number INT,
    acceptable_collateral BOOLEAN,
    . . .
    CHECK [account_number IS NOT NULL AND acceptable_collateral = true]
];
9
Primary key constraints
The  constraint serves a special purpose. It indicates that the column can be used to uniquely identify a record within the table. This means that it must be reliably unique and that every record must have a value in that column.
Primary keys are recommended for every table but not required, and every table may only have one primary key. Primary keys are mainly used to identify, retrieve, modify, or delete individual records within a table. They allow users and administrators to target the operation using an identifier that is guaranteed by MySQL to match exactly one record.
Let's use the 
ERROR 3819 [HY000]: Check constraint 'loan_worthiness' is violated.
5 table we saw before as an example:
CREATE TABLE person [
    . . .
    age INT,
    . . .
    CHECK [age >= 0]
];
9
Here we've identified that the 
ERROR 3819 [HY000]: Check constraint 'loan_worthiness' is violated.
6 should be unique. If we wanted to use this column as our primary key [guaranteeing uniqueness and a non-null value], we could simply change the 
INSERT INTO qualified_borrowers VALUES [123, false];
6 constraint to 
ERROR 3819 [HY000]: Check constraint 'loan_worthiness' is violated.
4:
INSERT INTO qualified_borrowers VALUES [123, false];
1
This way, if we needed to update the inventory amounts for a specific supply, we could target it using the primary key:
INSERT INTO qualified_borrowers VALUES [123, false];
2
INSERT INTO qualified_borrowers VALUES [123, false];
3
While many tables use a single column as the primary key, it is also possible to create a primary key using a set of columns, as a table constraint.
The 
INSERT INTO qualified_borrowers VALUES [123, false];
9 table is a good candidate to demonstrate this. If we wanted to create a primary key using the existing columns, we could replace the 
INSERT INTO qualified_borrowers VALUES [123, false];
6 table constraint with 
ERROR 3819 [HY000]: Check constraint 'loan_worthiness' is violated.
4:
INSERT INTO qualified_borrowers VALUES [123, false];
4
Foreign key constraints
 are columns within one table that reference column values within another table. This is desirable and often necessary in a variety of scenarios where tables contain related data. This ability for the database to easily connect and reference data stored in separate tables is one of the primary features of relational databases.
For example, you may have a 
CREATE TABLE teenagers [
    . . .
    age INT CONSTRAINT is_teenager CHECK [age >= 13 AND age = 13 AND age = 13 AND age = 13 AND age = 13 AND age = 13 AND age = 13 AND age = 0]
];
01 column in the 
CREATE TABLE teenagers [
    . . .
    age INT CONSTRAINT is_teenager CHECK [age >= 13 AND age = 0]
];
01 column in the 
CREATE TABLE teenagers [
    . . .
    age INT CONSTRAINT is_teenager CHECK [age >= 13 AND age = 0]
];
05 alias, which stands for 
CREATE TABLE person [
    . . .
    age INT,
    . . .
    CHECK [age >= 0]
];
06, so we can use 
CREATE TABLE person [
    . . .
    age INT,
    . . .
    CHECK [age >= 0]
];
07 as our data type for the 
CREATE TABLE teenagers [
    . . .
    age INT CONSTRAINT is_teenager CHECK [age >= 13 AND age = 13 AND age = 13 AND age = 13 AND age = 13 AND age = 13 AND age = 13 AND age = 13 AND age