![]() ![]() Select 1 from Person p group by PMid, PersonId, PName having COUNT(*) > 1 test your existing data suitable for new key or not Now the task he wants to accomplish is to execute a script to replace the existing compound-key with a new compound-key.įirst concern: the first requirement for fields to be a key is NOT NULL and they together need to be unique, otherwise you will get error when running the alter statement.įor that matter, I think the following is the right answer for the future answer seekers who have the same issue. He has an existing table with existing data and existing compound-key. I am surprised after all these years the answers to the question had not hit 100% what the question poster was asking. PRINT 'FSCK, need to figure out how to update NULL value(s).' PRINT 'FSCK, this column has duplicate keys, and can thus not be changed to primary key.' AND CONSTRAINT_NAME = 'PK_T_SYS_Language_Forms'ĪLTER TABLE T_SYS_Language_Forms ADD CONSTRAINT PK_T_SYS_Language_Forms PRIMARY KEY CLUSTERED (LANG_UID ASC) SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS IF 0 = (SELECT TOP 1 COUNT(*) AS cnt FROM T_SYS_Language_Forms GROUP BY LANG_UID ORDER BY cnt DESC) 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. Just in case anybody has as good a schema to work with as me. Hope this answer can help other SQL beginner. So I research some document to find every detail. ![]() I got a little confuse when i have looked all answers. This can set constraint name by developer. Or the better one below ALTER TABLE Persion ADD CONSTRAINT PK_Persion PRIMARY KEY (PersionId,Pname,PMID) MySQL / SQL Server / Oracle / MS Access: ALTER TABLE Persion ADD PRIMARY KEY (PersionId,Pname,PMID) Given constraint name when you created table,you can easily use You have to find the constraint name in your table. SQL Server / Oracle / MS Access: ALTER TABLE Persion Because there is one primary key for sql table.įirst, you have to drop your old primary key. You can't just add primary key,otherwise will cause error. There is already an primary key in your table. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |