Tag Info

New answers tagged

1

These two queries may help you. The first will list all of the tables and indexes on those tables in your database. If the table does not appear in the list is does not have any indexes defined on it. These queries assume SQL Server version 2005 or newer. SELECT I.name AS IndexName , T.name AS TableName , I.is_primary_key AS IsPrimaryKey ...


1

I am not sure if you're interested in all constraints but INFORMATION_SCHEMA.TABLE_CONSTRAINTS doesn't seem to return the DEFAULT constraints -- TABLE_CONSTRAINTS (Transact-SQL) CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY This query will do a simple count against the sys.objects DMV: select COUNT(*) from sys.objects o where o.type_desc like ...


1

No, something is incorrect. The check on sys.indexes should return a row even if your table has no indexes. The heap still has a record in sys.indexes with a type_desc of 'HEAP' and type of 0. I think you probably need to make sure you are in the right database context since OBJECT_ID() and sys.objects are database-specific. Try this: USE MyDatabase ...


4

Much of this is a matter of taste and style. And more importantly: specific requirements and consistent conventions. However, there are good reasons for this generic advice: CREATE TABLE item ( item_id serial PRIMARY KEY, grp_id integer NOT NULL REFERENCES grp(grp_id) ); If you have an item_id, better make it unique and ideally a surrogate ...


1

bigserial is just shorthand for creating a sequence: CREATE TABLE tablename ( colname BIGSERIAL ); Equals CREATE SEQUENCE tablename_colname_seq; CREATE TABLE tablename ( colname biginteger DEFAULT nextval('tablename_colname_seq') NOT NULL ); Also, if you these are your primary keys, then they are already indexed, so you don't need to index ...


1

Well, if you need a way to check if all the foreign keys in your table are valid, this might help ( it just validates all foreign keys in your schema ) do $$ declare r record; BEGIN FOR r IN ( SELECT 'ALTER TABLE '|| tc.table_name||' VALIDATE CONSTRAINT '||tc.constraint_name||';' X FROM information_schema.table_constraints AS tc JOIN ...


4

A simple CHECK constraint works just fine: $ sqlite SQLite version 3.8.4.1 2014-03-11 15:27:36 ... sqlite> CREATE TABLE table_B( ...> user1, ...> user2, ...> [other stuff], ...> CHECK (user2 < user1) ...> ); sqlite> INSERT INTO table_B VALUES (1, 0); sqlite> INSERT INTO table_B VALUES (2, 3); Error: ...


1

It's not eav - google "celko eav" Not sure about exact syntax, but CHECK (user1 > user2) or similar should work - it does in Firebird embedded. AIUI, SQLite doesn't support subqueries. This isn't one. The constraint will take care of your alice,bob-bob,alice issue. Paul...


0

First of all, for 2000 records (as others have stated) everything will work. So, for the OP the natural key will still work. On the other hand VARCHAR fields for primary keys are (in most of the cases) a bad idea. They are inefficient, hard to index and provide slow performance. In most of the cases a numeric field ( int / bigint) will probably work better. ...


2

There are many things that can cause gaps in an IDENTITY column (rollbacks, deletes), but in this case due to the jump I suspect it is this bug - caused by the changes to IDENTITY with the introduction of SEQUENCE: Connect # 739013 : Failover or Restart Results in Reseed of Identity So I bet that if you look in SQL Server's error logs, the rows ...


2

When inserts are rolled back, the identity values are NOT reset. This is one explanation of the so called "skipped" values.


7

With InnoDB tables, all secondary indexes include the columns of the clustered index (which is the primary key), appended in the end. So your unique index has actually 4 columns, the 3 you have defined plus the 1 primary key column. When running a query that needs a full table scan, both indexes have all the data needed, so the optimizer is free to choose ...


1

The right way is like the following: Users {user_id (PK), username (Unique)} Profiles {profile_id(PK), user_id FK, user_views} in the given structure, we are making user_id as PK to link between tables, and making the username as Unique to avoid duplication, some people will do the username as PK, but this will make a problem if you want to update the ...


1

To start with the correct primary key (single column vs multiple or artificial vs natural) is to use the primary key that is correct for the task. Some people will tell you to always use an Artificial or Surrogate Key. This is a key that is a generated key and has nothing to do with the information it represents. For example an identity column or a ...



Top 50 recent answers are included