Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [data-integrity]

The tag has no usage guidance.

1
vote
1answer
39 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 ...
21
votes
6answers
2k 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 ...
2
votes
0answers
45 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 ...
1
vote
2answers
50 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 ...
1
vote
1answer
80 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 (...
0
votes
2answers
89 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
3answers
74 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 ...
0
votes
1answer
83 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 --------------------------...
4
votes
1answer
2k 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 ...
1
vote
1answer
30 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 ...
4
votes
2answers
3k views

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 ...
2
votes
1answer
83 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 ...
10
votes
1answer
10k 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?
1
vote
1answer
85 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 ...
2
votes
1answer
739 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 ...
1
vote
1answer
76 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, ...
7
votes
2answers
833 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
1answer
238 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 + ...
0
votes
1answer
107 views

Complicated database design

We have a situation in a database design in our company. We are trying to figure out the best way to design the database to store transactional data. I need expert’s advice on the best relational ...
4
votes
3answers
252 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, ...
-1
votes
1answer
328 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 ...
7
votes
2answers
3k 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 ...
1
vote
1answer
192 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 ...
1
vote
1answer
774 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 ...
1
vote
1answer
58 views

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

I have these tables: |-------------tblSchool -------------| | + ID | | + Name | | | | ...
0
votes
1answer
74 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] ...
11
votes
1answer
20k 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?
2
votes
0answers
233 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 ...
0
votes
1answer
3k 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. ...
2
votes
1answer
982 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) ...
3
votes
1answer
150 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
votes
1answer
137 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 ...
6
votes
4answers
17k 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 ...
2
votes
1answer
1k 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 ...
1
vote
1answer
60 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 ...
0
votes
1answer
529 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 ...
13
votes
5answers
5k 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 ...
0
votes
1answer
112 views

Is it safe to use “NOT BLOCKING” option on HSQL backup?

The HSQL documentation has the following to say about the "NOT BLOCKING" option on backups: Hot backup is performed if NOT BLOCKING is specified. In this mode, the database can be used during ...
2
votes
1answer
1k views

PostgreSQL 9.2 tstzrange null/infinity CONSTRAINT CHECK

I'd like to limit a PostgreSQL 9.2 tstzrange to valid dates at both ends. No NULLs nor 'infinity'. Various revisions of this SQL isn't constraining '-/+infinity' input: CREATE TABLE bill ( id ...
0
votes
1answer
379 views

Restoring tab exports to a mysql database with integrity

I have a database with a lot of data for a client that's on a shared web host. Doing a plain mysqldump is causing the job to get killed on the server, so we were looking at backing up the tables ...
4
votes
1answer
2k views

Complex constraint across all data in a table

We have a table to record processing that occurs on a system and we need to ensure that only a single row has an 'in process' status. I want to ensure that the result of this is always either zero or ...
0
votes
1answer
473 views

how I can create check sum to validate no changes at int string pairs on table

Business case is. 1)Users print document on paper 2)users carry printed paper to Boss, Boss sing on paper 3)after that users going to administrator to digitally sign that paper. My task is to help ...
4
votes
1answer
201 views

How can I enforce data integrity in this model with a M:2 relationship?

This model describes a part of my database (somewhat simplified): A "Tumor model" describes an experiment a researcher does on animals. The "Animal line" is a breed of lab animals, e.g. a Black 6 ...
1
vote
1answer
943 views

Is SET INTEGRITY of any use after IMPORT statements, or just after LOAD statements?

I'm getting the feeling, both from documentation and empirically, that you can only put SET INTEGRITY to good use after a LOAD, not after an IMPORT. LOAD is mentioned for SET INTEGRITY, but not IMPORT....
1
vote
0answers
173 views

MySQL: Fixing Unique key constraint violation

I have a database with an unique key constraint. When I import this database from a dump created by mysqldump I get '14698-3' for key 'UNQ_CATALOGS EARCH_FULLTEXT_PRODUCT_ID_STORE_ID' But when I ...
2
votes
1answer
769 views

How do I ensure consistent MySQL backups?

How can we ensure MySQL backups are consistent when they are taken as full backups as well as incremental backups using binlogs? According to me, I record the COUNT(*) from random tables after ...
2
votes
1answer
187 views

Reformatting master MySQL server and temporary solution

We have MySQL master and replication servers both running on different machines. We have noticed some system files showing integrity issues in the master machine. Temporarily we would like to make the ...
13
votes
2answers
2k views

Modelling constraints on subset aggregates?

This is a cross-post from Stack Overflow. I figured I might get more interesting thoughts here. I am using PostgreSQL but I figure most of the top-end db's must have some similar capabilities, and ...
11
votes
1answer
1k views

What is the actual lowest possible positive REAL number

MSDN says that the range of REAL numbers is - 3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38. Apparently the true lower limit is much lower. The following script populates a REAL column ...
2
votes
1answer
1k views

How do you verify that all InnoDB tables are in good shape?

Without using a script, Is there a way to get mysql to check all the tables at once to make sure they're not corrupted?