Listed here are the restrictions consequently from the query
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
This is actually the consequence of the query
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'history' CONSTRAINT_NAME CONSTRAINT_TYPE IS_DEFERRABLE INITIALLY_DEFERRED PK_history PRIMARY KEY NO NO
My host offers an interface to my SQL Server DB via ASP.Internet Enterprise Manager.
I've 3 posts during my
userId(key, int, NULL not permitted)
name(key, string, NULL not permitted)
id(not key, int, NULL permitted)
I wish to result in the id column the only real key.
To achieve that, In my opinion I have to:
- Make certain you will find no NULLs for the reason that column for just about any row
- Set the column not to allow NULLs
- Add the column like a primary key
- Take away the other 2 posts as secrets
However, after i make use of the UI provided, it never works. Sometimes it'll just seem like it tries to behave however it never changes after i refresh viewing posts. It from time to time produces a temp table that appears enjoy it attempted to complete a few of the operation, but that never will get replicated/overwrites the initial table that I am attempting to change.
After I use a question, the alterations do not show up either. Listed here are the queries I believe I want:
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
I have only reached the make an effort to disallow NULLs and also to add the main key for that id column. It does not appear to operate. Can someone point me within the right direction? Thanks!
Presuming that the current primary key constraint is known as pk_history, you are able to replace the next lines:
ALTER TABLE history ADD PRIMARY KEY (id) ALTER TABLE history DROP CONSTRAINT userId DROP CONSTRAINT name
with one of these:
ALTER TABLE history DROP CONSTRAINT pk_history ALTER TABLE history ADD CONSTRAINT pk_history PRIMARY KEY (id)
If you do not understand what the title from the PK is, you'll find it using the following query:
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE TABLE_NAME = 'history'