Questions tagged [data-integrity]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
0 answers
35 views

Is creating table entry from another table entry and copying its value good practices?

Lets say I have inventory_receives table which contain many inventory_receive_entry and also I have inventory table which contain one inventory_receive_entry Then in my application inventory_receives ...
user avatar
  • 13
2 votes
2 answers
99 views

How do you handle "undo" in databases in general, and PostgreSQL specifically?

I have a complex PostgreSQL-based system which I made myself from the ground up. It contains tons of invaluable (to me) data which I don't want to lose or get corrupted. I'm always paranoid about ...
user avatar
2 votes
2 answers
201 views

DBCC consistency error in SQL Server

I ran DBCC CheckDB in our Production SQL Server 2014. It reported back with one consistency error: Failed:(-1073548784) Executing the query "DBCC CHECKDB(N'MYDB') WITH NO_INF..." failed ...
user avatar
0 votes
0 answers
7 views

How to deal with default and user-defined values in a table?

Suppose the software I'm building contains a table categories that is used to identify the nature of every record in a ledger. In the way the software works, there are a few records in this table that ...
user avatar
0 votes
2 answers
289 views

Check for spaces or zeros in all columns in all tables

I have multiple tables in an Oracle schema that have columns with incorrect values: Number columns where the value is zero, instead of null. Text columns where the value is a space, instead of null. ...
user avatar
  • 1,373
0 votes
0 answers
86 views

Ensure data integrity in relational DB?

Let's imagine I want to store in a relational database the following : A list of quizzes A list of questions associated with quizzes A list of users The answers to questions made from users. I could ...
user avatar
  • 123
1 vote
2 answers
396 views

How to ensure that two or more simultaneous requests to a MySQL table don't interfere

Suppose I have about a 1000 simultaneous requests to a table in my DB which consists of these statements (in order): SELECT INSERT or UPDATE (could be multiple) DELETE and I want to make sure that ...
user avatar
0 votes
1 answer
31 views

Database system checksumming to prevent network corruption not caught by TCP checksumming

Since TCP checksumming is not sufficient to detect/prevent all corruption, do modern database systems (e.g. SQL Server, MariaDB / MySQL, Oracle, etc.) use any additional checksumming of network ...
user avatar
  • 121
0 votes
1 answer
54 views

Database design - logging of same value when recorded in multiple tables

I need to record changes to a particular value, which is read as part of multiple events that are all separately tracked, and be able to view a log of all of the values later. I think an example will ...
user avatar
  • 293
1 vote
2 answers
342 views

What can cause what appears to be missing data in a single table [duplicate]

I have a situation where it appears as if I've lost data in a single table. I have a .NET Core API, with a logging middleware. This middleware takes the entire request and response and stores in the ...
user avatar
  • 11
2 votes
1 answer
4k views

Changing a column datatype from INT to BIGINT

I have a large, partitioned table with a field in that needs to upgraded from INT to BIGINT. My intention is to detach each partition, make the change and then re-attach, using something like this: ...
user avatar
1 vote
1 answer
38 views

Data Deplucation and relationship integrity [closed]

I apologize in advance as this question is likely to be more on the theoretical side, with (hopefully) a fair bit of discussion. This is the scenario: We have Salesforce and a multiple local ...
user avatar
4 votes
2 answers
108 views

A table with all fields NULLable with important, sensitive data

I have a requirement for a database table to hold sensitive patient information about their lifestyle (alcohol/ drug use / injecting etc). On screen the information is sectioned into tabs - "alcohol",...
user avatar
  • 143
0 votes
1 answer
43 views

What is the proper way to relate many to many?

I am building out a shipping app and database. Each shipment has three parties to it, shipper, receiver, and notification party and they all have the same attributes. I want to keep the attributes in ...
user avatar
1 vote
1 answer
46 views

Alternative to MS Acess for in-house user database [closed]

I am a working student at a bank and my current task is to create an in-house database for customer-specific lookups which often rely on complex underlying data aggregation from different sources. I ...
user avatar
23 votes
6 answers
3k views

How to prove the lack of implicit order in a database?

Recently I was explaining to colleagues the importance of having a column by which to sort data in a database table if it is necessary to do so, for example for chronologically-ordered data. This ...
user avatar
2 votes
0 answers
51 views

Can a database for teams, activites, markers and outputs be structured without data redundancy?

There are many TEAMS that engage in ACTIVITIES in a many-to-many relationship with TEAM_ACTIVITIES as a joining table. Each ACTIVITY has a set of MARKERS that are used to track each team's progress in ...
user avatar
1 vote
2 answers
59 views

Association Tables and Data Integrity

I'm developing a solution related to classroom materials. The hierarchy for classes is as follows: Course Type (e.g., Car Courses) Course (e.g., Fixing Cars, Driving Cars) Class (e.g., Fall ...
user avatar
1 vote
1 answer
174 views

How to prevent null foreign key during database normalization?

I'm rather new to databases and am having a hard time trying to figure out how to structure my database. This database contains students, classes and all their grades both overall and in categories (...
user avatar
0 votes
2 answers
148 views

Test/Verify Log-Shipping Backup

We recently suffered a power/connectivity outage at a data center housing two PostgreSQL 8.4 secondary servers, that remotely back up our two primaries via log-shipping replication. Backup power kept ...
user avatar
5 votes
3 answers
81 views

Constraining data based on related data in a different table. Should this be done on the database level or the application level?

I am working on a database that contains our customer information as well as information about our vendors. Part of that includes matching our customers with our vendors, the account numbers those ...
user avatar
0 votes
1 answer
179 views

Check and trigger constraints [closed]

Suppose a DB contains two tables A and B as given below: A --------------------- id SERIAL id_b INTEGER -- NOT NULL reference to B.id ts_update TIMESTAMP B --------------------------...
user avatar
  • 103
9 votes
1 answer
8k views

Cross-database foreign keys vs. database design best practice

MySQL allows to create cross-database foreign keys - but is it a good method of ensuring data integrity? Is it possible to give a general answer or is it project-dependent? What are the advantages and ...
user avatar
  • 193
1 vote
1 answer
39 views

Data integration name of best practices to avoid corruption, caused by becoming out of sync?

I have identified an issue in the design of a system and I am trying to find the right language to describe the cause of the issue so I can report it to managers, and get the issue fixed. The problem ...
user avatar
6 votes
3 answers
8k views

How to verify that postgres 9.3 db has no corruption?

I have a postgres 9.3 db running on ubuntu server. About a month ago we've had some problems with the hardware on the server which was resolved by our VPS hosting company. The problem was resolved ...
user avatar
  • 171
2 votes
1 answer
117 views

How to guarantee rows order

I'm trying to implement a methodology to check the integrity of a restored database. It goes as following: Create a snapshot of the database. Gather various metadata: On disk used space. Name of ...
user avatar
  • 193
18 votes
1 answer
23k views

How do I store phone numbers in PostgreSQL?

Let's assume I want to store phone numbers in a database. I may accept phone numbers from outside of the United States. How would I go about storing these phone numbers?
user avatar
  • 55.7k
1 vote
1 answer
137 views

How to Remedy Inconsistent MySQL Replication SYSDATE Value

When using master-master/master-slave replication in a MySQL environment, there's a possibility of creating inconsistent data when SQL statements use the sysdate instead of a hardcoded time in their ...
user avatar
2 votes
1 answer
1k views

How to conduct integrity test on SQL Server database backup file?

I was assigned to check the integrity of database backups from sql server every two weeks. I'm new in this field so I don't know how to correctly do this task. If someone(hacker) modified the data ...
user avatar
  • 23
1 vote
1 answer
83 views

Enforce Data Integrity without a direct Foreign Key relationship

I have a new table that will have a column named County_Codes. I would like to enforce that data to a list of County_Codes from another table named Institutions. However, due to this being a legacy, ...
user avatar
  • 2,928
8 votes
2 answers
2k views

What is the difference between integrity and consistency?

Whether I read about CAP or ACID, I see that consistency is referred to ensure the DB integrity constraints. So, I do not understand, why two terms are used to refer the same thing or there is a ...
user avatar
0 votes
1 answer
381 views

Check integrity between servers

I have three Database servers, running PostgreSQL 9.2: master01 (Own data center USA) slave01 (Own data center USA) slave02 (AWS Australia) Both slaves use streaming replication + ...
user avatar
4 votes
3 answers
256 views

Maintaining integrity between 3 tables when reference could be through or direct

Say we have the following 3 tables: tUsers (ID, name, countryID, regionID) ------ 1 | Joe | 1 | 1 2 | Sue | NULL | 2 3 | Bob | 2 | NULL 4 | Amy | NULL | NULL tCountries (ID, ...
user avatar
-1 votes
1 answer
491 views

-2147483648 value in integer type column despite entry mask [closed]

I have a desktop application with a textbox field that is a RadMaskedNumericInput with #6 masked value, meaning it can only have 6 digits. I store it in a database column of int type. When I examine ...
user avatar
14 votes
2 answers
10k views

How to add a check constraint in Postgres without locking the table?

I would like to add a check constraint to a very large table. Something like: ALTER TABLE "accounts" ADD CONSTRAINT "positive_balance" CHECK ("balance" >= 0); Unfortunately PostgreSQL 9.3 blocks ...
user avatar
4 votes
2 answers
442 views

MySQL: Overlay modifications on original data while indexing on a field that itself has modifications

I have a MySQL table of original_data that is important for historical/auditing purposes and should not ever be changed. I want to be able to mark up these data with modifications, e.g. to correct ...
user avatar
1 vote
1 answer
1k views

Diffrences between master and slave replication in mysql?

In a database named asterisk, there is a table named cdr which was partitioned on the time field. mysql> show create table cdr; Create Table: CREATE TABLE `cdr` ( `gid` bigint(20) NOT NULL ...
user avatar
  • 244
1 vote
1 answer
61 views

How can i enforce data integrity in this setup? [duplicate]

I have these tables: |-------------tblSchool -------------| | + ID | | + Name | | | | ...
user avatar
  • 153
0 votes
2 answers
96 views

Is unique index combined with a "not null" constraint equivalent to a PK constraint?

I have these legacy table definitions (from here): CREATE TABLE [dbo].[JobItems] ( [ItemId] UNIQUEIDENTIFIER NOT NULL, -- lots of other columns CONSTRAINT [PrimaryKey_GUID_HERE] ...
user avatar
  • 1,273
20 votes
1 answer
53k views

Use of log_bin_trust_function_creators in MySQL

What is the use of log_bin_trust_function_creators? Also, Is the value of the parameter dynamic? Will it change if I restart my MySQL service?
user avatar
  • 1,814
2 votes
0 answers
311 views

How to verify replication data integrity in SQL Server when using external replication engine

I am looking for a way to verify that data I am replicating between SQL Server databases is making it to the destination OK. Data is being replicated using an external replication engine, no built-in ...
user avatar
0 votes
1 answer
4k views

Best approach to compare specific columns of 2 tables in different databases?

I'm running an application who does have a shorten version of a table in the backend database. Both application- and backend-tables are having the same primary keys and some specific key columns. ...
user avatar
  • 23
2 votes
1 answer
1k views

CheckDB error 9003 SQL server 2014

Currently I am restoring SAN snapshots to another server nightly and running weekly checkdbs on that test server. The checkdb is throwing the following errors. The log scan number (105217:402:0) ...
user avatar
3 votes
1 answer
163 views

Is there any name for common sense / knowledge integrity constraints?

The Wikipedia page on data integrity lists 4 types of integrity constraints: entity integrity, referential integrity, domain integrity, and user-defined integrity. Is there a name for integrity ...
user avatar
2 votes
1 answer
180 views

Is there any point in specifying the precision and scale of a number for a foreign key?

I am running Oracle 11gR2 11.2.0.3. I have two tables: table1 contains a column named SUBJECT_ID, whose datatype is NUMBER(7,0) (numbers range from 13 to 48480) table2 contains a a column named ...
user avatar
8 votes
5 answers
25k views

When is it safe to disable InnoDB doublewrite buffering?

MySQL InnoDB allows us to disable doublewrite buffering by setting innodb_doublewrite = 0. Other databases doesn't seem to allow this setting to be tweaked. How could InnoDB still be able to maintain ...
user avatar
  • 431
2 votes
1 answer
2k views

How to verify a SSAS Backup and how to perform Integrity Checks in SSAS?

I have a procedure to backup SSAS Databases that is working successfully. I also delete the SSAS backup files that are older than 2 days. What I am looking forward to implement is: 1) How to verify ...
user avatar
1 vote
1 answer
68 views

Understanding how relationship sides works

So, I have this relations on my tables (see image) and I need to understand some things. I'll enumerate what I understand and what I need from yours is to say if I'm right or not and in case I'm not ...
user avatar
  • 1,690
0 votes
1 answer
564 views

Data exchange between 2 remote postgresql databases

As per subject I'm looking into a reliable way to synchronize 2 tables that have a same structure between two remote postgresql databases. I was thinking about dblink but an not really sure as in the ...
user avatar
13 votes
5 answers
7k views

eCommerce orders table. Save prices, or use an audit/history table?

Im designing my first eCommerce schema. I've been reading around the subject for a little while, and am a bit confused about the relationship between an order_line_item and a product A product can ...
user avatar
  • 489