SET CONSTRAINTS not schema-aware 
Author Message
 SET CONSTRAINTS not schema-aware

SET CONSTRAINTS doesn't allow you to schema-qualify a constraint name.
Here's an example:

create schema test;
create table test.foo (a int primary key);
create table test.bar (b int primary key, c int constraint myconstraint references test.foo);

-- This succeeds even though schema "test" is not in the search path:
set constraints myconstraint immediate;

-- This is what should work:
set constraints test.myconstraint immediate;
ERROR:  parser: parse error at or near "." at character 21

Comments?

--

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.***.com/



Fri, 28 Oct 2005 00:45:17 GMT
 SET CONSTRAINTS not schema-aware

On Sun, May 11, 2003 at 18:46:32 +0200,

Quote:
> SET CONSTRAINTS doesn't allow you to schema-qualify a constraint name.
> Here's an example:

> create schema test;
> create table test.foo (a int primary key);
> create table test.bar (b int primary key, c int constraint myconstraint references test.foo);

> -- This succeeds even though schema "test" is not in the search path:
> set constraints myconstraint immediate;

> -- This is what should work:
> set constraints test.myconstraint immediate;
> ERROR:  parser: parse error at or near "." at character 21

> Comments?

I am pretty sure I saw some comments in the discussion about sequence
naming that constraints are per table and giving them a schema name
makes no sense. The table they are for has the schema name in it.

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly



Fri, 28 Oct 2005 01:49:15 GMT
 SET CONSTRAINTS not schema-aware

Quote:

>> SET CONSTRAINTS doesn't allow you to schema-qualify a constraint name.
> I am pretty sure I saw some comments in the discussion about sequence
> naming that constraints are per table and giving them a schema name
> makes no sense. The table they are for has the schema name in it.

Yeah.  We had that discussion at some point during the 7.3 development
cycle, and concluded we liked table-local naming for constraints better
than the SQL spec's global constraint names.

SET CONSTRAINTS still does what it used to do, which is to alter the
behavior of all constraints with the given name.  We should probably
expand the syntax so that a particular table name can be mentioned.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Fri, 28 Oct 2005 04:02:34 GMT
 SET CONSTRAINTS not schema-aware

Quote:
Tom Lane writes:
> Yeah.  We had that discussion at some point during the 7.3 development
> cycle, and concluded we liked table-local naming for constraints better
> than the SQL spec's global constraint names.

Then why does pg_constraint store a schema OID?  The comments say it's for
SQL compatibility, so where else does it need to be compatible besides
this case?

--

---------------------------(end of broadcast)---------------------------



Fri, 28 Oct 2005 18:03:09 GMT
 SET CONSTRAINTS not schema-aware

Quote:

> Tom Lane writes:
>> Yeah.  We had that discussion at some point during the 7.3 development
>> cycle, and concluded we liked table-local naming for constraints better
>> than the SQL spec's global constraint names.
> Then why does pg_constraint store a schema OID?

I think we left it there for possible future use with assertions, which
are not tied to any particular table.

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org



Fri, 28 Oct 2005 21:45:01 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. Data-aware controls that be aware of nothing

2. Language and Character set aware string sorting on CLIENT

3. unknownin/out patch (was [HACKERS] PQescapeBytea is not multibyte aware)

4. Cannot create constraint referencing table in another schema.

5. DSNREXX is not libdef aware

6. PQescapeBytea is not multibyte aware

7. Copying constraints from one schema to another

8. Empty base table not aware of insert via connection object

9. Constraint validation text not showing when user not in sys admin role

10. SET NULL / SET NOT NULL

11. Compatibility set not set to 7?

12. SET NULL / SET NOT NULL


 
Powered by phpBB® Forum Software