You can disable the primary key of a table

Question

You can disable the primary key of a table

Navigation

#1 by (1 votes)

-1

I wanted to know if it is possible to disable a primary key in a table. I will explain the reason for disabling the primary key. I have a table B that has a foregein for table A, plus I need to delete table A without having to delete the foregein from table B. Table A was created with 999 record, however, from record 1 to record 35 were created right, more from record 36 until record 998 was asked to delete the records reserved.

the script to create the table

IF  EXISTS (SELECT * FROM SYS.TABLES WHERE NAME = 'Tabela A')

BEGIN  

delete from Tabela A

INSERT INTO Tabela A VALUES ('001','Administradora de Cartões Sicredi Ltda.',03106213000190)                
INSERT INTO Tabela A VALUES ('035','Administradora de Cartões Sicredi Ltda.(filial RS)',03106213000271)             
INSERT INTO Tabela A VALUES ('036','resevado',null)             
INSERT INTO Tabela A VALUES ('998','revervado',null) 
INSERT INTO Tabela A VALUES ('999','Outros',NULL)    
END 

GO

Can someone help me to delete this table.

sql sql-server

asked by anonymous 07.12.2015 / 19:54

1 answer

Query filter [closed] IDE for development in ionic framework? [closed]

score 1 · Accepted Answer

It is possible to deactivate a primary key. I'll use a client table as an example:

CREATE TABLE cliente(
    id_cliente int NOT NULL,
    nome VARCHAR(100) NOT NULL,
    CONSTRAINT pk_cliente_id PRIMARY KEY (id_cliente);
)

What's happening above / \ I created a table named client , with the id and name fields, and added a restriction to this table named pk_client_id indicating that the client_id field is a primary key. To remove the primary key, simply drop the constraint primary key:

ALTER TABLE cliente DROP CONSTRAINT pk_cliente_id

The same idea is worth removing a foreign key. A foreign key is also a constraint.

If you do not know the name of the constraint and you are using SQL Server, the query below lists all the primary keys and database bottlenecks.

SELECT
KCU1.CONSTRAINT_NAME AS 'FK_Nome_Constraint'
, KCU1.TABLE_NAME AS 'FK_Nome_Tabela'
, KCU1.COLUMN_NAME AS 'FK_Nome_Coluna'
, FK.is_disabled AS 'FK_Esta_Desativada'
, KCU2.CONSTRAINT_NAME AS 'PK_Nome_Constraint_Referenciada'
, KCU2.TABLE_NAME AS 'PK_Nome_Tabela_Referenciada'
, KCU2.COLUMN_NAME AS 'PK_Nome_Coluna_Referenciada'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU1
ON KCU1.CONSTRAINT_CATALOG = RC.CONSTRAINT_CATALOG
AND KCU1.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
AND KCU1.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
ON KCU2.CONSTRAINT_CATALOG = RC.UNIQUE_CONSTRAINT_CATALOG
AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA
AND KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME
AND KCU2.ORDINAL_POSITION = KCU1.ORDINAL_POSITION
JOIN sys.foreign_keys FK on FK.name = KCU1.CONSTRAINT_NAME
Order by
KCU1.TABLE_NAME