Facilities provided by a database management system to ensure consistency within the data.
3
votes
2answers
44 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
31 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
65 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 ...
1
vote
2answers
610 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 ...
2
votes
1answer
33 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
26 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
2answers
82 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 ...
3
votes
2answers
107 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 ...
2
votes
2answers
71 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
146 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
3answers
2k 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 ...
1
vote
1answer
61 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
345 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
147 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 ...
11
votes
5answers
488 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 ...
5
votes
2answers
144 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 ...
0
votes
1answer
55 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 ...
1
vote
2answers
549 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 ...
2
votes
1answer
77 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
3answers
190 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 ...
2
votes
2answers
710 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 ...
5
votes
2answers
189 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
332 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
468 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 ...
19
votes
13answers
2k 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 ...
