Thread: ALTER .. ADD PRIMARY KEY
Right now ALTER .. ADD PRIMARY KEY insists that the columns must be NOT NULL first. I'd like to change it so that it automatically creates the NOT NULL constraint (same as CREATE TABLE). I didn't see anything in the spec for or against doing this automatically. I believe the best way to do this is to move the NULL test to index_create or CreateConstraintEntry (leaning towards the latter) and use the AlterTableAlterColumnSetNotNull function to add in the bits. This cleans out a good chuck of transformIndexConstraints(), and will allow PRIMARY KEYS on inherited columns do to the right thing in adding a nullness bit. On a new table, there are no rows to check, so alter table is almost free. Likewise, we can add type checks to see if the type allows null, skipping the table constraint if this is the case. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Just check out the AlterTableSetNotNull function in tablecmds.c to see a quick and easy example of checking for NULL values. Chris ----- Original Message ----- From: "Rod Taylor" <rbt@rbt.ca> To: "PostgreSQL-development" <pgsql-hackers@postgresql.org> Sent: Monday, December 02, 2002 12:31 PM Subject: [HACKERS] ALTER .. ADD PRIMARY KEY Right now ALTER .. ADD PRIMARY KEY insists that the columns must be NOT NULL first. I'd like to change it so that it automatically creates the NOT NULL constraint (same as CREATE TABLE). I didn't see anything in the spec for or against doing this automatically. I believe the best way to do this is to move the NULL test to index_create or CreateConstraintEntry (leaning towards the latter) and use the AlterTableAlterColumnSetNotNull function to add in the bits. This cleans out a good chuck of transformIndexConstraints(), and will allow PRIMARY KEYS on inherited columns do to the right thing in adding a nullness bit. On a new table, there are no rows to check, so alter table is almost free. Likewise, we can add type checks to see if the type allows null, skipping the table constraint if this is the case. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc
Just check out the AlterTableSetNotNull function in tablecmds.c to see a quick and easy example of checking for NULL values. Chris ----- Original Message ----- From: "Rod Taylor" <rbt@rbt.ca> To: "PostgreSQL-development" <pgsql-hackers@postgresql.org> Sent: Monday, December 02, 2002 12:31 PM Subject: [HACKERS] ALTER .. ADD PRIMARY KEY Right now ALTER .. ADD PRIMARY KEY insists that the columns must be NOT NULL first. I'd like to change it so that it automatically creates the NOT NULL constraint (same as CREATE TABLE). I didn't see anything in the spec for or against doing this automatically. I believe the best way to do this is to move the NULL test to index_create or CreateConstraintEntry (leaning towards the latter) and use the AlterTableAlterColumnSetNotNull function to add in the bits. This cleans out a good chuck of transformIndexConstraints(), and will allow PRIMARY KEYS on inherited columns do to the right thing in adding a nullness bit. On a new table, there are no rows to check, so alter table is almost free. Likewise, we can add type checks to see if the type allows null, skipping the table constraint if this is the case. -- Rod Taylor <rbt@rbt.ca> PGP Key: http://www.rbt.ca/rbtpub.asc