Questions tagged [data-integrity]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
0 answers
22 views

Data Loss and data redeundancy when forced failed over to secondary replica on DR Data Center in SQL Server 2016

We are using SQL Server 2016 Enterprise edition and planning to have 2 datacenters primary and the remote Disaster recovery(DR). We have 2 replicas (primary replica and secondary replica) in the ...
Manoj Jeganathan's user avatar
0 votes
0 answers
12 views

Database design: Which rule requires database integrity with respect to two tables having the same attribute?

I am working with an RDBMS (mariadb) in which at least two tables (table1 and table2) share the same attribute, let it be "name". "name" is the primary key in table1, and table2 is ...
user214289's user avatar
2 votes
0 answers
212 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 ...
Yeldur's user avatar
  • 21
0 votes
1 answer
59 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 ...
SIRCAVEC's user avatar
  • 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 ...
joyoy's user avatar
  • 13
2 votes
2 answers
687 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 ...
user16508174's user avatar
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 ...
sqllover2020's user avatar
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. ...
User1974's user avatar
  • 1,457
0 votes
0 answers
188 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 ...
nowox's user avatar
  • 123
1 vote
2 answers
1k 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 ...
FunnelScr's user avatar
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 ...
g491's user avatar
  • 121
0 votes
1 answer
81 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 ...
elmer007's user avatar
  • 293
1 vote
2 answers
1k 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 ...
Dasch's user avatar
  • 11
4 votes
1 answer
8k 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: ...
ConanTheGerbil's user avatar
1 vote
1 answer
42 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 ...
TheDemonLord's user avatar
4 votes
2 answers
139 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",...
Scott's user avatar
  • 143
0 votes
1 answer
47 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 ...
Nick Seferos's user avatar
1 vote
1 answer
50 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 ...
huhnmonster's user avatar
26 votes
6 answers
4k 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
53 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 ...
Toby 1 Kenobi's user avatar
1 vote
2 answers
75 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 ...
Eaglegust's user avatar
1 vote
1 answer
227 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 (...
ryanmattscott's user avatar
0 votes
2 answers
173 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 ...
Clovis_Sangrail's user avatar
5 votes
3 answers
86 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 ...
Zachary Weber's user avatar
1 vote
1 answer
266 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 --------------------------...
migdsb's user avatar
  • 113
9 votes
1 answer
11k 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 ...
Anna's user avatar
  • 193
1 vote
1 answer
46 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 ...
user802599's user avatar
6 votes
3 answers
11k 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 ...
Rubinsh's user avatar
  • 171
2 votes
1 answer
226 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 ...
Spack's user avatar
  • 193
19 votes
2 answers
28k 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?
Evan Carroll's user avatar
  • 63.1k
1 vote
1 answer
183 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 ...
Josh Bonello's 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 ...
Ricj's user avatar
  • 23
1 vote
1 answer
101 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, ...
DForck42's user avatar
  • 3,048
9 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 ...
Little Alien's user avatar
0 votes
1 answer
407 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 + ...
Patrick B.'s user avatar
4 votes
3 answers
315 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, ...
David Starkey's user avatar
-1 votes
1 answer
585 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 ...
Naila Akbar's user avatar
17 votes
2 answers
15k 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 ...
Kevin Burke's user avatar
4 votes
2 answers
587 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 ...
Chris Martin's 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 ...
shgnInc's user avatar
  • 244
1 vote
1 answer
62 views

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

I have these tables: |-------------tblSchool -------------| | + ID | | + Name | | | | ...
g_b's user avatar
  • 153
0 votes
2 answers
103 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] ...
sharptooth's user avatar
  • 1,301
22 votes
1 answer
74k 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?
tesla747's user avatar
  • 1,896
2 votes
0 answers
336 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 ...
santiagovm's user avatar
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. ...
heuri's user avatar
  • 23
2 votes
1 answer
2k 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) ...
b_murray14's user avatar
3 votes
1 answer
182 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 ...
Franck Dernoncourt's user avatar
2 votes
1 answer
219 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 ...
Franck Dernoncourt's user avatar
11 votes
5 answers
30k 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 ...
Pacerier's user avatar
  • 471
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 ...
Marcello Miorelli's user avatar