ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED
От | Simon Riggs |
---|---|
Тема | ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED |
Дата | |
Msg-id | 1292194280.2737.2311.camel@ebony обсуждение исходный текст |
Ответы |
Re: ALTER TABLE ... ADD FOREIGN KEY ... NOT ENFORCED
|
Список | pgsql-hackers |
The new SQL Standard (SQL:2011) contains this: "Table constraints are either enforced or not enforced. Domain constraints and assertions are always enforced.", 4.17.2 The SQL Standard allows you to turn the checking on and off for CHECK constraints, UNIQUE constraints and FOREIGN KEYS. Which of those make sense for us, if any? The ability to create FKs without checking all the data has been frequently requested to me over many years. OTOH, I can't really see any point in turning on/off all of the other aspects mentioned by the SQL Standard, especially indexes. It's lots of work and seems likely to end with poorer data quality. And the obvious thing is if you don't want a CHECK constraint, just drop it... My proposal is that we add a short and simple clause NOT ENFORCED onto the ADD constraint syntax. So we have ALTER TABLE foo ADD FOREIGN KEY .... NOT ENFORCED; The "enforced" state is not persisted - once added the FK is checked every time. So there is no additional column on pg_constraint. The benefit here is that we implement a capability that allows skipping very long running SQL statements when required, and doesn't require too much code. It has been discussed before on hackers, but that was before it was part of the SQL Standard. Oracle has had this for years and it is popular feature. We can expect other RDBMS to implement this feature, now it is part of the standard. If you want more than my good-bits-only proposal, it really isn't going to happen for 9.1, and seems pretty pointless anyway. Very short hack to implement this attached for discussion. No tests, not even a compile - just showing how quick a patch this can be. Thoughts? Alternative syntax? -- Simon Riggs http://www.2ndQuadrant.com/books/ PostgreSQL Development, 24x7 Support, Training and Services
Вложения
В списке pgsql-hackers по дате отправления: