I have an SSIS sequence container that extracts data from individual source database tables into a Stage layer. However, I need to know when a given calculation crashes in order for the given error to be written to the configuration table, which is provided by the yellow highlighted task in which the procedure is called. The problem is that if a given container falls due to an error, it does not flow through failure precendence constraint into the given task (LogPackageFailed). I tried setting FaiPackageOnFailure together with FailParentOnFailure to TRUE in the properties, but it didn't help. The proposed solution in the following links does not work either: - http://techblog.elish.net/2010/12/ssis-foreach-loop-container-continue-on.html - https://simonworth.wordpress.com/2009/11/11/ssis-event-handler-variables-propagate/ , which was addressed in a previous similar query.

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 would prefer something like a CHECK or UNIQUE constraint. The simpler the better. One row must be True, they cannot all be False (so the first row inserted would have to be True). The row will need to be updated, which means I have to wait to check constraints until updates are done, since all rows may be set False first and one row True afterwards. There is a FK between products.tax_rate_id and tax_rate.id, but it has nothing to do with the default or standard tax rate, which is user selectable to ease creating new products.. PostgreSQL 9.5 if it matters. ### Background The table is the tax rate. One of the tax rates is the default (standard since default is a Postgres command). When a new product is added, the standard tax rate is applied to the product. If there is no standard, the database must either do a guess or all kinds of unneeded checks. The simple solution, I thought, was to make sure there is a standard. By "default" above, I mean for the presentation layer (UI). There is a user option for changing the default tax rate. I either need to add extra checks to ensure the GUI/user does not try to set the tax_rate_id to NULL, or then just set a default tax rate.

#### The Schema Say I have the following:

table user
id bigint

table promo_codes
user_id bigint
code string
expires_at datetime NOT NULL
invalidated_at datetime

#### The Need Each user can have only one promo code that expires in the future AND is not deactivated, i.e expires_at > NOW() AND invalidated_at IS NULL #### What I tried 1. I tried adding a partial unique index:

CREATE UNIQUE INDEX only_one_active_promo
ON promo_codes (user_id)
WHERE expires_at > NOW() AND invalidated_at IS NULL

but got: > PG::InvalidObjectDefinition: ERROR: functions in index predicate must be marked IMMUTABLE That's because NOW(), CURRENT_TIMESTAMP and pretty much any other date/time function I'm aware of is STABLE at best but none of them is IMMUTABLE which is a requirement for creating an index. 2. I looked into adding a check constraint instead. It's okay if the check is only done during promo_code creation, but I didn't figure out how to do that. 3. Rules is another option that I considered but I have too little knowledge there and am not sure this is the right path actually. 4. At the time of writing this question I'm looking into exclusion constraints which is totally new to me. Using Postgres 16.3

In the example I pasted in the image, I have 5 Tasks. I don't think it's important what kind of Tasks I'm using--but I could be wrong, of course--because the main issue seems to be how the constraints (the arrows) flow. I have one flow that goes: Task 1, Task 2, Task 3, Task 4, Task 5. The other flow goes: Task 1, Task 2, Task 5. What I'm trying to do is: If Task 2 fails, skip Task 3 and 4 and go right to Task 5. If Task 2 succeeds, go ahead and run Tasks 3, 4, and 5. Unfortunately, when I debug the package, the package effectively hangs and never executes Task 5, regardless if the Task 2 Constraint is Success or Failure: Is there some design limitation where you cannot assign multiple constraints, pointing to the same Task? I'd rather not have boiler plate code, where Task 5 is created twice, one for each flow. Sure, in this example, it's only one Task. I could duplicate Task 5 and the package should run. But for scaling, it's woefully inefficient and a change management nightmare to create n number of duplicate Tasks, just to skip two Tasks. How can I make this work as the flow scales? Is it possible for a Task to have multiple constraints pointing at it and I'm just not setting something correctly? If not, what are my options? Could using a container help me out here? Thank you for your help in advance.

I am using PostgreSQL 16 upwards. I am currently trying to create an example for a relationship of type `G-|o-----|= 1 H. x CHAR(3), -- example for other attributes CONSTRAINT g_h_fk FOREIGN KEY (h, id) REFERENCES h (id, g) ); -- To table H, we add the foreign key reference constraint towards g. ALTER TABLE h ADD CONSTRAINT h_g_fk FOREIGN KEY (g) REFERENCES g (id);

Then I can insert data into the tables and read them back out as follows:

/* Insert into tables for G-|o-----|<-H relationship. */ -- Insert some rows into the table for entity type H. -- Not specifying g leave the references G as NULL for now. INSERT INTO h (y) VALUES ('AB'), ('CD'), ('EF'), ('GH'), ('IJ'); -- Insert into G and relate to H. We do this three times. WITH g_id AS (INSERT INTO g (h, x) VALUES (1, '123') RETURNING id) UPDATE h SET g = g_id.id FROM g_id WHERE h.id = 1; WITH g_id AS (INSERT INTO g (h, x) VALUES (3, '456') RETURNING id) UPDATE h SET g = g_id.id FROM g_id WHERE h.id = 3; WITH g_id AS (INSERT INTO g (h, x) VALUES (4, '789') RETURNING id) UPDATE h SET g = g_id.id FROM g_id WHERE h.id = 4; -- Link one H row to another G row. (We do this twice.) UPDATE h SET g = 3 WHERE id = 2; UPDATE h SET g = 3 WHERE id = 5; -- Combine the rows from G and H. SELECT g.id AS g_id, g.x, h.id AS h_id, h.y FROM h INNER JOIN g ON g.id = h.g; ``` This still requires the use of Common Table Expressions. However, by now, I am fairly confident that this is OK. Still, I am not 100% sure. I think both approaches do work and I could not find an error with either of them. But the two table method is probably more efficient and more elegant. @Akina was right.

EDIT: I did clean up the invalid data in column Y, and then I was able to add the FK to column X in one go. If anybody comes across this, it would still be interesting to know why it happened in the first place? --- This question is regarding MySQL 8.0. All tables are InnoDB. I am adding a column X to a table T1, and the column needs to have a foreign key constraint to a column in table T2. (The two columns have matching types and collations.) This is being done programatically by a script. When I run the script in our remote testing environment, the column is added successfully, but the constraint cannot be added. The command fails with a typical constraint error -- SQLSTATE: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails. However, it is not failing on column X; it is failing on a constraint for a different column, column Y. Column Y actually does have data that fails its own FK constraint, but I am surprised that this comes up when I attempt to add a constraint to column X. This did not occur when I tested the script against my own locally-running copy of the database. I even recreated the circumstances locally (putting invalid data into column Y), and the FK was still added to column X successfully. The most surprising part is that I am able to work around this in our remote test environment by removing column X and re-running the script. This time, it successfully creates the column and then adds the FK constraint to it. A difference between local and remote is that remote has quite a bit more data. Adding the column takes about 23 seconds in remote, vs. 1 second locally. In remote, when I run the script the second time without refreshing the db in between, and it successfully adds both the column and the constraint, it takes about 287 seconds. The sql_mode is identical between the local and remote. The part where it works the second time makes me suspect some sort of buffer that hasn't flushed in time for the constraint to be added the first time. I have tried updating the script to perform FLUSH TABLES between creating the column and adding the constraint, but that had no apparent impact. My main question is: why is it failing the first time and succeeding the second time. Any ideas? EDIT: the column is create with one statement and the FK is added with a separate statement. --- Note: I have been able to perform this test repeatedly in the remote environment because I'm running it on a clone, so each time I refresh it from the original data source and run the script again.

I am trying to draft a very simple data model for a multi-tenant database. So far I have only 3 tables: - **company**: pretty much my tenant - **account**: is part of one company and zero or more groups - **account_group**: same as account: one company and zero or more accounts Here is the SQL: CREATE TABLE company ( name TEXT NOT NULL, PRIMARY KEY (name) ); CREATE TABLE account ( email_address TEXT NOT NULL, company_name TEXT NOT NULL, PRIMARY KEY (email_address), FOREIGN KEY (company_name) REFERENCES company (name) ); CREATE TABLE account_group ( name TEXT NOT NULL, company_name TEXT NOT NULL, PRIMARY KEY (name, company_name), FOREIGN KEY (company_name) REFERENCES company (name) ); CREATE TABLE account_group_membership ( account_group_name TEXT NOT NULL, account_group_company_name TEXT NOT NULL, account_email_address TEXT NOT NULL, PRIMARY KEY (account_group_name, account_group_company_name, account_email_address), FOREIGN KEY (account_group_name, account_group_company_name) REFERENCES account_group (name, company_name), FOREIGN KEY (account_email_address) REFERENCES account (email_address) ); In this specific case, there is no constraint between the Account Company and Group Company. After doing some search, I found this question and tried to add the following to my account_group_membership table; CREATE TABLE account_group_membership ( account_group_name TEXT NOT NULL, account_group_company_name TEXT NOT NULL, account_email_address TEXT NOT NULL, account_company_name TEXT NOT NULL, PRIMARY KEY (account_group_name, account_group_company_name, account_email_address), FOREIGN KEY (account_group_name, account_group_company_name) REFERENCES account_group (name, company_name), FOREIGN KEY (account_email_address, account_company_name) REFERENCES account (email_address, company_name), CONSTRAINT same_company CHECK (account_group_company_name == account_company_name) ); Unfortunately this doesn't work since the company_name column of the account table is not unique. I cannot: 1. Make the column company_name unique since multiple accounts can be from the company. 2. Use email_address and company_name as primary key for an account. The email_address should be unique in the whole table. Also account will mostly be queried by email_address and from time to time by company_name. 3. See any other solution actually.... So is there any simple way to do this (simple as in easy to understand and maintain) ? Alternatively, does having this problem, mean that there is something wrong going on in the design ?

It seems like I have been bitten by the first bug described in the release notes of postgresql 9.3.4: http://www.postgresql.org/docs/devel/static/release-9-3-4.html I now have e.g. duplicate primary keys. What is the best approach on doing a loop of re-checking of all my constraints (pkeys, fkeys) -> fixing issues -> checking again, to make sure that my data is OK? **UPDATE** I decided to go with an approach on fixing the issue by deleting all constraints, and then recreating them using the following method http://blog.hagander.net/archives/131-Automatically-dropping-and-creating-constraints.html . However I'm currently stuck on an error message, trying to recreate a pkey: ERROR: failed to find parent tuple for heap-only tuple at (1192248,5) in table "fruits" CONTEXT: SQL statement "ALTER TABLE "pm"."fruits" ADD CONSTRAINT "fruits_pkey" PRIMARY KEY (id)" What does this mean, and how do I fix that one (I can live with deleting it, if that's an option)? Another question: Would my data actually be all good, if I just got rid of any duplicate rows by deleting them, and then did a pg_dump, and restored the database from that dump. It would rebuild the data structure - right?

I have a similar table to this one create table my_table ( id serial, attribute boolean, number integer ) Is there a way to have it force not null in column number IF attribute value is true? So if a record is saved with attribute value 'true' then number must be given a value. EDIT: After some digging I have tried this alter table my_table add constraint number_must_have_value CHECK (attribute = 't' and number IS NOT NULL) It throw constraint is violated by some row, but if I run: select * from my_table where attribute = 't' and number IS NOT NULL I retrive 0 rows. So my data seem to be OK? To force it anyway I try to use constraint number_must_have_value NOT VALID CHECK (attribute = 't' and number IS NOT NULL) But can't get the NOT VALID option to work. I get a syntax error. Is it not in the right place?

I applied a constraint but forgot its name. Now I want to drop it using ALTER TABLE [TABLENAME] DROP CONSTRAINT [CONSTRAINTNAME]. If there is a way to drop all constraint from a column that should work too. I cannot use a psql command.

In SQL Server I deploy a table with a UNIQUE constraint: CREATE TABLE [dbo].[Something] ( [IdentityId] UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID() -- Whatever else... ) and this makes SQL Server to create a unique index to back the constraint. That index will have name generated automatically and looking something like UQ__tmp_ms_x__1F778345BD where UQ means "unique" and all the rest is well, random stuff to me. If I have two unique constraints for the same table they just look like two random strings which is not very convenient. Is it possible to specify the name for the index which is created under the hood when a unique constraint is created?

I have a table like the following: create table my_table ( id int8 not null, id_A int8 not null, id_B int8 not null, id_C int8 null, constraint pk_my_table primary key (id), constraint u_constrainte unique (id_A, id_B, id_C) ); And I want (id_A, id_B, id_C) to be distinct in any situation. So the following two inserts must result in an error: INSERT INTO my_table VALUES (1, 1, 2, NULL); INSERT INTO my_table VALUES (2, 1, 2, NULL); But it doesn't behave as expected because according to the documentation, two NULL values are not compared to each other, so both inserts pass without error. How can I guarantee my unique constraint even if id_C can be NULL in this case? Actually, the real question is: can I guarantee this kind of uniqueness in "pure sql" or do I have to implement it on a higher level (java in my case)?

I am trying to make my all table columns in lowercase but the following error appears: SQL> UPDATE COUNTRY SET country_code = LOWER(country_code); UPDATE COUNTRY SET country_code = LOWER(country_code) * ERROR at line 1: ORA-02292: integrity constraint (OWNER.CITY_COUNTRY_FK) violated - child record found Here are my tables: create table country( country_code varchar2(2) PRIMARY KEY, country nvarchar2(57) NOT NULL ); create table city( city_code varchar2(3) PRIMARY KEY, country_code varchar2(3) CONSTRAINT city_country_fk references country(country_code) NOT NULL, city varchar2(24) NOT NULL ); Here is some sample data: INSERT INTO COUNTRY VALUES ('US', 'United States'); INSERT INTO COUNTRY VALUES ('SR', 'Suriname'); INSERT INTO city(city_code,country_code,city) VALUES('BAF','US','Westfield'); INSERT INTO city(city_code,country_code,city) VALUES('BBB','US','Benson'); INSERT INTO city(city_code,country_code,city) VALUES('OEM','SR','Paloemeu'); Thanks

I have two boolean columns in my table. One called IsUnlisted and one called IsPrivate. I only want to allow IsPrivate to be 1 (TRUE) if IsUnlisted is 1 (TRUE) as well using a check constraint. I am using MariaDB which shares most things with mysql.

At my place of work we are running into an issue, we have many parameters stored in a table like so. | ModelParameterValueID | ModelParameterID | ModelID | CurrentValue | Updated Date | | --- | --- | --- | --- | --- | | 187883 | 81 | 10849 | TEMPSTR | 26-Jul-24 | ModelParameterID = 81 corresponds to a string parameter in our database. Our technicians have some models that need the CurrentValue nulled, but only for that modelParameterID. We have a CHECK in place that makes sure CurrentValue is not Null, so I surmised to myself that if we could have a conditional CHECK that allowed CurrentValue to be Null only if ModelParameterID = 81 then this issues would not be there. So I contacted our database administrator and asked him if a change of this kind would be possible. He said > It is not good practice to have a constraint defined like that based on a particular value in another column. I asked him to explain but he would not. Could someone explain as to why something like this is frowned upon? And if it indeed is not good practice, then what would an alternative be?

Let's say I have multiple tables with a column foobar. And I wish to make to make all these columns reference foobar.id as a FOREIGN KEY. The problem is **I know there are violations in this schema from the party I am importing the data from.** But, I would rather not find them all iteratively. How can I find all of the violations so as to prepare a report of data-integrity issues for the third party?

### Context Suppose that you have a unique filtered index on a table. Said index exists [only to enforce a constraint](https://dba.stackexchange.com/questions/340941/are-unique-filtered-indexes-considered-an-antipattern-for-enforcing-constraints) . A classic example of such an index would be one making sure that a non-nullable bit column called Active only takes the value 1 at most once for each value of another column. Should such indexes have their filtering columns in the INCLUDES clause? The answers to [this question](https://dba.stackexchange.com/questions/224149/should-the-filtering-columns-always-be-in-the-keys-includes) suggests that **all** filtered indexes should include their filtering columns. However, I find that indexes that exist **only as constraints** blur the lines enough that they merit me asking this question specifically about them. To be very specific: What are the disadvantages of not putting the filtering columns in the INCLUDES clause of a unique filtered index that is intended for the sole purpose of enforcing constraints? ### Example Code

CREATE TABLE #OwnersAndMachines
(
	OwnersAndMachines INT IDENTITY (1, 1) PRIMARY KEY NOT NULL, 	
	OwnerId INT NOT NULL,
	MachineId INT NOT NULL,
	Active BIT NOT NULL,
);

CREATE UNIQUE NONCLUSTERED INDEX NobodyOwnsSameMachineActively ON #OwnersAndMachines
(
  MachineId
)
WHERE Active = 1

INSERT INTO #OwnersAndMachines
VALUES
(1, 200, 1),
(1, 185, 0),
(1, 150, 0),
(2, 300, 1),
(3, 100, 0),
(3, 185, 1)

SELECT * FROM #OwnersAndMachines;

/*
    Violates constraint
*/
INSERT INTO #OwnersAndMachines
VALUES
(4, 200, 1)

[dbfiddle](https://dbfiddle.uk/dzD6VWmv) .

I have a table, such as the following. Let's called it Fools. | FoolID | FoolValue | IsActiveFoolValue | |--------|-----------|-------------------| | 1 | Ultra Foolish | 0 | | 1 | Super Foolish | 1 | | 2 | Quite Foolish | 1 | A business rule is that for each FoolID, we can only have IsActiveFoolValue be 1 on one row. I want some sort of key or constraint to enforce this. However, to my knowledge, keys and constraints cannot do this. The only tool is a unique index like

CREATE UNIQUE NONCLUSTERED INDEX OnlyOneActiveFoolValuePerFoolId
ON Fools(FoolID)
WHERE IsActiveFoolValue = 1

is this considered an antipattern? Indexes aren't supposed to be constraints. They're supposed to be performance tools. Constraints should be enforced by keys, constraints, and maybe triggers. If it is considered an antipattern, what disadvantages does it bring and what alternatives exist? I'm assuming SQL Server 2019, because some features of unique filtered indexes did not exist in very old versions and I want to keep my options open for any modern alternatives.

I'm using PostgreSQL 9.3. I want to understand if I have an option to make a constraint unique across the entire table vs. unique across a subset of the table (i.e. by using 2 columns in the unique constraint, I restrict the uniqueness), which one is better for lookups? Consider this table where a unique alphanumeric code is allotted to each student of the class. CREATE TABLE sc_table ( name text NOT NULL, code text NOT NULL, class_id integer NOT NULL, CONSTRAINT class_fk FOREIGN KEY (class_id) REFERENCES class (id), CONSTRAINT sc_uniq UNIQUE (code) ); Currently the code is unique across the entire table. However the specification says that it is sufficient for the code to be unique across the class only. For my design requirements there's no restriction either way. However if I change the constraint to be unique for a given class only, how would it affect lookup by code? Or, in other words, which of the following combination of constraint & lookup is the best speed wise: -- 1. unique across entire table, lookup by value CONSTRAINT sc_uniq UNIQUE (code) SELECT * FROM sc_table WHERE code='alpha-2-beta' -- 2. unique across entire table, lookup by value & class CONSTRAINT sc_uniq UNIQUE (code) SELECT * FROM sc_table WHERE class_id=1 AND code='alpha-2-beta' -- 3. unique per class, lookup by value CONSTRAINT sc_uniq UNIQUE (code, class_id) SELECT * FROM sc_table WHERE code='alpha-2-beta' -- 4. unique per class, lookup by value & class CONSTRAINT sc_uniq UNIQUE (code, class_id) SELECT * FROM sc_table WHERE class_id=1 AND code='alpha-2-beta' **Question**: My understanding is that 2 is better than 1 & 4 is better than 3. But which one's better between 1-vs-3 & 2-vs-4? **Update** : Adding output of explain analyze. 3 is bad because there's no index for the lookup. 2 seems to be the best but the table is too small to conclude that. -- 1 "Index Scan using sc_uniq on sc_table (cost=0.15..8.17 rows=1 width=72) (actual time=0.041..0.044 rows=1 loops=1)" " Index Cond: (code = 'code1'::text)" "Total runtime: 0.096 ms" -- 2 "Index Scan using sc_uniq on sc_table (cost=0.15..8.17 rows=1 width=72) (actual time=0.024..0.026 rows=1 loops=1)" " Index Cond: (code = 'code1'::text)" " Filter: (class_id = 1)" "Total runtime: 0.056 ms" -- 3 "Bitmap Heap Scan on sc_table2 (cost=4.18..12.64 rows=4 width=72) (actual time=0.052..0.053 rows=1 loops=1)" " Recheck Cond: (code = 'code1'::text)" " -> Bitmap Index Scan on sc_uniq2 (cost=0.00..4.18 rows=4 width=0) (actual time=0.039..0.039 rows=1 loops=1)" " Index Cond: (code = 'code1'::text)" "Total runtime: 0.121 ms" -- 4 "Index Scan using sc_uniq2 on sc_table2 (cost=0.15..8.17 rows=1 width=72) (actual time=0.036..0.039 rows=1 loops=1)" " Index Cond: ((code = 'code1'::text) AND (class_id = 1))" "Total runtime: 0.093 ms"

Let's say I have table Account that contains username and password of users to login:-

account (
    account_id BIGINT PRIMARY KEY,
    username VARCHAR(30),
    password VARCHAR(30)
)

-- I have two more tables that stores their credentials in the above table:-

customer (
    delivery_address VARCHAR(100),
    account_id FOREIGN KEY,
    ..30 more customer columns
)

admin (
    sp_admin_code INT,
    account_id FOREIGN KEY,
    ..30 more admin columns
)

An admin cannot be a customer and use same username and password to login as a customer. My Questions:- 1. Is this the proper way to implement this kind of relationship? If not, what is? 2. Do I need to add some constraint in the primary key of the account table to denote that it's single entry can either be referenced by customer table or by admin table at once? 3. Now we also know that, an entry on account can only exist if there is an entry for it on either customer table or admin table and vice versa. So do we need to or if we want do add some cascade/constraint, how do we do it or how should we do it or if we should do it at all? sorry im confused.. thank you!