![]() |
Add primary key to existing table - Printable Version +- 0Day Forums (https://0day.red) +-- Forum: Coding (https://0day.red/Forum-Coding) +--- Forum: Database (https://0day.red/Forum-Database) +---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server) +---- Thread: Add primary key to existing table (/Thread-Add-primary-key-to-existing-table) Pages:
1
2
|
Add primary key to existing table - nerysiljyiya - 07-31-2023 I have an existing table called `Persion`. In this table I have 5 columns: - persionId - Pname - PMid - Pdescription - Pamt When I created this table, I set `PersionId` and `Pname` as the **primary key**. I now want to include one more column in the primary key - PMID. How can I write an `ALTER` statement to do this? (I already have 1000 records in the table) RE: Add primary key to existing table - estruation441499 - 07-31-2023 drop constraint and recreate it alter table Persion drop CONSTRAINT <constraint_name> alter table Persion add primary key (persionId,Pname,PMID) edit: you can find the constraint name by using the query below: select OBJECT_NAME(OBJECT_ID) AS NameofConstraint FROM sys.objects where OBJECT_NAME(parent_object_id)='Persion' and type_desc LIKE '%CONSTRAINT' RE: Add primary key to existing table - acouchi578746 - 07-31-2023 I think something like this should work -- drop current primary key constraint ALTER TABLE dbo.persion DROP CONSTRAINT PK_persionId; GO -- add new auto incremented field ALTER TABLE dbo.persion ADD pmid BIGINT IDENTITY; GO -- create new primary key constraint ALTER TABLE dbo.persion ADD CONSTRAINT PK_persionId PRIMARY KEY NONCLUSTERED (pmid, persionId); GO RE: Add primary key to existing table - kyeen - 07-31-2023 The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain UNIQUE values and column cannot contain NULL Values. -- DROP current primary key ALTER TABLE tblPersons DROP CONSTRAINT <constraint_name> Example: ALTER TABLE tblPersons DROP CONSTRAINT P_Id; -- ALTER TABLE tblpersion ALTER TABLE tblpersion add primary key (P_Id,LastName) RE: Add primary key to existing table - precieuse896781 - 07-31-2023 -- create new primary key constraint ALTER TABLE dbo.persion ADD CONSTRAINT PK_persionId PRIMARY KEY NONCLUSTERED (pmid, persionId); is a better solution because you have control over the naming of the primary_key. ---------- It's better than just using ALTER TABLE Persion ADD PRIMARY KEY(persionId,Pname,PMID) which yeilds randomized names and can cause problems when scripting out or comparing databases RE: Add primary key to existing table - mercurated388493 - 07-31-2023 Necromancing.<br /> Just in case anybody has as good a schema to work with as me...<br /> Here is how to do it correctly:<br /> In this example, the table name is dbo.T_SYS_Language_Forms, and the column name is LANG_UID -- First, chech if the table exists... IF 0 < ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'T_SYS_Language_Forms' ) BEGIN -- Check for NULL values in the primary-key column IF 0 = (SELECT COUNT(*) FROM T_SYS_Language_Forms WHERE LANG_UID IS NULL) BEGIN ALTER TABLE T_SYS_Language_Forms ALTER COLUMN LANG_UID uniqueidentifier NOT NULL -- No, don't drop, FK references might already exist... -- Drop PK if exists (it is very possible it does not have the name you think it has...) -- ALTER TABLE T_SYS_Language_Forms DROP CONSTRAINT pk_constraint_name --DECLARE @pkDropCommand nvarchar(1000) --SET @pkDropCommand = N'ALTER TABLE T_SYS_Language_Forms DROP CONSTRAINT ' + QUOTENAME((SELECT CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS --WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' --AND TABLE_SCHEMA = 'dbo' --AND TABLE_NAME = 'T_SYS_Language_Forms' ----AND CONSTRAINT_NAME = 'PK_T_SYS_Language_Forms' --)) ---- PRINT @pkDropCommand --EXECUTE(@pkDropCommand) -- Instead do -- EXEC sp_rename 'dbo.T_SYS_Language_Forms.PK_T_SYS_Language_Forms1234565', 'PK_T_SYS_Language_Forms'; -- Check if they keys are unique (it is very possible they might not be) IF 1 >= (SELECT TOP 1 COUNT(*) AS cnt FROM T_SYS_Language_Forms GROUP BY LANG_UID ORDER BY cnt DESC) BEGIN -- If no Primary key for this table IF 0 = ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'T_SYS_Language_Forms' -- AND CONSTRAINT_NAME = 'PK_T_SYS_Language_Forms' ) ALTER TABLE T_SYS_Language_Forms ADD CONSTRAINT PK_T_SYS_Language_Forms PRIMARY KEY CLUSTERED (LANG_UID ASC) ; END -- End uniqueness check ELSE PRINT 'FSCK, this column has duplicate keys, and can thus not be changed to primary key...' END -- End NULL check ELSE PRINT 'FSCK, need to figure out how to update NULL value(s)...' END RE: Add primary key to existing table - scoggin320115 - 07-31-2023 Try using this code: ALTER TABLE `table name` CHANGE COLUMN `column name` `column name` datatype NOT NULL, ADD PRIMARY KEY (`column name`) ; RE: Add primary key to existing table - sheply308 - 07-31-2023 ALTER TABLE TABLE_NAME ADD PRIMARY KEY(`persionId`,`Pname`,`PMID`) RE: Add primary key to existing table - Sirbersagliere6 - 07-31-2023 If you add primary key constraint ALTER TABLE <TABLE NAME> ADD CONSTRAINT <CONSTRAINT NAME> PRIMARY KEY <COLUMNNAME> for example: ALTER TABLE DEPT ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO) RE: Add primary key to existing table - tadzhik593 - 07-31-2023 Please try this- ALTER TABLE TABLE_NAME DROP INDEX `PRIMARY`, ADD PRIMARY KEY (COLUMN1, COLUMN2,..); |