Menu
I have a table with a primary key already defined in it. I would like to add a column to it which must also be part of the primary key. How can that be done?
986
You can modify the primary key of a table by changing the column order, index name, clustered option, or fill factor.
SQL PRIMARY KEY on ALTER TABLE. ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName); Note: If you use ALTER TABLE to add a primary key, the primary key column(s) must have been declared to not contain NULL values (when the table was first created).
Alter table table_name add primary key (column_name); To change the Primary key column in the SQL Server, follow these steps: Drop already defined primary key. Add a new column as the primary key.
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName); Note: If you use ALTER TABLE to add a primary key, the primary key column (s) must have been declared to not contain NULL values (when the table was first created). DROP a PRIMARY KEY Constraint To drop a PRIMARY KEY constraint, use the following SQL:
You can modify a primary key in SQL Server by using SQL Server Management Studio or Transact-SQL. You can modify the primary key of a table by changing the column order, index name, clustered option, or fill factor.
Each table can only have one primary key. Access can automatically create a primary key field for you when you create a table, or you can specify the fields that you want to use as the primary key. This article explains how and why to use primary keys.
If you choose Yes, Access creates an ID field that uses the AutoNumber data type to provide a unique value for each record. If your table already includes an AutoNumber field, Access uses that field as the primary key. Remove the existing primary key using the instructions in the section Remove the primary key.
If PK_MY_TABLE is constraint name of existing primary key :
ALTER TABLE MY_TABLE DROP CONSTRAINT PK_MY_TABLE;
COMMIT;
alter table MY_TABLE
add constraint PK_MY_TABLE
primary key (ID,ID_1);
or
ALTER TABLE MY_TABLE DROP CONSTRAINT PK_MY_TABLE, ADD CONSTRAINT PK_MY_TABLE PRIMARY KEY (ID,ID_1);
An elegant way to do this in single query
In MYSQL
ALTER TABLE TABLE_NAME DROP PRIMARY KEY, ADD PRIMARY KEY(ID,ID_1);
FOR FIREBIRD:-
ALTER TABLE TABLE_NAME DROP CONSTRAINT TEST_CONST, ADD PRIMARY KEY (ID,ID_1)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
