Tag Info

New answers tagged

1

I'd say that this is pretty standard stuff ; Create table employees (id int primary key, name varchar(100) -- Plus some additional columns ) Create table responsibilities (id int primary key, description varchar(100) ...


0

It's a typo. I changed the REFERENCES Emplyee to REFERENCES Employee sorry, beginner mistake.


1

It seems to me that in a case like this you need a linking table that will allow for that many to many relationship. Something like Create Table tblBookWriter (tblBookWriter_ID int , Book_id --(FK to Book table-Book_Id) , Writer_id --(FK to Writer table-Writer_Id) ); With this you can add have a book with as many writers as it needs and a writer can have ...


1

You can't use on delete set null if any of the foreign key columns don't accept null: create schema stack; set search_path=stack; -- create table t1( foo integer , bar integer , primary key(foo,bar) ); -- create table t2( foo integer , baz integer , bar integer , primary key ...


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 ...


8

You've got lots of questions in here: Q: (The lack of foreign keys) confuses me a lot! It is a good practice (not mandatory) to have Fk's in the DWH for a variety of reasons (data integrity, relations visible for semantic layer, ....) A: Correct, it's normally a good practice to have foreign keys in a data warehouse. However, clustered columnstore indexes ...


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.


3

Define all the foreign keys as CASCADE UPDATE If you have not done this, then you'll have to Create a new row with new primary key Update all child tables Remove old row .. in a transaction of course and watching out for other constraints that could fail


3

If you have defined the Foreign Key constraints as ON UPDATE CASCADE then the Primary Key value that was changed should cascade down to all the Foreign Keys with that constraint. If you do not have the ON UPDATE CASCADE constraint, then you will need create scripts to complete the update. EDIT: Since you do not have the ON UPDATE CASCADE constraint, but ...


4

You sure can. ON UPDATE CASCADE is what you are looking for. Here's a small how-to: http://sqlandme.com/2011/08/08/sql-server-how-to-cascade-updates-and-deletes-to-related-tables/ Basically, when you modify the PK, the cascade will go out and update all the FKs that reference it. This can be done in your CREATE statement, same as if you were doing a ...


1

You're not showing us what you're doing - but based on the table structure, this is what you should do: create an INSERT statement that explicitly lists the columns it will insert into - assuming that ID might be an IDENTITY column that you don't want / can't insert into define the exact number of values to fill into these columns So your INSERT ...


10

The query results are correct, as your query finds all teams and results where a team played against itself (home_team = away_team). You probably want to use 2 joins to teams in you query: SELECT home.team_name AS home_team_name, away.team_name AS away_team_name, r.score FROM results AS r JOIN teams AS home ON r.home_team = ...



Top 50 recent answers are included