Gerry Snyder <[EMAIL PROTECTED]> wrote: > [T]here must be a good reason why the > constraints are not copied, but I have not seen an explanation. >
The statement is of the form: CREATE TABLE <name> AS <arbitrary-select-statement> Where would the constraints come from? If the arbitrary select statement were of the form: SELECT * FROM <table> then you might reasonably take the constraints from <table>. But what if the SELECT where a join? Or a compound select involving different tables? What if there are subqueries? Or what if you do something like this: CREATE TABLE one(a INTEGER CHECK(a>10)); INSERT INTO one VALUES(11); CREATE TABLE two AS SELECT a-5 FROM one; Do we try to copy the CHECK(a>10) constraint and thus cause the CREATE TABLE statement to fail? UNIQUE constraints are implemented by creating implicit indices. So if there are UNIQUE constraints on the source table, does that mean that some indices should be created automatically on the created table? Complications like this go and on and on. The easiest way to deal with all of these questions is to simply say that constraints are not copied at all. When you say that, the rules for dealing with constraints are simple to state and easy to understand. Any other constraint copying rule gets really complicated really fast. -- D. Richard Hipp <[EMAIL PROTECTED]> ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------