asked
Column list in column constraint - is it legal?
Consider the following query to create a table with a single column c1, which is the primary key: 1) CREATE TABLE t1(c1 INT CONSTRAINT PK_t1 PRIMARY KEY); I discovered that you can specify a column list, e.g.: 2) CREATE TABLE t1(c1 INT CONSTRAINT PK_t1 PRIMARY KEY(c1)); You can even do this: 3) CREATE TABLE t1(c1 INT CONSTRAINT PK_t1 PRIMARY KEY(c1, c2), c2 INT); or this: 4) CREATE TABLE t1(c1 INT CONSTRAINT PK_t1 PRIMARY KEY(c2), c2 INT); In my opinion, (2), (3) and (4) are not allowed according to the published syntax; there is nothing that indicates that a list of columns may be specified. This is different from the behaviour of the CHECK constraint. For example: CREATE TABLE t1( c1 INT CONSTRAINT PK_t1 PRIMARY KEY , c2 INT CONSTRAINT CHK_c2 CHECK(c1 > 0)) generates "Column CHECK constraint for column 'c2' references another column, table 't1'". I would have expected a similar error for (3) and (4). What do you think?
I am not confusing COLUMN constraints with TABLE constraints. All my examples are COLUMN constraints. Let us look at (2). CREATE TABLE t1(c1 INT CONSTRAINT PK_t1 PRIMARY KEY(c1)); The structure of this statement is: CREATE TABLE t1( ); CREATE TABLE t1(c1 INT ); Rewriting to use a TABLE constraint gives CREATE TABLE t1(c1 INT, CONSTRAINT PK_t1 PRIMARY KEY(c1)); CREATE TABLE t1( , ) The difference is the comma after INT, as you pointed out in your reference to my CHECK constraint example. Please review the syntax for <column_constraint> in the link that you posted and show me where you can specify a list of columns in a primary key COLUMN constraint. Then explain why allowing a list of columns ina COLUMN constraint would make sense. Why should the definition of a column c1 say that the primary key is *another column* c2, as in (4)? I maintain that the examples that I originally posted are all illegal except (1).
Either way, you should probably still raise a Connect issue. I'd upvote it!
But all your examples are column constraints because you believe them to be. For the PK examples there's no need to have a comma between the column definitions and a table constraint so both can be written as you've shown. I agree the syntax in the link does not allow for a column list in a column constraint - but in your case it is a table constraint. I don't see why any of the examples are illegal.
Kev, you say that "there's no need to have a comma between the column definitions and a table constraint", but that is not so according to the syntax. Look at the first production in CREATE TABLE. The relevant section is: ( { <column_definition> | <computed_column_definition> | <column_set_definition> | [ <table_constraint> ] | [ <table_index> ] [ ,...n ] } ) Inside the round brackets, there is a COMMA-SEPARATED list (see the "[ ,...n ]") of any of 5 non-terminals, one of which is <table_constraint>. If what you are saying is true, then we could define a table with two columns like this: CREATE TABLE t1( c1 INT c2 INT);
Ah! But I qualified it with "For the PK examples there's no need...." OK I agree the documentation doesn't support what you are seeing, but instead of taking the point of view that the syntax is illegal, I'm simply saying that by having a column list you are clearly defining a table constraint and not a column constraint. Put another way, it's not that you are being allowed to create an illegal column constraint, it's that you are creating a table constraint using a syntax that isn't fully explained in the documentation. Maybe you should raise a connect item to correct the documentation?
I could raise a documentation issue, but I believe that the documentation is OK. The *behaviour* is wrong. It is not logically consistent. You say that "by having a column list you are clearly defining a table constraint and not a column constraint". By that logic, CREATE TABLE t1( c1 INT, c2 INT CHECK(c1 + c2 = 100)); should succeed, since by referring to c1 and c2 I am "clearly defining a table constraint and not a column constraint". However, it quite correctly fails and tells me that I am referring to another column (which would be c1). If I want to refer to c1, the column constraint should be part of the definition of c1. If I want to refer to c1 and c2, that is what table constraints are for. What is so special about a primary key column constraint that it should be allowed to refer to any column? Even CREATE TABLE t1(c1 INT PRIMARY KEY(c1)); should not be allowed. It should just be CREATE TABLE t1(c1 INT PRIMARY KEY); and if you want to define a table constraint instead, then you can write: CREATE TABLE t1(c1 INT, PRIMARY KEY(c1));
The published syntax does allow for a list of columns - I think you are confusing COLUMN constraints with TABLE constraints.
http://msdn.microsoft.com/en-gb/library/ms174979.aspx It is possible to define a PK as either - when it is a single column PK; when it is a multiple column PK, then you use a TABLE constraint. Clearly a TABLE constraint is 'added' once the table has been created, so it is possible to reference a column 'before' you declare it. In your CHECK constraint example, you are trying to apply a COLUMN level constraint but referring to another column - this is not allowed. If you meant to apply a TABLE constraint, you need another comma! CREATE TABLE t1( c1 INT CONSTRAINT PK_t1 PRIMARY KEY , c2 int, CONSTRAINT CHK_c2 CHECK(c1 > 0))
Actually this syntax is valid as well, though I wouldn't use it, as it's very confusing to read: CREATE TABLE t(c1 int CONSTRAINT PK_t PRIMARY KEY(c1,c2), c2 int)
14 People are following this question.
