alter-table

How to add not null constraint to existing column in MySQL

by

I have table name called “Person” with following column names P_Id(int), LastName(varchar), FirstName (varchar). I forgot to give NOT NULL Constraint to P_Id. Now I tried with following query to add NOT NULL Constraint to existing column called P_Id, 1. ALTER TABLE Person MODIFY (P_Id NOT NULL); 2. ALTER TABLE Person ADD CONSTRAINT NOT NULL … Read more

Can I create a named default constraint in an add column statement in SQL Server?

by

In SQL Server, I have a new column on a table: ALTER TABLE t_tableName ADD newColumn NOT NULL This fails because I specify NOT NULL without specifying a default constraint. The table should not have a default constraint. To get around this, I could create the table with the default constraint and then remove it. … Read more

Postgresql – change the size of a varchar column to lower length

by

I have a question about the ALTER TABLE command on a really large table (almost 30 millions rows). One of its columns is a varchar(255) and I would like to resize it to a varchar(40). Basically, I would like to change my column by running the following command: ALTER TABLE mytable ALTER COLUMN mycolumn TYPE … Read more

Alter column, add default constraint

by

I have a table and one of the columns is “Date” of type datetime. We decided to add a default constraint to that column Alter table TableName alter column dbo.TableName.Date default getutcdate() but this gives me error: Incorrect syntax near ‘.’ Does anyone see anything obviously wrong here, which I am missing (other than having … Read more

How do I add more members to my ENUM-type column in MySQL?

by

The MySQL reference manual does not provide a clearcut example on how to do this. I have an ENUM-type column of country names that I need to add more countries to. What is the correct MySQL syntax to achieve this? Here’s my attempt: ALTER TABLE carmake CHANGE country country ENUM(‘Sweden’,’Malaysia’); The error I get is: … Read more

ALTER TABLE to add a composite primary key

by

I have a table called provider. I have three columns called person, place, thing. There can be duplicate persons, duplicate places, and duplicate things, but there can never be a dupicate person-place-thing combination. How would I ALTER TABLE to add a composite primary key for this table in MySQL with the these three columns? 6 … Read more

Alter a MySQL column to be AUTO_INCREMENT

by

I’m trying to modify a table to make its primary key column AUTO_INCREMENT after the fact. I have tried the following SQL, but got a syntax error notification. ALTER TABLE document ALTER COLUMN document_id AUTO_INCREMENT Am I doing something wrong or is this not possible? +——————–+ | VERSION() | +——————–+ | 5.0.75-0ubuntu10.2 | +——————–+ 21 … Read more

SQL Server add auto increment primary key to existing table

by

As the title, I have an existing table which is already populated with 150000 records. I have added an Id column (which is currently null). I’m assuming I can run a query to fill this column with incremental numbers, and then set as primary key and turn on auto increment. Is this the correct way … Read more

How can I add a column that doesn’t allow nulls in a Postgresql database?

by

I’m adding a new, “NOT NULL” column to my Postgresql database using the following query (sanitized for the Internet): ALTER TABLE mytable ADD COLUMN mycolumn character varying(50) NOT NULL; Each time I run this query, I receive the following error message: ERROR: column “mycolumn” contains null values I’m stumped. Where am I going wrong? NOTE: … Read more