Facilities provided by a database management system to ensure consistency within the data.
1
vote
1answer
27 views
Resolving Cyclical Reference in Referential Relationships
I'm designing a small SQL Server Compact Edition database and am attempting to enforce referential integrity. The database is a simple backend for a small desktop application that functions as an ...
3
votes
1answer
47 views
Foreign key constraint on array member?
Suppose I have a table containing job roles:
CREATE TABLE roles
(
"role" character varying(80) NOT NULL,
CONSTRAINT "role" PRIMARY KEY (role)
);
Suppose I further have a table, users, and each ...
2
votes
2answers
57 views
SQL Server - Recursive “find all FK connections for ID through the entire DB” query
I'm dealing with a DB with hundreds of tables, with interconnecting FK references producing potentially thousands of different permutations for how for example table A and table K can join together. ...
3
votes
1answer
46 views
Non-overlapping rectangles constrained to a boundary
I am trying to model placement of parts on a circuit board. Without any
meaningful constraints, my basic schema looks like this:
create table part (
part_id bigserial primary key,
name text ...
2
votes
1answer
85 views
Enforcing constraints “two tables away”
I ran into some trouble modeling an electrical schematic in SQL.
The structure I'd like to capture is
part ←────────── pin
↑ ↑
part_inst ←───── pin_inst
where "inst" is short ...
2
votes
0answers
51 views
Maintaining Referential Integrity with a Filestream enabled database
We have a production database which is filestream enabled and has grown, within a year, to about 20Gb as it stores documents. On many occasions we need to bring the live data back to UAT or Dev ...
1
vote
1answer
61 views
Better way to manage data integrity, without having too many composite FK's
I have following tables:
class_table (class_id = Auto Incremented Primary Key (AIPK) of this table)
user_table (class_id, user_id (AIPK of this table))
course_table(class_id, course_id (AIPK of this ...
2
votes
1answer
146 views
Relationships between subtypes of an entity
I have a very big (hundred of milions rows) table which represent an entity (event) having many subtypes (different kind of recorded events).
Each row is identified by an id (obj_id) and has an ...
0
votes
0answers
90 views
Self Referencing Table, Child and Master, Update Cascade Through Stored Procedure in SQL Server
Basically I am expanding an existing system that has every template as its own template, however there is becoming a lot of overlap (same template exists in multiple categories and has a new record ...
0
votes
1answer
46 views
SQL - Referential integrity
seems like my problem pretty much describes GC (Garbage Collector) but I don't want to make a procedure to run every X time. so this is my question:
I have three tables or more, refer to those as ...
2
votes
1answer
76 views
How can I change Referential Integrity from a Unique Key Constraint to a Primary Key?
(without dropping foreign keys)
I have migrated a SQL Server CE database to SQL Server 2012, however, referential integrity was enforced via unique key constraints on the surrogate INT identity ...
2
votes
1answer
683 views
If foreign keys/cascade deletes are bad, why use a database-server with that feature?
I noticed wordpress/rails etc do not use foreign keys constraint or cascade deletes features from database. Instead they handle this in PHP/Ruby/scripting level!
I have read this and this. Most ...
0
votes
1answer
235 views
MySQL cannot create foreign key, errors 1005, 150
I'm trying to add a foreign key to a MySQL InnoDB table, linking a BIGINT with a table within the same database. I'm using MySQL Workbench to do this. It generates a script:
ALTER TABLE ...
4
votes
2answers
61 views
Table design when one data is dependant of another and the second one has repeated values
I have a products, and subproducts.
Like:
Product Identifier - Product Name - Subproduct Identifier - SubProduct Name
1 ProA 1 SubProA1
...
0
votes
2answers
51 views
Enforce indirect relationship
Given the following structure
There's a way to enforce that Table1.id3 only can seted to values in Table4.id3 where Table4.id2 = Table1.id2? Initially I thought in a CHECK constraint, but subqueries ...
1
vote
2answers
123 views
Foreign Key constraint on fixed value field - Ever appropriate?
I have a short (15 rows) look-up table that lists the valid values for several columns in the database.
I don't think it can be considered a One-True-Lookup-Table, it is about a single definite ...
2
votes
1answer
42 views
How to read this output from MySQL?
Query that is being ran:
INSERT INTO `log_url` (`url_id`, `visitor_id`, `visit_time`) VALUES (?, ?, ?)
Error that is being thrown:
SQLSTATE[23000]: Integrity constraint violation: 1062 ...
0
votes
1answer
46 views
Check for records that break a foreign constraint (created with foreign_key_checks = 0)?
Is there an easy way to check for any records in a table that breaks any constraint?
The constraint was created on a populated table using "foreign_key_checks=0".
5
votes
1answer
998 views
Many to Many and Weak Entities
I have an entity that cannot exist without being defined by another, and I want this entity to participate in a many to many relationship.
Example: An artist has an album (the album cannot exist ...
5
votes
2answers
102 views
Relationship that are optionally more specific
Forgive my title, I couldn't think of anything that accurately describes what I'm talking about.
I currently have the following relationship.
CREATE TABLE Events
(
ID INT IDENTITY(1,1) NOT ...
2
votes
2answers
76 views
Referential Integrity - Re-using table
This is a simplified example of a design issue that I am facing: I have 3 tables: Car, Ship and Bicycle. I need to add an "activity logging" table that records user actions such as deletion and user ...
0
votes
2answers
256 views
How to model a 1..1 Composition Relationship
We have a couple of entities/relationships of the following kind:
Entity Event has a TimePoint
The TimePoint contains time information about the event. It belongs to that event and cannot be used by ...
3
votes
2answers
151 views
Finding violations of the symmetry constraint
Suppose I have a table Friends with columns Friend1ID, Friend2ID. I chose to represent each friendship with two records, say (John, Jeff) and (Jeff, John). Thus, each pair of friends should show up ...
1
vote
1answer
152 views
Issues on defining a PK on a nvarchar column and indexing the FK referring this PK
Someone from the team, has defined two reference tables with a few records in each, and has defined the PK of these tables on a column of type nvarchar(255).
The value exists in this columns are ...
4
votes
3answers
680 views
Lookup table for a one-to-many relationship
I have a tickets table with an id that I need to associate to a lookup table where the counterpart of that data is another id that is controlled from an outside source.
tickets
- id
- sutff
lookup
- ...
2
votes
1answer
182 views
Representation of circular references constraint
We have a database where we store commands and properties for devices that may be controlled via a network.
For instance, "Concrete Device" is an instance of some DeviceType, which in turn contains ...
5
votes
2answers
198 views
Help with good RDBMS transactional schema design that mimicks a sports league
I need to design a data model for a typical RDBMS that will mimic the structure of your typical sports league. Architecturally my requirements are:
Well normalized
Transactional
Application ...
1
vote
2answers
1k views
set integrity not enforced in DB2
I am a bit confused with the SET INTEGRITY concept in DB2, why we can OFF the set integrity check to put the table in PENDING state ? does that mean that we can neglect the constraint definitions on ...
0
votes
1answer
62 views
Unknown relationship
I have a problem with a database design:
I have an EReport. This EReport will have a QAPCategory.
User must select a QAPCategory from an set of QAPCategory.
To know which QAPCategory can be ...
12
votes
5answers
1k views
Are there DBMS that allow a Foreign Key that References a View (and not only base tables)?
Inspired by a Django modeling question: Database Modeling with multiple many-to-many relations in Django. The db-design is something like:
CREATE TABLE Book
( BookID INT NOT NULL
, BookTitle ...
1
vote
2answers
1k views
Truncate parent table in Oracle when child table is empty
Suppose I have a parent table parent referenced by a child table child. The table parent is populated but child is not. Attempting to truncate parent results in
ORA-02449: unique/primary keys in ...
3
votes
1answer
95 views
Relationship with a default value
Firstly, I know my title is somewhat misleading, but I couldn't think of a better title.
Here is my current relationship
CREATE TABLE Events
(
ID INT IDENTITY(1,1) NOT NULL,
Name ...
2
votes
2answers
919 views
Do I need a separate Id column for this “mapping” table?
I have a table of Producers and a table of Products, both of which are of the form:
Id - int, Primary key
Name - nvarchar
A Producer can carry multiple Products, so I was going to create a table ...
2
votes
3answers
258 views
How to maintain referential integrity of an object graph when a node is deleted
In a relational database, if a node in an object graph is deleted, how can the object graph's referential integrity be maintained?
For example:
A product is deleted, how is the invoice containing ...
5
votes
2answers
211 views
How should an object that can reference an object of multiple types be modeled in a relational schema?
Apologies for the obscure question, it may make more sense with a concrete example:
In my application, I may create a portfolio that contains a set of projects. However, I may also add a ...
2
votes
3answers
390 views
SQL Server 2008r2 replication from multiple sources
I am currently rearchitecting an application so that each of seven businesses will have there own "live" SQL Server 2008 database and at scheduled times during the data the data from all seven ...
2
votes
2answers
690 views
How to select a subset of data from a large ER model?
I have a quite large ER model in my database and lots of data. Now I would like to take a subset of this data into a different database for testing purposes. The problem is how to get a subset from ...
20
votes
13answers
3k views
How to implement a 'default' flag that can only be set on a single row
For example, with a table similar to this:
create table foo(bar int identity, chk char(1) check (chk in('Y', 'N')));
It doesn't matter if the flag is implemented as a char(1), a bit or whatever. I ...
4
votes
3answers
4k views
How do I identify tables that have a foreign key to a specific table in Sybase?
I am looking for (preferably) an SQL statement that selects the table/and column names for any table with a foreign key to a given table in Sybase. I think it should be somehow possible with the ...
