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.

116 questions
52
votes
2 answers

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?
user32234
32
votes
15 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
  • 37,076
  • 14
  • 93
  • 173
31
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
  • 311
  • 1
  • 3
  • 3
28
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 | foo@bar.com | t 1 |…
Kevin Burke
  • 675
  • 8
  • 16
24
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ᵀᴹ
  • 92,106
  • 13
  • 189
  • 284
23
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
  • 331
  • 1
  • 2
  • 6
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
14
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
  • 591
  • 4
  • 10
  • 26
13
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
  • 297
  • 3
  • 8
11
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
  • 26,899
  • 5
  • 60
  • 110
10
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
  • 101
  • 1
  • 1
  • 3
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
  • 1
  • 3
  • 10
8
votes
2 answers

Is having relationships between separate databases bad practice?

I am working with a client that has multiple databases. There are several master level databases that have relationships back to them from instance level databases (application specific DBs). The relationships from instance to master are integer…
DDiVita
  • 183
  • 4
7
votes
4 answers

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 sys... tables but being completely new to sybase, I…
René Nyffenegger
  • 3,623
  • 5
  • 29
  • 39
7
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
  • 185
  • 1
  • 6
1
2 3 4 5 6 7 8