New answers tagged

18

You could define the ID as a computed column evaluating to a constant value, and declare that column to be unique: CREATE TABLE dbo.Configuration ( ID AS CAST(1 AS tinyint), -- or: AS bit ... -- other columns CONSTRAINT UQ_Configuration_ID UNIQUE (ID) );


9

You can also use trigger.. create trigger LimitTable on YourTableToLimit after insert as declare @tableCount int select @tableCount = Count(*) from YourTableToLimit if @tableCount > 50 begin rollback end go


41

These two constraints would do: CREATE TABLE dbo.Configuration ( ConfigurationID TINYINT NOT NULL DEFAULT 1, -- the rest of the columns CONSTRAINT Configuration_PK PRIMARY KEY (ConfigurationID), CONSTRAINT Configuration_OnlyOneRow CHECK (ConfigurationID = 1) ) ; You need both the PRIMARY KEY (or a UNIQUE constraint) so no two rows have the ...


1

Seems a bit of an odd requirement but ho-hum :) You could just have a constraint on the table and then only allow updates (no insert or deletes) to the table? CREATE TABLE dbo.Config ( ID INT identity(1,1), CONFIGURATION VARCHAR(MAX), constraint ck_limitrows CHECK (ID <=1) ); It's a bit of a hackey way to do it though, would it not ...


2

s is not valid at that point you only have inserted if you want the fields then try OUTPUT inserted.ID, inserted.[Field1], inserted.[Field2] instead of line 2 in your query. You can find more here: OUTPUT Clause (Transact-SQL)


2

The CHECK clause is parsed but ignored by all storage engines. http://dev.mysql.com/doc/refman/5.7/en/create-table.html Use trigger instead.


2

You could create a view that looks like something like this: create view test_view as select a,b,c,d, case when a = greatest(a,b,c,d) then 'a' when b = greatest(a,b,c,d) then 'b' when c = greatest(a,b,c,d) then 'c' when d = greatest(a,b,c,d) then 'd' end from test; Note that the above will not ...


0

Your table is about the 95th percentile among MySQL tables. So, it is certainly possible. However, it will take a long time to insert 28M rows one at a time. If practical, do one of these: LOAD DATE INFILE ... -- probably the fastest INSERT ... VALUES (...), (...), ... -- "batch" load 1000 rows at a time; very fast Assuming you use ENGINE=InnoDB (which ...



Top 50 recent answers are included