All Questions

Filter by
Sorted by
Tagged with
1
vote
1answer
22 views

two-table-over <+> functional relationship: maintaining referential integrity

I have three tables, the last two having a many to one relationship with the first, written here in SQL: create table study ( id bigint primary key, name text ); create table treatment ( ...
1
vote
0answers
22 views

Redundant column to maintain data integrity

Consider the following example: Syllabus -id -course_id FOREIGN KEY (course_id) REFERENCES Course(id) SyllabusItem -id -syllabus_id FOREIGN KEY (syllabus_id) REFERENCES Syllabus(id) ...
13
votes
2answers
8k views

Constraint - one boolean row is true, all other rows false

I have a column: standard BOOLEAN NOT NULL I would like to enforce one row True, and all others False. The are no FK's or anything else depending on this constraint. I know I can accomplish it with ...
2
votes
2answers
586 views

Conditional table reference

I have a table tree: +------+-------+ | id | name | +------+-------+ | 0 | tree1 | | 1 | tree2 | | 2 | tree3 | | 3 | tree4 | +------+-------+ A table pen: +------+------+ | id | ...
2
votes
1answer
70 views

Create foreign key constraint on indirect data

I am trying to enforce relational integrity across some database design containing sensor data. The relevant parts of the database: CREATE TABLE logger ( id INTEGER PRIMARY KEY ); CREATE TABLE ...
3
votes
2answers
4k views

the REFERENCES privilege is only about creating a foreign key constraint? Practical use cases?

Today I learned about GRANT REFERENCES. In years of SQL admin and dev work I never heard about it and never had issues with it. quote from MySQL 5.7 Reference Manual / GRANT Syntax REFERENCES ...
1
vote
2answers
968 views

Deleting would-be orphans when creating FK in Postgres

I am working on a script that replicates schema and data. I first create all the structure except for foreign keys after reading it from the source's system tables, then copy the data, then create the ...
0
votes
1answer
296 views

Check integrity between servers

I have three Database servers, running PostgreSQL 9.2: master01 (Own data center USA) slave01 (Own data center USA) slave02 (AWS Australia) Both slaves use streaming replication + ...
0
votes
1answer
141 views

Simple way to list order of referential integrity in Postgres

Is there a clean, simple, and preferably prepackaged (ready function?) way to list the order of referential integrity in a Postgres database, rather than trying to read the system tables to find that ...
1
vote
1answer
595 views

Make a boolean column unique per group

I want to ensure consistency and make sure only one phone line is the default for a house. Schema: PhoneLine ID | isDefault | HouseID I want to add a trigger so that when you add/update a row, and ...
4
votes
1answer
6k views

Foreign key for array column [duplicate]

I have a table: CREATE TABLE methods ( method_id serial PRIMARY KEY, method_name varchar(100) ); I now want to create a table with the following columns: CREATE TABLE experiments ( ...
4
votes
3answers
2k views

SELECT + INSERT or INSERT alone

I have an application where several devices send data every second to my server. On the database I have a table with the devices and another table with the data sent every second. The first time a ...
1
vote
0answers
481 views

Constraint across multiple tables

I'm modeling a system where with vendors, each of which sells some overlapping subset of products available in the universe. Products (T-shirt) come in various variants (Small, Medium, Large) with ...
5
votes
2answers
3k views

“Merge” two rows in a Postgres table, with foreign keys

I am keeping a database of books I've read, using the following two tables in PostgreSQL: CREATE TABLE authors ( id SERIAL PRIMARY KEY, name text ); CREATE TABLE books ( id SERIAL ...
24
votes
3answers
5k views

Constraint to enforce “at least one” or “exactly one” in a database

Say we have users and each user can have multiple email addresses CREATE TABLE emails ( user_id integer, email_address text, is_active boolean ) Some sample rows user_id | email_address ...
3
votes
1answer
414 views

What is the correct way to map relationships between structurally similar comments tables and commentable entities?

I'm working on the usual comments table in postgres that has a one-to-many relationship with a commentable entity. In other words, one comment belongs to a post, and a post can have many comments. ...
5
votes
1answer
3k views

Delete rows while updating references

Suppose I have a person table with an id and some other columns. Other tables reference the person table using ON UPDATE CASCADE. for instance: CREATE TABLE person (id int PRIMARY KEY); CREATE TABLE ...
27
votes
3answers
18k 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 ...
4
votes
1answer
347 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 ...
9
votes
1answer
3k 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 for ...
29
votes
2answers
26k views

Differences between MATCH FULL, MATCH SIMPLE, and MATCH PARTIAL?

I've noticed a MATCH SIMPLE and MATCH FULL, but I don't understand what they do. I see default is MATCH SIMPLE; but, how do the other MATCH clauses to the FOREIGN KEY constraint function?
5
votes
3answers
5k 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 - ...