Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Apart that it sounds like a possibly poor design,</p> <p>You in essence want values spanning two columns in different tables, to be unique.</p> <p>In order to utilize DBs native capability to check for uniqueness, you can create a 3rd, helper column, which will contain a copy of all the values inside the wanted two columns. And that column will have uniqueness constraint. So for each new value added to one of your target columns, you need to add the same value to the helper column. In order for this to be an inner DB constraint, you can add this by a trigger. </p> <p>And again, needing to do the above, sounds like an evidence for a poor design.</p> <p>--</p> <p>Edit: Regarding your edit:</p> <p>You say " Alternate part numbers for each master part number must not themselves exist in the master parts table." This itself is a design decision, which you don't explain. I don't know enough about the domain of your problem, but: If you think of master and alternate parts, as totally different things, there is no reason why you may want "Alternate part numbers for each master part number must not themselves exist in the master parts table". Otherwise, you have a common notion of "parts" be it master or alternate. This means they need to be in the same table, and column.</p> <p>If the second is true, you need something like this:</p> <p>table "parts" columns:</p> <p>id - pk</p> <p>is_master - boolean (assuming a part can not be master and alternate at the same time)</p> <p>description - text</p> <p>This tables role is to list and describe the parts.</p> <p>Then you have several ways to denote which part is alternate to which. It depends on whether a part can be alternate to more than one part. And it sounds that anyway one master part can have several alternates. You can do it in the same table, or create another one.</p> <p>If same: add column: alternate_to, which will be null for master parts, and will have a foreign key into the id column of the same table.</p> <p>Otherwise create a table, say "alternatives" with: master_id, alternate_id both referencing with a foreign key to the parts table. (The first above assumes that a part cannot be alternate to more than one other part. If this is not true, the second will work anyway)</p>
    singulars
    1. This table or related slice is empty.
    1. POConstraint To Prevent Adding Value Which Exists In Another Table
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USshealtiel
    1. USshealtiel
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTDownMod
    1. COThanks, I edited my original post to be more clear. I think the idea of the function by @Saulius may be better, using a helper column which appears to mean listing the entire Part Number column twice, seems like a lot of overhead.
      singulars
      1. PO
      1. USReafidy
    2. COMasterparts and alternate parts are not the same at all. An alternate part number is specifically another part number for a specific master part number. See my second edit. Alternate part numbers will not have all the attributes of a master part. I did not add this information or explain it to start with because I wanted to keep the question simple. However as a lot of helpers have suspected a design flaw I have tried to explain the situation in more detail.
      singulars
      1. PO
      1. USReafidy
    3. COAlso, alternate part numbers must be unique. An alternate can not be an alternate to multiple master parts.
      singulars
      1. PO
      1. USReafidy
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload