Linked Questions

1
vote
1answer
2k views

How to design to avoid two-way reference between two tables [duplicate]

I am having a problem with foreign keys between two tables. I am trying to model a relationship between members of a club and their relatives. This is a straightforward many-to-one relationship, with ...
1
vote
1answer
248 views

How can I achieve a unique constraint with two fields? [duplicate]

I have a table with e.g. Name and IsDeleted fields. I want to add a row constraint so that only one Name value can have IsDeleted as 'false'. There can be many duplicate Name values, but they must all ...
0
votes
1answer
303 views

How to enforce that one row in a parent table MUST be associated with at least a row in a child table [duplicate]

I have a one to many relationship enforced via FK, classic example order and order_item, whereby a fk on the order_item to the order table enforces that one item belongs to one order, and one order ...
0
votes
1answer
268 views

How to store a Preferred Contact? [duplicate]

I'm creating a CRM type application for a client. They need to store information about Companies and information about Contacts within those companies. Each Company can have multiple Contacts ...
2
votes
1answer
61 views

How to constrain many-to-many to mark a single record as a “default”? [duplicate]

Suppose I have the following entities: customer(id, name) template(id, name) With a junction table: customer_template(id, customerId, templateId, is_default) A customer may have any number of ...
0
votes
1answer
35 views

MySQL set ID of primary child with constraint [duplicate]

I have two MySQL tables where the primary relationship between the two is one-to-many. I also need a one-to-one relationship in the parent_table with the ID for the primary record from the child_table....
0
votes
0answers
33 views

Can I create a constraint to a subset of data? [duplicate]

Here's a simplified view of my database |---------------| |-----------| | ORGANIZATION | | CONTACT | |---------------| |-----------| | OrgId | | ContactId | | OrgName ...
16
votes
2answers
9k views

Implementation of a many-to-many relationship with total participation constraints in SQL

How should I implement in SQL the scenario depicted in the following Entity-Relationship diagram? As it is shown, every A entity type occurrence must be related to at least one B counterpart (...
2
votes
1answer
25k views

How to insert data into tables which reference each other?(MySQL)

I'm a student and right now I have a problem that I couldn't solve by myself(after searching on the Internet). I'm not going to give my «homework» because I want to learn what I'm doing but I will ...
5
votes
3answers
1k views

Tables with FK's of each other

I'm working on a database that stores address information. (Related to this question I posted) I have 2 tables: CREATE TABLE [dbo].[Country] ( [Code] CHAR (2) NOT NULL, [Code3] ...
3
votes
1answer
398 views

Check constraint that exactly one in group has bit column set as true

Let's say you have a table of addresses like the following: dbo.Address ( AddressId INT PRIMARY KEY, CustomerId INT, IsPrimary BIT, ... more typical address fields ... ) I ...
4
votes
1answer
354 views

Circular dependency in schema or boolean with CHECK?

Let's say I have the following tables: create table repositories ( id serial primary key, name varchar(255) not null ); create table remotes ( id serial primary key, url varchar(255) ...
1
vote
1answer
161 views

Circular references for tracking currently active entity

I want to store active entity in one of my tables let's say I have 2 tables: Pirate Ship Named it like that to keep things interesting I want each pirate to own many ships but have only one active ...
1
vote
2answers
108 views

Enforce 1 or many relationship

In the er diagram below (not mine), A Class can have many instructors via a standard junction table. Is there a way of designing the Instructors/Classes tables using constraints to ensure that a ...
2
votes
1answer
110 views

How to go about modeling the main object of relationship?

I'm using MySQL and MyISAM tables. I have a bunch of objects that I want to group together 1 object belongs to 1 and only 1 group 1 group can have many objects 1 group has 1 required main object ...

15 30 50 per page