Questions tagged [deferrable-constraint]
9 questions
23
votes
4 answers
Deferrable check constraint in PostgreSQL
I have function checking mandatory participation as follows:
CREATE FUNCTION member_in_has_address()
RETURNS BOOLEAN AS $$
BEGIN
RETURN EXISTS (SELECT *
FROM address a, member_details b
WHERE b.member_id = a.member_id);
END;
$$ …
Radovan Luptak
- 281
- 1
- 2
- 8
6
votes
1 answer
deferrable initially deferred in postgresql
I have a cyclic foreign keys on 2 tables, so i use deferrable initially deferred as below:
uni=# create table vorlesungen (vnr integer primary key, gelesenvon integer);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index…
hooch
- 1,135
- 1
- 16
- 31
6
votes
1 answer
Force Hibernate to issue DELETEs prior to INSERTs to avoid unique constraint violations?
Background: http://jeffkemponoracle.com/2011/03/11/handling-unique-constraint-violations-by-hibernate
Our table is:
BOND_PAYMENTS (BOND_PAYMENT_ID, BOND_NUMBER, PAYMENT_ID)
There is a Primary key constraint on BOND_PAYMENT_ID, and a Unique…
Jeffrey Kemp
- 59,135
- 14
- 106
- 158
1
vote
2 answers
Transaction with multiple updates and unique index
SQL Server has table, where one record is FK to the same table (parent record or 'null' if it's first record):
CREATE TABLE [dbo].[RegisteredDevice]
(
[Id] [int] IDENTITY(1,1) NOT NULL,
[PreviousDeviceId] [int] NULL,
[Position] [int]…
Oleg Sh
- 8,496
- 17
- 89
- 159
1
vote
1 answer
Why is my regular foreign key constraint not deferrable?
I have 2 simple tables with a foreign key constraint:
CREATE TABLE a(i integer);
ALTER TABLE a ADD CONSTRAINT pkey_a PRIMARY KEY (i);
CREATE TABLE b(j integer);
ALTER TABLE b add CONSTRAINT fkey_ij FOREIGN KEY (j) REFERENCES a (i);
I want to defer…
Nicolas C
- 944
- 1
- 10
- 22
1
vote
1 answer
In PostgreSQL 10, why is a foreign key violation error not caught by pgplsql exception handling for deferrable constraints?
When calling a SQL function that performs an insert, from a PGPLSQL function with an EXCEPTION WHEN OTHERS block, the exception is raised rather than caught if the violated foreign key constraint is deferrable.
I'm using Amazon Aurora PostgreSQL…
enjayaitch
- 137
- 1
- 9
1
vote
2 answers
How to defer foreign key constraint in Postgres
I tried to set a foreign key constraint deferrable so that it won't be checked when I insert into a lookup/pivot table until end of transaction. However, it woks in psql shell but its just not working in the code. same as in the psql shell, I start…
shangsunset
- 1,585
- 4
- 22
- 38
1
vote
2 answers
With Rails Minitest, how do tests pass but fail when retested?
I'm using transactional fixtures in Minitest, my tests run successfully (and pass) when I first run them:
rake test test/models/number_test.rb
Run options: --seed 31462
# Running:
..
Finished in 0.271344s, 7.3707 runs/s, 7.3707 assertions/s.
2 runs,…
Turgs
- 1,729
- 1
- 20
- 49
0
votes
2 answers
What is a smart way to change all PKs and FKs to DEFERRABLE in Oracle?
I'm managing quite an intricate Oracle relational database (~50 tables) with many integrity constraints in place. All PKs and FKs are NOT DEFERRABLE, I would like to change them to DEFERRABLE INITIALLY IMMEDIATE, but I understand my only chance is…
Luigi Cortese
- 10,841
- 6
- 37
- 48