Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [referential-integrity]

Facilities provided by a database management system to ensure consistency within the data.

Referential integrity is a feature provided by relational database management systems to constrain data so that it cannot hold invalid values. The normal mechanism employed for this is a foreign key, although other means can be used.

A foreign key refers one more columns on a table to a primary or unique identifier on another table. The key precludes the columns on that table from holding values that are not present at the source.

For example, one could have a 'currency' table that holds a list of three letter ISO currency codes. A financial transaction could have a 'currency_code' column that can contain an ISO code describing the currency of the transaction.

Placing a foreign key on the currency column in the transaction table that refers to the currency code table prevents that column from holding a value that is not present in the list of currencies. The foreign key constraint enforces referential integrity by preventing values that do not match the list of valid currencies from being entered into the transactions table.

128 questions
63
votes
2 answers

MATCH FULL vs MATCH SIMPLE in foreign key constraints

I've noticed the clauses MATCH SIMPLE and MATCH FULL in phpPgAdmin, but I can't find a description in the docs. The default is MATCH SIMPLE. How do they function?
user32234
41
votes
2 answers

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 plpgsql, but this seems like a sledgehammer. I…
theGtknerd
  • 511
  • 1
  • 4
  • 6
35
votes
16 answers

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 just want to be able to enforce the constraint that…
Jack Douglas
  • 40,517
  • 16
  • 106
  • 178
34
votes
3 answers

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 row (a specific user) can have an arbitrary number…
user2965107
  • 441
  • 1
  • 4
  • 3
33
votes
3 answers

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 | is_active 1 | alice@bar.com | t 1 …
Kevin Burke
  • 855
  • 3
  • 9
  • 19
28
votes
5 answers

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 VARCHAR(200) NOT NULL , PRIMARY KEY (BookID) ) ; CREATE…
ypercubeᵀᴹ
  • 99,450
  • 13
  • 217
  • 306
20
votes
3 answers

Cascade primary key update to all referencing foreign keys

Is it possible to update a primary key column value with cascading the update among all the foreign keys referencing it ? # EDIT 1: When I run followinq query select * from sys.foreign_keys where referenced_object_id=OBJECT_ID('myTable') , I…
mounaim
  • 659
  • 5
  • 13
  • 26
17
votes
2 answers

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 without an artist), the album also has many tracks,…
Raiden Freeman
  • 171
  • 1
  • 1
  • 3
15
votes
1 answer

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 "instance". For example, I might have as a part…
Snowball
  • 327
  • 3
  • 8
14
votes
3 answers

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 Enable foreign key creation. Levels: Global,…
Sybil
  • 2,578
  • 6
  • 34
  • 61
14
votes
2 answers

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 called ProducerDetails that would have: ProducerId…
Josh Darnell
  • 30,133
  • 5
  • 70
  • 124
11
votes
4 answers

DELETE statement conflicted with the REFERENCE constraint

My situation looks like this: Table STOCK_ARTICLES: ID *[PK]* OTHER_DB_ID ITEM_NAME Table LOCATION: ID *[PK]* LOCATION_NAME Table WORK_PLACE: ID *[PK]* WORKPLACE_NAME Table INVENTORY_ITEMS: ID *[PK]* ITEM_NAME STOCK_ARTICLE *[FK]* LOCATION…
derwodaso
  • 111
  • 1
  • 1
  • 3
9
votes
2 answers

Foreign key without referenced columns specification

I noticed in the SQL Server documentation that the list of referenced columns is not a required parameter of a foreign key constraint: ::= [ CONSTRAINT constraint_name ] { { PRIMARY KEY | UNIQUE } …
discrete
  • 199
  • 1
  • 2
8
votes
1 answer

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 arguments against foreign keys constraint talks about…
rahul286
  • 215
  • 2
  • 3
  • 10
8
votes
2 answers

"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 PRIMARY KEY, title text, author_id integer…
Steve D
  • 195
  • 1
  • 6
1
2 3
…
8 9