Disabling Constraints in SQL server?

[WWGD]

Hi, I want to practice queries that require having repeated records. This is not allowed by default since it violates uniqueness constraint. Do I just use an alter table drop unique constraint for this?

 

[jedishrfu]

Can’t you build a table without the uniqueness? I’ve done that from time to time. These kinds of tables are slower if large and you need to find stuff. You want to avoid table scans that’s why indexes were created.

 

[WWGD]

Can’t you build a table without the uniqueness? I’ve done that from time to time. These kinds of tables are slower if large and you need to find stuff. You want to avoid table scans that’s why indexes were created.

Thanks, I am trying to use them to test certain queries, like queries done to delete duplicates. So for this, I need to have duplicates which I can then delete.

 

[QuantumQuest]

Hi, I want to practice queries that require having repeated records. This is not allowed by default since it violates uniqueness constraint. Do I just use an alter table drop unique constraint for this?

If the table you want to have duplicate records in has a unique identification column (some sort of an "ID" thing) then (obviously) there is no problem inserting duplicate records or even more than two times the same record. So, I think that you mean that you don't have something like this and hence the uniqueness constraint issue you talk about. In this case - as far as I know, you need to have permission for an "ALTER TABLE" in order to add an identity column. Then you can utilize one of various methods to get rid of the duplicate records i.e. correlated subquery, using temporary table etc. (for these various methods you may want to take a look here). Needless to say that all the above are for testing purposes on a table that you can really alter without affecting the database schema i.e. not on any sort of database in production phase.

 

[jim mcnamara]

Modern databases support sequences, a record field (part of the primary key) that is/can be automatically numbered on insertion by a predefined increment - usually one. This is specifically meant for repeating child tables. I think it is what @QuantumQuest is describing, but I am not sure - so I wrote this quick post.

Do not consider making a child table without keys unless it is VERY tiny and static. Then full table scans are sufficient and as fast as indexed column lookups.
Note: this defeats the purpose of most child tables.

 

[WWGD]

If the table you want to have duplicate records in has a unique identification column (some sort of an "ID" thing) then (obviously) there is no problem inserting duplicate records or even more than two times the same record. So, I think that you mean that you don't have something like this and hence the uniqueness constraint issue you talk about. In this case - as far as I know, you need to have permission for an "ALTER TABLE" in order to add an identity column. Then you can utilize one of various methods to get rid of the duplicate records i.e. correlated subquery, using temporary table etc. (for these various methods you may want to take a look here). Needless to say that all the above are for testing purposes on a table that you can really alter without affecting the database schema i.e. not on any sort of database in production phase.

Yes, this is just small-scale academic effort; I would not experiment in a production environment. Thanks.