Questions tagged [data-integrity]
The data-integrity tag has no usage guidance.
66
questions
0
votes
0
answers
9
views
Data documentation and planning
I’m trying to find information about data attribute documentation but not by entity. We have 5 or 6 separate products we are simultaneously building that all serve a part of related customer need.
...
0
votes
0
answers
20
views
Enforce dynamic value correctly
I am working on quiz builder app with advanced features such as enforcing constraints on quizzes and/or quiz items. Here is the snippet of the ERD:
violated_on represents the timestamp when this ...
- 1
2
votes
0
answers
113
views
Setting up Database Integrity Checks
I'm relatively new to SQL and, unfortunately, am definitely not at the level of a DBA - However, it's become my job to look into some SQL issues...
There's a Database Integrity Check set to run for ...
- 21
0
votes
1
answer
37
views
Best practices for tracking ingested data sources
I am in the process of creating an ingestion pipeline wherein there exists a step of periodically reading new .csv files and storing them into a postgres database. This step is functioning, however it ...
- 101
1
vote
0
answers
39
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 ...
- 13
2
votes
2
answers
447
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 ...
- 277
3
votes
2
answers
1k
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 ...
- 83
0
votes
2
answers
1k
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.
...
- 1,437
0
votes
0
answers
149
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 ...
- 123
1
vote
2
answers
972
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 ...
- 13
0
votes
1
answer
34
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 ...
- 121
0
votes
1
answer
79
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 ...
- 293
1
vote
2
answers
975
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 ...
- 11
4
votes
1
answer
7k
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:
...
- 1,065
1
vote
1
answer
40
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 ...
- 193
4
votes
2
answers
136
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",...
- 143
0
votes
1
answer
46
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 ...
1
vote
1
answer
48
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 ...
- 113
24
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 ...
anon
2
votes
0
answers
52
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 ...
- 121
1
vote
2
answers
68
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 ...
- 11
1
vote
1
answer
216
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 (...
- 155
0
votes
2
answers
169
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 ...
5
votes
3
answers
85
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 ...
- 185
1
vote
1
answer
250
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
--------------------------...
- 113
9
votes
1
answer
10k
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 ...
- 193
1
vote
1
answer
43
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 ...
- 443
6
votes
3
answers
10k
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 ...
- 171
2
votes
1
answer
198
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 ...
- 193
18
votes
2
answers
27k
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?
- 61.5k
1
vote
1
answer
177
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 ...
- 600
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 ...
- 23
1
vote
1
answer
100
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, ...
- 3,028
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 ...
0
votes
1
answer
400
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 + ...
- 181
4
votes
3
answers
300
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, ...
- 195
-1
votes
1
answer
564
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 ...
- 175
15
votes
2
answers
13k
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 ...
- 785
4
votes
2
answers
540
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 ...
- 141
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 ...
- 244
1
vote
1
answer
62
views
How can i enforce data integrity in this setup? [duplicate]
I have these tables:
|-------------tblSchool -------------|
| + ID |
| + Name |
| |
| ...
- 153
0
votes
2
answers
100
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] ...
- 1,301
22
votes
1
answer
69k
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?
- 1,866
2
votes
0
answers
325
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 ...
- 21
0
votes
1
answer
5k
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.
...
- 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) ...
- 21
3
votes
1
answer
178
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 ...
- 2,013
2
votes
1
answer
204
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 ...
- 2,013
10
votes
5
answers
29k
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 ...
- 461
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 ...
- 15.8k