check table assignment to a field provides

• Stack Overflow Public questions & answers
• Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
• Talent Build your employer brand
• Advertising Reach developers & technologists worldwide
• Labs The future of collective knowledge sharing
• About the company

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

SQL Server - validate table column values against with values provided

I have the following task that want to solve using SQL Server's query and/or stored procedure, and I would really appreciated if someone can give me some directions.

Basically we have a data warehouse based on SQL Server. I want to validate columns in certain tables to ensure the values in these columns are valid.

Example as below:

Table1 ColumnsToValidate specifies the table/columns in which values need to be validated. In the example I want to validate the Gender column of the Customer table, and the State column of the Address table. And the validationID is a foreign-key to a table holding all the valid values ( Table2 ).

Table2 ValidationValues : this table holds all valid values for specific validation rules. In the example, validation rule #1 ( ValidationID = 1 ) has two valid values, and validation rule #2 specified 3 valid values.

I'd like to (using SQL) dynamically create a query based on values in Table 1, which accordingly selects the Customer.Gender column and the Address.State column, so the values in these columns can be validated against the values in Table 2.

Table1: ColumnsToValidate

Table2: ValidationValues

Table3: Customer

Table4: Address

EDIT: I could write this in a C# program, but the program will be slow. I would think there could be a way in pure SQL (SQL Server)

• just wish upon a star –  RoMEoMusTDiE Apr 16, 2018 at 2:39
• @maSTAShuFu too hard? –  Michael C Apr 16, 2018 at 2:56
• Here's a good start: meta.stackoverflow.com/questions/334822/… . Then, once you have that down, move onto this one: spaghettidba.com/2015/04/24/… –  Eric Brandt Apr 16, 2018 at 3:04
• @MichaelC if you let us do the imagination it will take ages... so just wish for now that what you want to do will appear in your OP. –  RoMEoMusTDiE Apr 16, 2018 at 3:05
• 1 An Option: You can create a table where you can insert the validation details after validating tables and use a store procedure to validate it by scheduling it in a job in the SQL Server Agent for the validation. –  Aasish Kr. Sharma Apr 16, 2018 at 3:37

Here is my solution, in two steps -

1) To validate the values "as if" the target table and column are known, e.g. the following outer join query finds invalid values on the Customer.Gender column.

2) Use dynamic-SQL to generate above SQL script, using values from table ColumnsToValidate , substituting table-name 'Customer' and column-name 'Gender' with variables @tab and @col1 :

As being said, these are mock-up codes and they are not tested. However I'd just to share the ideas to people having similar problems - the key to the solution is "Dynamic SQL".

Your Answer

Sign up or log in, post as a guest.

Required, but never shown

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct .

Not the answer you're looking for? Browse other questions tagged sql sql-server validation or ask your own question .

• The Overflow Blog
• What it’s like being a professional workplace bestie (Ep. 603)
• Journey to the cloud part I: Migrating Stack Overflow Teams to Azure
• Featured on Meta
• Moderation strike: Results of negotiations
• Our Design Vision for Stack Overflow and the Stack Exchange network
• Temporary policy: Generative AI (e.g., ChatGPT) is banned
• Discussions experiment launching on NLP Collective
• Call for volunteer reviewers for an updated search experience: OverflowAI Search

Hot Network Questions

• Word for when a life event (like a vacation) is coming to an end and you feel an immense feeling of sadness due to not wanting to leave
• What does the sky look like from the moon?
• Can anyone close non-rent-exempt accounts?
• Are PCIe and USB 3.0 the same interface?
• Pay raise linked to velocity?
• Why did 1990s-2000s LCD all use 60 Hz refresh?
• Is it an Element?
• Can you represent a language with a group with a small/simple generator set?
• What movie is this avocado-like monster from?
• Why do programming languages use delimiters (quotes) for strings?
• Science fiction book about a war veteran who was resurrected and goes on to meet a crab alien brain that gets installed onto his ship
• Correcting how a student writes symbols
• How in Bash to test a name/string is an executable
• I've been invited to a free 2-day conference. What's the catch?
• Pennylane CFI(Classical Fisher Information) plot
• is it possible to turn this dumb-waiter into an elevator?
• Analog science fact article about dimensions
• Turned away for medical service at private urgent care due to being enrolled in medicare even with private insurance
• Are there questions that science can't answer, but philosophy can?
• Why do many AI bots feel the need to be know-it-alls?
• Birds flying or swimming in space
• Constructing a vector of structs (with some custom constructors) from exactly two string literals crashes. Why?
• Non-invasive bubble sensor
• Underground Wiring Through Basement Wall

Your privacy

By clicking “Accept all cookies”, you agree Stack Exchange can store cookies on your device and disclose information in accordance with our Cookie Policy .

13.7.3.2 CHECK TABLE Statement

CHECK TABLE checks a table or tables for errors. CHECK TABLE can also check views for problems, such as tables that are referenced in the view definition that no longer exist.

To check a table, you must have some privilege for it.

CHECK TABLE works for InnoDB , MyISAM , ARCHIVE , and CSV tables.

Before running CHECK TABLE on InnoDB tables, see CHECK TABLE Usage Notes for InnoDB Tables .

CHECK TABLE is supported for partitioned tables, and you can use ALTER TABLE ... CHECK PARTITION to check one or more partitions; for more information, see Section 13.1.9, “ALTER TABLE Statement” , and Section 24.3.4, “Maintenance of Partitions” .

CHECK TABLE ignores virtual generated columns that are not indexed.

CHECK TABLE Output

Checking version compatibility, checking data consistency, check table usage notes for innodb tables, check table usage notes for myisam tables.

CHECK TABLE returns a result set with the columns shown in the following table.

The statement might produce many rows of information for each checked table. The last row has a Msg_type value of status and the Msg_text normally should be OK . Table is already up to date means that the storage engine for the table indicated that there was no need to check the table.

The FOR UPGRADE option checks whether the named tables are compatible with the current version of MySQL. With FOR UPGRADE , the server checks each table to determine whether there have been any incompatible changes in any of the table's data types or indexes since the table was created. If not, the check succeeds. Otherwise, if there is a possible incompatibility, the server runs a full check on the table (which might take some time).

Incompatibilities might occur because the storage format for a data type has changed or because its sort order has changed. Our aim is to avoid these changes, but occasionally they are necessary to correct problems that would be worse than an incompatibility between releases.

FOR UPGRADE discovers these incompatibilities:

The indexing order for end-space in TEXT columns for InnoDB and MyISAM tables changed between MySQL 4.1 and 5.0.

The storage method of the new DECIMAL data type changed between MySQL 5.0.3 and 5.0.5.

Changes are sometimes made to character sets or collations that require table indexes to be rebuilt. For details about such changes, see Section 2.10.4, “Changes in MySQL 8.0” . For information about rebuilding tables, see Section 2.10.13, “Rebuilding or Repairing Tables or Indexes” .

MySQL 8.0 does not support the 2-digit YEAR(2) data type permitted in older versions of MySQL. For tables containing YEAR(2) columns, CHECK TABLE recommends REPAIR TABLE , which converts 2-digit YEAR(2) columns to 4-digit YEAR columns.

Trigger creation time is maintained.

A table is reported as needing a rebuild if it contains old temporal columns in pre-5.6.4 format ( TIME , DATETIME , and TIMESTAMP columns without support for fractional seconds precision) and the avoid_temporal_upgrade system variable is disabled. This helps the MySQL upgrade procedure detect and upgrade tables containing old temporal columns. If avoid_temporal_upgrade is enabled, FOR UPGRADE ignores the old temporal columns present in the table; consequently, the upgrade procedure does not upgrade them.

To check for tables that contain such temporal columns and need a rebuild, disable avoid_temporal_upgrade before executing CHECK TABLE ... FOR UPGRADE .

Warnings are issued for tables that use nonnative partitioning because nonnative partitioning is removed in MySQL 8.0. See Chapter 24, Partitioning .

The following table shows the other check options that can be given. These options are passed to the storage engine, which may use or ignore them.

You can combine check options, as in the following example that does a quick check on the table to determine whether it was closed properly:

If CHECK TABLE finds no problems with a table that is marked as “ corrupted ” or “ not closed properly ” , CHECK TABLE may remove the mark.

If a table is corrupted, the problem is most likely in the indexes and not in the data part. All of the preceding check types check the indexes thoroughly and should thus find most errors.

To check a table that you assume is okay, use no check options or the QUICK option. The latter should be used when you are in a hurry and can take the very small risk that QUICK does not find an error in the data file. (In most cases, under normal usage, MySQL should find any error in the data file. If this happens, the table is marked as “ corrupted ” and cannot be used until it is repaired.)

FAST and CHANGED are mostly intended to be used from a script (for example, to be executed from cron ) to check tables periodically. In most cases, FAST is to be preferred over CHANGED . (The only case when it is not preferred is when you suspect that you have found a bug in the MyISAM code.)

EXTENDED is to be used only after you have run a normal check but still get errors from a table when MySQL tries to update a row or find a row by key. This is very unlikely if a normal check has succeeded.

Use of CHECK TABLE ... EXTENDED might influence execution plans generated by the query optimizer.

Some problems reported by CHECK TABLE cannot be corrected automatically:

Found row where the auto_increment column has the value 0 .

This means that you have a row in the table where the AUTO_INCREMENT index column contains the value 0. (It is possible to create a row where the AUTO_INCREMENT column is 0 by explicitly setting the column to 0 with an UPDATE statement.)

This is not an error in itself, but could cause trouble if you decide to dump the table and restore it or do an ALTER TABLE on the table. In this case, the AUTO_INCREMENT column changes value according to the rules of AUTO_INCREMENT columns, which could cause problems such as a duplicate-key error.

To get rid of the warning, execute an UPDATE statement to set the column to some value other than 0.

The following notes apply to InnoDB tables:

If CHECK TABLE encounters a corrupt page, the server exits to prevent error propagation (Bug #10132). If the corruption occurs in a secondary index but table data is readable, running CHECK TABLE can still cause a server exit.

If CHECK TABLE encounters a corrupted DB_TRX_ID or DB_ROLL_PTR field in a clustered index, CHECK TABLE can cause InnoDB to access an invalid undo log record, resulting in an MVCC -related server exit.

If CHECK TABLE encounters errors in InnoDB tables or indexes, it reports an error, and usually marks the index and sometimes marks the table as corrupted, preventing further use of the index or table. Such errors include an incorrect number of entries in a secondary index or incorrect links.

If CHECK TABLE finds an incorrect number of entries in a secondary index, it reports an error but does not cause a server exit or prevent access to the file.

CHECK TABLE surveys the index page structure, then surveys each key entry. It does not validate the key pointer to a clustered record or follow the path for BLOB pointers.

When an InnoDB table is stored in its own .ibd file , the first 3 pages of the .ibd file contain header information rather than table or index data. The CHECK TABLE statement does not detect inconsistencies that affect only the header data. To verify the entire contents of an InnoDB .ibd file, use the innochecksum command.

When running CHECK TABLE on large InnoDB tables, other threads may be blocked during CHECK TABLE execution. To avoid timeouts, the semaphore wait threshold (600 seconds) is extended by 2 hours (7200 seconds) for CHECK TABLE operations. If InnoDB detects semaphore waits of 240 seconds or more, it starts printing InnoDB monitor output to the error log. If a lock request extends beyond the semaphore wait threshold, InnoDB aborts the process. To avoid the possibility of a semaphore wait timeout entirely, run CHECK TABLE QUICK instead of CHECK TABLE .

CHECK TABLE functionality for InnoDB SPATIAL indexes includes an R-tree validity check and a check to ensure that the R-tree row count matches the clustered index.

CHECK TABLE supports secondary indexes on virtual generated columns, which are supported by InnoDB .

As of MySQL 8.0.14, InnoDB supports parallel clustered index reads, which can improve CHECK TABLE performance. InnoDB reads the clustered index twice during a CHECK TABLE operation. The second read can be performed in parallel. The innodb_parallel_read_threads session variable must be set to a value greater than 1 for parallel clustered index reads to occur. The default value is 4. The actual number of threads used to perform a parallel clustered index read is determined by the innodb_parallel_read_threads setting or the number of index subtrees to scan, whichever is smaller.

The following notes apply to MyISAM tables:

CHECK TABLE updates key statistics for MyISAM tables.

If CHECK TABLE output does not return OK or Table is already up to date , you should normally run a repair of the table. See Section 7.6, “MyISAM Table Maintenance and Crash Recovery” .

If none of the CHECK TABLE options QUICK , MEDIUM , or EXTENDED are specified, the default check type for dynamic-format MyISAM tables is MEDIUM . This has the same result as running myisamchk --medium-check tbl_name on the table. The default check type also is MEDIUM for static-format MyISAM tables, unless CHANGED or FAST is specified. In that case, the default is QUICK . The row scan is skipped for CHANGED and FAST because the rows are very seldom corrupted.

Search This Blog

Assigning check table to specific field in the custom table.

In SAP, a check table is a table that is used to define the domain of a data element. A data element is a technical object that is used to describe a field in a SAP database table. The check table is used to ensure that data entered in a field conforms to certain predefined values or ranges.

Assigning Check Table in a Custom Table Field

Step 1: Go to the custom table in change mode from the transaction code SE11. Select the required field for which we are going to assign a check table. Then click on the foreign key button.

Post a Comment

Popular posts from this blog, sap production order confirmation table list, adding custom quantity fields in the sap single entry confirmation screen, sap fiori data migration cockpit freezing problem.

• Ask a Question
• Write a Blog Post
• Login / Sign-up

A small tip to find out which table stores field checktable assignment information

For project reasons I need to find all tables whose fields have used a given table say COMM_PRODUCT as check table.

The only information I know is that such kind of table metadata is stored in table with name prefix DD*. Unfortunately there are huge number of table starting with DD:

How to efficiently find the exact one by yourself?

1. Make the check table field selected, and press F1:

In popup dialog, click this icon to display technical information:

Unfortunately, the found DD03P_D is a structure, so it is not possible to view it via tcode SE16. Nevertheless let’s write down the field name CHECKTABLE.

2. Go to SE80 -> Repository information system, double check ABAP Dictionary -> Fields -> Table fields, type field name as CHECKTABLE and search

The DD08L seems to be the table I am looking for.

Double confirm in SE16:

Confirmed 🙂

Assigned Tags

Thanks for sharing.

• Share Right click and copy the link to share this comment

Good Information. Thanks for the post.. 🙂

Good way, how about to use where-used feature?

Insert/edit link

Enter the destination URL

Or link to existing content

  Foreign Keys  

You use foreign keys to define relationships between tables in the ABAP Dictionary, create value checks for input fields and link several tables in a view or a lock object .

Field Assignment in the Foreign Key

A foreign key links two tables T1 and T2 by assigning fields of table T1 to the primary key fields of table T2 . In the following figure you can see the relationships between the fields of these two tables.

Table T1 is called the foreign key table (dependent table) and table T2 the check table (referenced table). The pair of fields for the two tables must have the same data type and length. One field of the foreign key table corresponds to each key field of the check table. This field is called the foreign key field.

A foreign key allows you to assign data records in the foreign key table and check table. By using the entries in the foreign key fields, one record of the foreign key table uniquely identifies one record of the check table.

Check Field and Value Check

One of the foreign key fields is marked as the check field. The foreign key relationship is maintained for this field.

When you make an entry in the check field, the system checks whether the check table contains a record with the key defined by the values in the foreign key fields. If there is such a record, the entry is valid. Otherwise the system rejects the entry.

The figure above is an example of the usage of check fields. In this example the entry Field2 = 2 and Field4 = 2 would be rejected since T2 does not contain a record with the key Field5 = 2 and Field6 = 2.

You have the option of not checking against all the key fields of the check table. To exclude fields of the foreign key table from the assignment of the fields to the check table, use generic and constant foreign keys .

How the Input Check Works

A SELECT statement is generated from the definition of the foreign key. If you make an entry in the check field, the system submits this SELECT statement. If the system finds a suitable record of the check table, the entry is valid. Otherwise the system rejects the entry.

The corresponding SELECT statement has the following form for the foreign key table shown in the figure above:

SELECT * FROM T2 WHERE T2-FIELD5 = T1-FIELD2 AND T2-FIELD6 = T1-FIELD4.

This SQL statement is a pseudo code that explains the corresponding functionality. The syntax of this statement is not the same in ABAP.

A screen entry for check field Field2 is valid if the check table contains a record with the entries made in the screen for Field2 and Field4 as key.

Table SBOOK in the flight model contains the customer’s flight bookings for a carrier. The flight bookings can be made by a travel agency or directly at the carrier’s sales counter. If the booking was made at a counter, its number is stored together with the booking in field COUNTER in table SBOOK.

You must make sure that only correct counter numbers can be entered in table SCOUNTER. The necessary value check can be defined by creating a foreign key for check field COUNTNUM. This is explained in the figure below.

Multi-Structured Foreign Keys

Semantic Attributes of Foreign Keys

Creating Foreign Keys