Questions tagged [data-integrity]
66 questions
26
votes
6 answers
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 proved somewhat difficult because they could simply…
anon
22
votes
1 answer
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
- 1,910
- 10
- 42
- 77
20
votes
2 answers
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 reads or writes until the constraint check has been…
Kevin Burke
- 855
- 3
- 9
- 19
19
votes
2 answers
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
- 65,432
- 50
- 254
- 507
15
votes
5 answers
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 been purchased. It has various details, but the most…
GWed
- 519
- 9
- 24
14
votes
2 answers
Modelling constraints on subset aggregates?
I am using PostgreSQL but I figure most of the top-end db's must have some similar capabilities, and moreover, that solutions for them may inspire solutions for me, so don't consider this PostgreSQL-specific.
I know I am not the first to try to…
Chris Travers
- 13,112
- 51
- 95
12
votes
5 answers
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 data integrity and ACID if we disable doublewrite…
Pacerier
- 491
- 2
- 7
- 24
11
votes
1 answer
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 with 1.401298E-45:
CREATE TABLE a
(
r1 REAL…
A-K
- 7,444
- 3
- 35
- 52
10
votes
2 answers
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 difference between the integrity and consistency?
I…
Little Alien
- 299
- 1
- 10
9
votes
1 answer
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 what threat does it pose?
Let's say there are…
Anna
- 193
- 1
- 1
- 5
6
votes
3 answers
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 fairly quickly and everything seemed to be working…
Rubinsh
- 171
- 1
- 1
- 5
5
votes
3 answers
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 vendors provide to us for our customers and what we…
Zachary Weber
- 185
- 6
4
votes
1 answer
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 one:
select count(id) from jobs where status in…
KayEss
- 145
- 1
- 5
4
votes
1 answer
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 mouse. "Experimental animal" is the specific animal…
Rumi P.
- 145
- 5
4
votes
1 answer
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:
ALTER TABLE testTable DETACH PARTITION…
ConanTheGerbil
- 1,303
- 5
- 31
- 50