Tag Info

New answers tagged

1

The DB2 optimizer relies on foreign key constraints, which by default are enabled for optimization even though they may not be enforced. The optimizer may choose not to read the rows that should not exist according to the constraint. Try disabling query optimization on the RI constraint: ALTER TABLE xyz ALTER FOREIGN KEY fkname DISABLE QUERY OPTIMIZATION


1

Your Altered_Files design is absolutely fine. In fact I would award bonus points for separating the files per se from the hierarchy. @chillworld's suggestion is workable and very common but yours is better because of the NULL issues you mention. I can't think of any update which is better in one schema or the other. Change a single row - either the file ...


0

You could use the SET INTEGRITY statement to locate the offending rows. You can have the offending rows placed in a table T24_EXCEPTIONS that is a copy DDL-wise of T24. You can also check the parent T02 at the same time and use a similar tactic. SET INTEGRITY FOR T02,T24 IMMEDIATE CHECKED FOR EXCEPTION IN T02 USE T02_EXCEPTIONS, IN T42 USE T24_EXCEPTIONS ...


1

Well, if you need a way to check if all the foreign keys in your table are valid, this might help ( it just validates all foreign keys in your schema ) do $$ declare r record; BEGIN FOR r IN ( SELECT 'ALTER TABLE '|| tc.table_name||' VALIDATE CONSTRAINT '||tc.constraint_name||';' X FROM information_schema.table_constraints AS tc JOIN ...


1

No. You're referring to SQL Server Management Studio, presumably, and that behaviour doesn't exist there. There are other applications you can use that will leverage FKs in that way though.


6

Yes, you can do this with a COMPUTED column, It's a workaround really as the column has to be PERSISTED to be used for the foreign key constraint so it consumes storage space: CREATE TABLE dbo.OtherTable ( ID INT PRIMARY KEY IDENTITY(1,1), Blah VARCHAR(100), FieldName AS CAST('This' AS VARCHAR(100)) PERSISTED NOT NULL ) ; ALTER TABLE ...


4

A simple CHECK constraint works just fine: $ sqlite SQLite version 3.8.4.1 2014-03-11 15:27:36 ... sqlite> CREATE TABLE table_B( ...> user1, ...> user2, ...> [other stuff], ...> CHECK (user2 < user1) ...> ); sqlite> INSERT INTO table_B VALUES (1, 0); sqlite> INSERT INTO table_B VALUES (2, 3); Error: ...


0

If you are trying to make foreign key and your child table already have some data that does not match with your parent table, you will get this error message. For Example: CREATE TABLE MyParent(Job_id int PRIMARY KEY,Job_Name Varchar(50)); INSERT INTO MyParent Values(1,'CEO'); INSERT INTO MyParent Values(2,'Business Manager'); INSERT INTO MyParent ...


1

It's not eav - google "celko eav" Not sure about exact syntax, but CHECK (user1 > user2) or similar should work - it does in Firebird embedded. AIUI, SQLite doesn't support subqueries. This isn't one. The constraint will take care of your alice,bob-bob,alice issue. Paul...


0

You are missing column name of parent table to which your child refer, while creating child table, do like below create table department (dept_name varchar(20), building varchar(15), budget numeric(12,2), primary key (dept_name) ); create table course (course_id varchar(8), title varchar(50), dept_name ...



Top 50 recent answers are included