Tag Info

New answers tagged

1

Let's first distinguish between keys and indexes, key is part of the logical model and is often implemented with an unique index. You can however create a unique index without creating a key, but that can not be referenced by a foreign key. A candidate key is something that uniquely identifies a row in a table, in SQL one of the candidate keys are normally ...


1

Having the unique index on EmailAddress is fine. As you have already stated that there is validation in your application for having Email Address as required field, I would say as the other validation would be from database is not accept a user with out an Email address and prevent duplicate entry as well and these validation will be imposed with this ...


2

Yes having a unique index on the EmailAddress column should be ok. The only problem would be if someone gave up the email address after signing up for your service but didn't tell you, then whoever the owner of the email address tries to sign up. But that's a pretty rare edge case. As to if a Unique Index allows null values that'll depend on your database ...


1

If I guessed correct you are using PHP PDO, so what your are looking for is this function. http://us3.php.net/manual/en/pdo.lastinsertid.php. It will return the id off the last insert tuple into the database which you can save in a variable to be used in the insert for the junction table. $stmt = $sqli->prepare("INSERT INTO book(book_original_name, ...


0

If you don't like using primary keys that are auto generated, there is another way. Instead of indexing the userID (which is large and slow to index, because it is a string) you can instead index the hash value of userID. You can then do: CREATE TABLE Users ( ID BIGINT , Username VARCHAR(255) ) And you can find the user with: $user = ...


1

You might need INFORMATION_SCHEMA.KEY_COLUMN_USAGE You would have to issue this query SELECT column_name FROM information_schema.key_column_usage WHERE table_schema='$db' AND table_name='$table' AND constraint_name='PRIMARY'; This will retrieve every column in the PRIMARY KEY. You could then make this query generate the ALTER TABLE clauses for you as ...


4

A multiple-column foreign key constraint must be declared at the table level, not on each column: CREATE TABLE dbo.t1 ( id int NOT NULL, name nchar(10) NOT NULL, CONSTRAINT PK_dbo_t1_id_name PRIMARY KEY CLUSTERED (id,name) ); CREATE TABLE dbo.t2 ( id int NOT NULL, name nchar(10) NOT NULL, CONSTRAINT PK_dbo_t2_id_name ...


6

There's no way to do this automatically but you can query the catalogue views to find constraint names not in the desired pattern then generate the desired script that way. Something like DECLARE @Script NVARCHAR(MAX); WITH FK AS (SELECT *, 'FK_' + OBJECT_NAME(parent_object_id) + '_' + OBJECT_NAME(referenced_object_id) ...


1

You can use sp_rename systems stored procedure to change the name of the keys. sp_rename 'dbo.PK_TableName_OldName', 'PK_TableName_NewName';


2

You can use sp_rename to change the names of constraints such as Foreign Keys. The syntax is simple: From the SQL Server documentation: http://msdn.microsoft.com/en-us/library/ms188351.aspx exec sp_rename 'YourSchema.FK_OldName', 'FK_NewName'; Note that the schema is required when renaming constraints.



Top 50 recent answers are included