It is possible to disable the Primary key of a table

Asked

Viewed 3,634 times

-1

I was wondering if it’s possible to disable a Primary key from a table. I’ll explain why you have to disable Prime Key. I have a table B that has a foregein referring to table A, but I need to delete table A without having to delete 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 to record 998 was requested to delete the reserved record.

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

  • What you really need is to remove the Foreign key from table A to table B, no? Since this relation (e.g., field idA of table B reference field id from table A), you may remove any elements from table A (assuming there is no other FK relation to that table).

  • Look I don’t know if I got it wrong, but in my view you need to do an update to update Table 'B' with the correct records, that is, the records from 1 to 35. Then you can delete the information you consider wrong in Table 'A' which are 35 to 998.

  • Value the Marconi tip, was talked with the other programmer and was set that, and better to give an update and then delete the registration from 35 to 998, thanks for the tip.

1 answer

0


It is possible to disable a Primary key. I will 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 is happening up / I created a table called client, with the fields id and name, and added a restriction to this table called pk_cliente_id indicating that the field id_client 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 applies to removing a Foreign key. A Foreign key is also a Constraint.

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

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

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.