Question

<>>>>>

这里是因询问而产生的制约因素。

SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME =  history 

CONSTRAINT_NAME   COLUMN_NAME  ORDINAL_POSITION
PK_history        userKey       1
PK_history        name          2

这里是询问的结果。

SELECT * 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE TABLE_NAME =  history 

CONSTRAINT_NAME   CONSTRAINT_TYPE  IS_DEFERRABLE  INITIALLY_DEFERRED
PK_history        PRIMARY KEY      NO             NO

http://www.ohchr.org。

我的东道国通过伙伴关系向我的服务器-DB提供接口。 NET 企业经理。

页: 1 表:

userId (key, int, NULL not allowed)
name (key, string, NULL not allowed)
id (not key, int, NULL allowed)

我想把id栏作为唯一关键。

为此,我认为我需要:

Make sure there are no NULLs in that column for any row
Set the column to not allow NULLs
Add the column as a primary key
Remove the other 2 columns as keys

然而,当我使用《国际调查》时,它从未奏效。有时,它只是想做一些事情,但在我重新审视各栏时,它从未改变过。它偶尔会形成一个诱惑表,像它试图做某些行动一样,但从未像我试图改变的原始表格那样复制/篡改。

当我尝试使用询问时,这些变化也没有显示。这里,我认为我需要:

    SELECT * from history WHERE id is NULL     <---- This shows 0 results

    ALTER TABLE history
    ALTER COLUMN id int NOT NULL

    ALTER TABLE history ADD PRIMARY KEY (id)

    ALTER TABLE history
    DROP CONSTRAINT userId
    DROP CONSTRAINT name
    GO

我只想去掉民族解放军,并补充id一栏的主要钥匙。似乎没有工作。谁能把我引向正确方向? 感谢!

Answer 1

假设你目前的主要制约因素被称为“k”。

ALTER TABLE history ADD PRIMARY KEY (id)

ALTER TABLE history
DROP CONSTRAINT userId
DROP CONSTRAINT name

注

ALTER TABLE history DROP CONSTRAINT pk_history

ALTER TABLE history ADD CONSTRAINT pk_history PRIMARY KEY (id)

如果你不知道PK的名称是什么,你可以发现它有以下疑问:

SELECT * 
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
 WHERE TABLE_NAME =  history

Answer 2

Necromancing.
It looks you have just as good a schema to work with as me... Here is how to do it correctly:

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 
        -- 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)
            ;

            -- Adding foreign key
            IF 0 = (SELECT COUNT(*) FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME =  FK_T_ZO_SYS_Language_Forms_T_SYS_Language_Forms ) 
                ALTER TABLE T_ZO_SYS_Language_Forms WITH NOCHECK ADD CONSTRAINT FK_T_ZO_SYS_Language_Forms_T_SYS_Language_Forms FOREIGN KEY(ZOLANG_LANG_UID) REFERENCES T_SYS_Language_Forms(LANG_UID); 
        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

友情链接